Deployment Issue

We have a requirement to display some 80,000 records in the report. The Stored procedure in the back end is displaying results in less than 2 mins..but the system hangs when we run the report.

Any work around for this strange behaviour?

When you run a report, not only do you have to collect the data, you also have to render the report, 80K records is 800 pages if you have 100 records per page.

What happens if you run the report as a scheduled report, and set the run time to the middle of the night when database and report server activity is low?

Deploying to Report Manager error

I have created a report using SSRS 2005. It uses a stored procedure. It executes beautifully when I'm in reporting services. But, when I deploy it to Report Manager, this is the error I am getting.

An error has occurred during report processing.
Query execution failed for data set 'DataSource1'.
An MDX Statement was expected. An MDX expression was specified.

I have 3 parameters in this report. Start Date, End Date & Region. Region is a drop down that contains 2 options. This is a string field being passed into the procedure. Start and end dates are just basic date fields.

I am not using a cube to get my data.

I have deployed to the Report Manager before and have been successful.

Thanks!

With a name like DataSource1 I'm assuming this is a test environment. It's probable that in a previous test a shared data source (connecting to MSAS) with the same name was deployed to the server.

In you report project, make sure that in the deployment properties you have the "Overwrite Data Sources" option set to true. I suspect that when you deploy, only the report is being uploaded and not the new data source that it's using

Deploying SQL Database

Hi All,

Can anyone tell me,is it possible to create an exe or msi for sql stored procedures,tables and triggers?I want to deploy the database objects(stored proc,tables,views and functions) as an exe file..just like publishing and deploying the asp.net application.Is it possible for sql server database objects.Pls,let me know.

Thank U[:)]Usually database with all objects gets backed up and then restored on another Server. It is the best and safest way to do it.

You also can extract scripts of all objects and then run them on another Server using exe or any means which can connect to that Server.

Good Luck.

deploying extended stored proc

Hi
I have written an extended stored procedure DLL, thanks to the people who
helped me on this.
however i wonder if anyone could shed any light on my deployment issues that
I'm having with it.
Basically, I built it on my XP development PC with VC7.1. I can deploy it on
this PC's local default instance of MSDE without problems. It uses no managed
code whatsoever, and is compiled without /clr. Totally unmanaged.
I am trying to deploy it to a different PC with Win2K, and again - a local,
default, instance of MSDE. I copied it to C:\program files\microsoft sql
server\mssql\binn just as on my pc, added it using "sp_addextendedproc
'xp_myproc', 'xp_myproc.dll'", but it gave the error:
ODBC: Msg 0, Level 16, State 1
Cannot load the DLL xp_myproc.dll, or one of the DLLs it references. Reason:
126(error not found).
The only thing I can think to be different is that one's got Win2000,
whereas my dev. PC has got XP. This shouldn't matter though, should it?
I checked that the names are the correct case, correct names of the
procedure and the DLL filename and everything.
What could be wrong?
Thanks
Sorry for the obvious question, but perhaps you added the dll file for the wrong instance of SQL
server?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bonj" <Bonj@.discussions.microsoft.com> wrote in message
news:A412072A-1157-4908-8D07-96CDBE6788AC@.microsoft.com...
> Hi
> I have written an extended stored procedure DLL, thanks to the people who
> helped me on this.
> however i wonder if anyone could shed any light on my deployment issues that
> I'm having with it.
> Basically, I built it on my XP development PC with VC7.1. I can deploy it on
> this PC's local default instance of MSDE without problems. It uses no managed
> code whatsoever, and is compiled without /clr. Totally unmanaged.
> I am trying to deploy it to a different PC with Win2K, and again - a local,
> default, instance of MSDE. I copied it to C:\program files\microsoft sql
> server\mssql\binn just as on my pc, added it using "sp_addextendedproc
> 'xp_myproc', 'xp_myproc.dll'", but it gave the error:
> ODBC: Msg 0, Level 16, State 1
> Cannot load the DLL xp_myproc.dll, or one of the DLLs it references. Reason:
> 126(error not found).
> The only thing I can think to be different is that one's got Win2000,
> whereas my dev. PC has got XP. This shouldn't matter though, should it?
> I checked that the names are the correct case, correct names of the
> procedure and the DLL filename and everything.
> What could be wrong?
> Thanks
>
|||Nope. Like I say, the following is true of both dev machine, and deployment
target:
There's only one, default, instance of SQL Server (MSDE) on the machine. The
path of the binaries is
c:\program files\microsoft sql server\mssql\binn
and this is the only location where sqlservr.exe is found on the machine.
"Tibor Karaszi" wrote:

> Sorry for the obvious question, but perhaps you added the dll file for the wrong instance of SQL
> server?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Bonj" <Bonj@.discussions.microsoft.com> wrote in message
> news:A412072A-1157-4908-8D07-96CDBE6788AC@.microsoft.com...
>
>
|||I see, it was only a wild guess...
Re-reading the error message, it can also be that the DLL you produced in return references some
other DLL file, and this is the file that cannot be found. (I'm sorry if I'm stating the obvious
here... :-\).
I'm sure that there are tools out there to check DLL dependencies etc to investigate what the
problem might be. Perhaps some of the VC persons can pick up on that. ;-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bonj" <Bonj@.discussions.microsoft.com> wrote in message
news:18370E98-D316-4A4C-B5D7-BCCD553AC4FD@.microsoft.com...[vbcol=seagreen]
> Nope. Like I say, the following is true of both dev machine, and deployment
> target:
> There's only one, default, instance of SQL Server (MSDE) on the machine. The
> path of the binaries is
> c:\program files\microsoft sql server\mssql\binn
> and this is the only location where sqlservr.exe is found on the machine.
>
> "Tibor Karaszi" wrote:
|||Like I say, it does it the same with a 'blank' extended stored procedure...
i.e. just let the wizard create one, don't add any code, and compile. That
doesn't work on it either.
"Tibor Karaszi" wrote:

> I see, it was only a wild guess...
> Re-reading the error message, it can also be that the DLL you produced in return references some
> other DLL file, and this is the file that cannot be found. (I'm sorry if I'm stating the obvious
> here... :-\).
> I'm sure that there are tools out there to check DLL dependencies etc to investigate what the
> problem might be. Perhaps some of the VC persons can pick up on that. ;-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Bonj" <Bonj@.discussions.microsoft.com> wrote in message
> news:18370E98-D316-4A4C-B5D7-BCCD553AC4FD@.microsoft.com...
>
>
|||Possibly some stuff is linked in by the compiling environment. I haven't used C or C++ for some 12
years now, so this is only a guess...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bonj" <Bonj@.discussions.microsoft.com> wrote in message
news:67E3A9A6-3BC7-4110-9188-8FC3996555A8@.microsoft.com...[vbcol=seagreen]
> Like I say, it does it the same with a 'blank' extended stored procedure...
> i.e. just let the wizard create one, don't add any code, and compile. That
> doesn't work on it either.
>
> "Tibor Karaszi" wrote:
SQL[vbcol=seagreen]
|||As you suggested I created a default extended stored procedure using
VS.NET'03 on WinXP...I used the depends.exe to determine the dependencies.
Depends.exe reported and error trying to find the OPENDS60.DLL. Perhaps this
is part of your problem...
"Bonj" wrote:
[vbcol=seagreen]
> Like I say, it does it the same with a 'blank' extended stored procedure...
> i.e. just let the wizard create one, don't add any code, and compile. That
> doesn't work on it either.
>
> "Tibor Karaszi" wrote:
|||oh right... i'll check it out when I get back to work..
Cheers for the investigation, appreciate it!
"Mike M" <Mike M@.discussions.microsoft.com> wrote in message
news:D91F9749-AED1-44C0-9AA0-6251A39EE6B2@.microsoft.com...[vbcol=seagreen]
> As you suggested I created a default extended stored procedure using
> VS.NET'03 on WinXP...I used the depends.exe to determine the dependencies.
> Depends.exe reported and error trying to find the OPENDS60.DLL. Perhaps
> this
> is part of your problem...
> "Bonj" wrote:
|||I ran depends.exe on it and it said this at the bottom in red:
Warning: At least one module has an unresolved import due to a missing
export function in a delay-load dependent module.
How can I find which one, and resolve it?
|||It didn't seem to be the opends60.dll, because the target machine has got it
in its SQL server "binn" directory.
"Bonj" <benjtaylor at hotpop d0t com> wrote in message
news:uXSnkr5tEHA.2072@.tk2msftngp13.phx.gbl...
> oh right... i'll check it out when I get back to work..
> Cheers for the investigation, appreciate it!
> "Mike M" <Mike M@.discussions.microsoft.com> wrote in message
> news:D91F9749-AED1-44C0-9AA0-6251A39EE6B2@.microsoft.com...
>

deploying extended stored proc

Hi
I have written an extended stored procedure DLL, thanks to the people who
helped me on this.
however i wonder if anyone could shed any light on my deployment issues that
I'm having with it.
Basically, I built it on my XP development PC with VC7.1. I can deploy it on
this PC's local default instance of MSDE without problems. It uses no manage
d
code whatsoever, and is compiled without /clr. Totally unmanaged.
I am trying to deploy it to a different PC with Win2K, and again - a local,
default, instance of MSDE. I copied it to C:\program files\microsoft sql
server\mssql\binn just as on my pc, added it using "sp_addextendedproc
'xp_myproc', 'xp_myproc.dll'", but it gave the error:
ODBC: Msg 0, Level 16, State 1
Cannot load the DLL xp_myproc.dll, or one of the DLLs it references. Reason:
126(error not found).
The only thing I can think to be different is that one's got Win2000,
whereas my dev. PC has got XP. This shouldn't matter though, should it?
I checked that the names are the correct case, correct names of the
procedure and the DLL filename and everything.
What could be wrong?
ThanksSorry for the obvious question, but perhaps you added the dll file for the w
rong instance of SQL
server?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bonj" <Bonj@.discussions.microsoft.com> wrote in message
news:A412072A-1157-4908-8D07-96CDBE6788AC@.microsoft.com...
> Hi
> I have written an extended stored procedure DLL, thanks to the people who
> helped me on this.
> however i wonder if anyone could shed any light on my deployment issues th
at
> I'm having with it.
> Basically, I built it on my XP development PC with VC7.1. I can deploy it
on
> this PC's local default instance of MSDE without problems. It uses no mana
ged
> code whatsoever, and is compiled without /clr. Totally unmanaged.
> I am trying to deploy it to a different PC with Win2K, and again - a local
,
> default, instance of MSDE. I copied it to C:\program files\microsoft sql
> server\mssql\binn just as on my pc, added it using "sp_addextendedproc
> 'xp_myproc', 'xp_myproc.dll'", but it gave the error:
> ODBC: Msg 0, Level 16, State 1
> Cannot load the DLL xp_myproc.dll, or one of the DLLs it references. Reaso
n:
> 126(error not found).
> The only thing I can think to be different is that one's got Win2000,
> whereas my dev. PC has got XP. This shouldn't matter though, should it?
> I checked that the names are the correct case, correct names of the
> procedure and the DLL filename and everything.
> What could be wrong?
> Thanks
>|||Nope. Like I say, the following is true of both dev machine, and deployment
target:
There's only one, default, instance of SQL Server (MSDE) on the machine. The
path of the binaries is
c:\program files\microsoft sql server\mssql\binn
and this is the only location where sqlservr.exe is found on the machine.
"Tibor Karaszi" wrote:

> Sorry for the obvious question, but perhaps you added the dll file for the
wrong instance of SQL
> server?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Bonj" <Bonj@.discussions.microsoft.com> wrote in message
> news:A412072A-1157-4908-8D07-96CDBE6788AC@.microsoft.com...
>
>|||I see, it was only a wild guess...
Re-reading the error message, it can also be that the DLL you produced in re
turn references some
other DLL file, and this is the file that cannot be found. (I'm sorry if I'm
stating the obvious
here... :-\).
I'm sure that there are tools out there to check DLL dependencies etc to inv
estigate what the
problem might be. Perhaps some of the VC persons can pick up on that. ;-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bonj" <Bonj@.discussions.microsoft.com> wrote in message
news:18370E98-D316-4A4C-B5D7-BCCD553AC4FD@.microsoft.com...[vbcol=seagreen]
> Nope. Like I say, the following is true of both dev machine, and deploymen
t
> target:
> There's only one, default, instance of SQL Server (MSDE) on the machine. T
he
> path of the binaries is
> c:\program files\microsoft sql server\mssql\binn
> and this is the only location where sqlservr.exe is found on the machine.
>
> "Tibor Karaszi" wrote:
>|||Like I say, it does it the same with a 'blank' extended stored procedure...
i.e. just let the wizard create one, don't add any code, and compile. That
doesn't work on it either.
"Tibor Karaszi" wrote:

> I see, it was only a wild guess...
> Re-reading the error message, it can also be that the DLL you produced in
return references some
> other DLL file, and this is the file that cannot be found. (I'm sorry if I
'm stating the obvious
> here... :-\).
> I'm sure that there are tools out there to check DLL dependencies etc to i
nvestigate what the
> problem might be. Perhaps some of the VC persons can pick up on that. ;-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Bonj" <Bonj@.discussions.microsoft.com> wrote in message
> news:18370E98-D316-4A4C-B5D7-BCCD553AC4FD@.microsoft.com...
>
>|||Possibly some stuff is linked in by the compiling environment. I haven't use
d C or C++ for some 12
years now, so this is only a guess...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bonj" <Bonj@.discussions.microsoft.com> wrote in message
news:67E3A9A6-3BC7-4110-9188-8FC3996555A8@.microsoft.com...[vbcol=seagreen]
> Like I say, it does it the same with a 'blank' extended stored procedure..
.
> i.e. just let the wizard create one, don't add any code, and compile. That
> doesn't work on it either.
>
> "Tibor Karaszi" wrote:
>
SQL[vbcol=seagreen]|||As you suggested I created a default extended stored procedure using
VS.NET'03 on WinXP...I used the depends.exe to determine the dependencies.
Depends.exe reported and error trying to find the OPENDS60.DLL. Perhaps thi
s
is part of your problem...
"Bonj" wrote:
[vbcol=seagreen]
> Like I say, it does it the same with a 'blank' extended stored procedure..
.
> i.e. just let the wizard create one, don't add any code, and compile. That
> doesn't work on it either.
>
> "Tibor Karaszi" wrote:
>|||oh right... i'll check it out when I get back to work..
Cheers for the investigation, appreciate it!
"Mike M" <Mike M@.discussions.microsoft.com> wrote in message
news:D91F9749-AED1-44C0-9AA0-6251A39EE6B2@.microsoft.com...[vbcol=seagreen]
> As you suggested I created a default extended stored procedure using
> VS.NET'03 on WinXP...I used the depends.exe to determine the dependencies.
> Depends.exe reported and error trying to find the OPENDS60.DLL. Perhaps
> this
> is part of your problem...
> "Bonj" wrote:
>|||I ran depends.exe on it and it said this at the bottom in red:
Warning: At least one module has an unresolved import due to a missing
export function in a delay-load dependent module.
How can I find which one, and resolve it?|||It didn't seem to be the opends60.dll, because the target machine has got it
in its SQL server "binn" directory.
"Bonj" <benjtaylor at hotpop d0t com> wrote in message
news:uXSnkr5tEHA.2072@.tk2msftngp13.phx.gbl...
> oh right... i'll check it out when I get back to work..
> Cheers for the investigation, appreciate it!
> "Mike M" <Mike M@.discussions.microsoft.com> wrote in message
> news:D91F9749-AED1-44C0-9AA0-6251A39EE6B2@.microsoft.com...
>

deploying extended stored proc

Hi
I have written an extended stored procedure DLL, thanks to the people who
helped me on this.
however i wonder if anyone could shed any light on my deployment issues that
I'm having with it.
Basically, I built it on my XP development PC with VC7.1. I can deploy it on
this PC's local default instance of MSDE without problems. It uses no managed
code whatsoever, and is compiled without /clr. Totally unmanaged.
I am trying to deploy it to a different PC with Win2K, and again - a local,
default, instance of MSDE. I copied it to C:\program files\microsoft sql
server\mssql\binn just as on my pc, added it using "sp_addextendedproc
'xp_myproc', 'xp_myproc.dll'", but it gave the error:
ODBC: Msg 0, Level 16, State 1
Cannot load the DLL xp_myproc.dll, or one of the DLLs it references. Reason:
126(error not found).
The only thing I can think to be different is that one's got Win2000,
whereas my dev. PC has got XP. This shouldn't matter though, should it?
I checked that the names are the correct case, correct names of the
procedure and the DLL filename and everything.
What could be wrong?
ThanksSorry for the obvious question, but perhaps you added the dll file for the wrong instance of SQL
server?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bonj" <Bonj@.discussions.microsoft.com> wrote in message
news:A412072A-1157-4908-8D07-96CDBE6788AC@.microsoft.com...
> Hi
> I have written an extended stored procedure DLL, thanks to the people who
> helped me on this.
> however i wonder if anyone could shed any light on my deployment issues that
> I'm having with it.
> Basically, I built it on my XP development PC with VC7.1. I can deploy it on
> this PC's local default instance of MSDE without problems. It uses no managed
> code whatsoever, and is compiled without /clr. Totally unmanaged.
> I am trying to deploy it to a different PC with Win2K, and again - a local,
> default, instance of MSDE. I copied it to C:\program files\microsoft sql
> server\mssql\binn just as on my pc, added it using "sp_addextendedproc
> 'xp_myproc', 'xp_myproc.dll'", but it gave the error:
> ODBC: Msg 0, Level 16, State 1
> Cannot load the DLL xp_myproc.dll, or one of the DLLs it references. Reason:
> 126(error not found).
> The only thing I can think to be different is that one's got Win2000,
> whereas my dev. PC has got XP. This shouldn't matter though, should it?
> I checked that the names are the correct case, correct names of the
> procedure and the DLL filename and everything.
> What could be wrong?
> Thanks
>|||Nope. Like I say, the following is true of both dev machine, and deployment
target:
There's only one, default, instance of SQL Server (MSDE) on the machine. The
path of the binaries is
c:\program files\microsoft sql server\mssql\binn
and this is the only location where sqlservr.exe is found on the machine.
"Tibor Karaszi" wrote:
> Sorry for the obvious question, but perhaps you added the dll file for the wrong instance of SQL
> server?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Bonj" <Bonj@.discussions.microsoft.com> wrote in message
> news:A412072A-1157-4908-8D07-96CDBE6788AC@.microsoft.com...
> > Hi
> > I have written an extended stored procedure DLL, thanks to the people who
> > helped me on this.
> > however i wonder if anyone could shed any light on my deployment issues that
> > I'm having with it.
> > Basically, I built it on my XP development PC with VC7.1. I can deploy it on
> > this PC's local default instance of MSDE without problems. It uses no managed
> > code whatsoever, and is compiled without /clr. Totally unmanaged.
> > I am trying to deploy it to a different PC with Win2K, and again - a local,
> > default, instance of MSDE. I copied it to C:\program files\microsoft sql
> > server\mssql\binn just as on my pc, added it using "sp_addextendedproc
> > 'xp_myproc', 'xp_myproc.dll'", but it gave the error:
> > ODBC: Msg 0, Level 16, State 1
> > Cannot load the DLL xp_myproc.dll, or one of the DLLs it references. Reason:
> > 126(error not found).
> >
> > The only thing I can think to be different is that one's got Win2000,
> > whereas my dev. PC has got XP. This shouldn't matter though, should it?
> >
> > I checked that the names are the correct case, correct names of the
> > procedure and the DLL filename and everything.
> >
> > What could be wrong?
> >
> > Thanks
> >
>
>|||I see, it was only a wild guess...
Re-reading the error message, it can also be that the DLL you produced in return references some
other DLL file, and this is the file that cannot be found. (I'm sorry if I'm stating the obvious
here... :-\).
I'm sure that there are tools out there to check DLL dependencies etc to investigate what the
problem might be. Perhaps some of the VC persons can pick up on that. ;-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bonj" <Bonj@.discussions.microsoft.com> wrote in message
news:18370E98-D316-4A4C-B5D7-BCCD553AC4FD@.microsoft.com...
> Nope. Like I say, the following is true of both dev machine, and deployment
> target:
> There's only one, default, instance of SQL Server (MSDE) on the machine. The
> path of the binaries is
> c:\program files\microsoft sql server\mssql\binn
> and this is the only location where sqlservr.exe is found on the machine.
>
> "Tibor Karaszi" wrote:
> > Sorry for the obvious question, but perhaps you added the dll file for the wrong instance of SQL
> > server?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Bonj" <Bonj@.discussions.microsoft.com> wrote in message
> > news:A412072A-1157-4908-8D07-96CDBE6788AC@.microsoft.com...
> > > Hi
> > > I have written an extended stored procedure DLL, thanks to the people who
> > > helped me on this.
> > > however i wonder if anyone could shed any light on my deployment issues that
> > > I'm having with it.
> > > Basically, I built it on my XP development PC with VC7.1. I can deploy it on
> > > this PC's local default instance of MSDE without problems. It uses no managed
> > > code whatsoever, and is compiled without /clr. Totally unmanaged.
> > > I am trying to deploy it to a different PC with Win2K, and again - a local,
> > > default, instance of MSDE. I copied it to C:\program files\microsoft sql
> > > server\mssql\binn just as on my pc, added it using "sp_addextendedproc
> > > 'xp_myproc', 'xp_myproc.dll'", but it gave the error:
> > > ODBC: Msg 0, Level 16, State 1
> > > Cannot load the DLL xp_myproc.dll, or one of the DLLs it references. Reason:
> > > 126(error not found).
> > >
> > > The only thing I can think to be different is that one's got Win2000,
> > > whereas my dev. PC has got XP. This shouldn't matter though, should it?
> > >
> > > I checked that the names are the correct case, correct names of the
> > > procedure and the DLL filename and everything.
> > >
> > > What could be wrong?
> > >
> > > Thanks
> > >
> >
> >
> >|||Like I say, it does it the same with a 'blank' extended stored procedure...
i.e. just let the wizard create one, don't add any code, and compile. That
doesn't work on it either.
"Tibor Karaszi" wrote:
> I see, it was only a wild guess...
> Re-reading the error message, it can also be that the DLL you produced in return references some
> other DLL file, and this is the file that cannot be found. (I'm sorry if I'm stating the obvious
> here... :-\).
> I'm sure that there are tools out there to check DLL dependencies etc to investigate what the
> problem might be. Perhaps some of the VC persons can pick up on that. ;-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Bonj" <Bonj@.discussions.microsoft.com> wrote in message
> news:18370E98-D316-4A4C-B5D7-BCCD553AC4FD@.microsoft.com...
> > Nope. Like I say, the following is true of both dev machine, and deployment
> > target:
> > There's only one, default, instance of SQL Server (MSDE) on the machine. The
> > path of the binaries is
> > c:\program files\microsoft sql server\mssql\binn
> > and this is the only location where sqlservr.exe is found on the machine.
> >
> >
> > "Tibor Karaszi" wrote:
> >
> > > Sorry for the obvious question, but perhaps you added the dll file for the wrong instance of SQL
> > > server?
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "Bonj" <Bonj@.discussions.microsoft.com> wrote in message
> > > news:A412072A-1157-4908-8D07-96CDBE6788AC@.microsoft.com...
> > > > Hi
> > > > I have written an extended stored procedure DLL, thanks to the people who
> > > > helped me on this.
> > > > however i wonder if anyone could shed any light on my deployment issues that
> > > > I'm having with it.
> > > > Basically, I built it on my XP development PC with VC7.1. I can deploy it on
> > > > this PC's local default instance of MSDE without problems. It uses no managed
> > > > code whatsoever, and is compiled without /clr. Totally unmanaged.
> > > > I am trying to deploy it to a different PC with Win2K, and again - a local,
> > > > default, instance of MSDE. I copied it to C:\program files\microsoft sql
> > > > server\mssql\binn just as on my pc, added it using "sp_addextendedproc
> > > > 'xp_myproc', 'xp_myproc.dll'", but it gave the error:
> > > > ODBC: Msg 0, Level 16, State 1
> > > > Cannot load the DLL xp_myproc.dll, or one of the DLLs it references. Reason:
> > > > 126(error not found).
> > > >
> > > > The only thing I can think to be different is that one's got Win2000,
> > > > whereas my dev. PC has got XP. This shouldn't matter though, should it?
> > > >
> > > > I checked that the names are the correct case, correct names of the
> > > > procedure and the DLL filename and everything.
> > > >
> > > > What could be wrong?
> > > >
> > > > Thanks
> > > >
> > >
> > >
> > >
>
>|||Possibly some stuff is linked in by the compiling environment. I haven't used C or C++ for some 12
years now, so this is only a guess...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bonj" <Bonj@.discussions.microsoft.com> wrote in message
news:67E3A9A6-3BC7-4110-9188-8FC3996555A8@.microsoft.com...
> Like I say, it does it the same with a 'blank' extended stored procedure...
> i.e. just let the wizard create one, don't add any code, and compile. That
> doesn't work on it either.
>
> "Tibor Karaszi" wrote:
> > I see, it was only a wild guess...
> >
> > Re-reading the error message, it can also be that the DLL you produced in return references some
> > other DLL file, and this is the file that cannot be found. (I'm sorry if I'm stating the obvious
> > here... :-\).
> >
> > I'm sure that there are tools out there to check DLL dependencies etc to investigate what the
> > problem might be. Perhaps some of the VC persons can pick up on that. ;-)
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Bonj" <Bonj@.discussions.microsoft.com> wrote in message
> > news:18370E98-D316-4A4C-B5D7-BCCD553AC4FD@.microsoft.com...
> > > Nope. Like I say, the following is true of both dev machine, and deployment
> > > target:
> > > There's only one, default, instance of SQL Server (MSDE) on the machine. The
> > > path of the binaries is
> > > c:\program files\microsoft sql server\mssql\binn
> > > and this is the only location where sqlservr.exe is found on the machine.
> > >
> > >
> > > "Tibor Karaszi" wrote:
> > >
> > > > Sorry for the obvious question, but perhaps you added the dll file for the wrong instance of
SQL
> > > > server?
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > http://www.karaszi.com/sqlserver/default.asp
> > > > http://www.solidqualitylearning.com/
> > > >
> > > >
> > > > "Bonj" <Bonj@.discussions.microsoft.com> wrote in message
> > > > news:A412072A-1157-4908-8D07-96CDBE6788AC@.microsoft.com...
> > > > > Hi
> > > > > I have written an extended stored procedure DLL, thanks to the people who
> > > > > helped me on this.
> > > > > however i wonder if anyone could shed any light on my deployment issues that
> > > > > I'm having with it.
> > > > > Basically, I built it on my XP development PC with VC7.1. I can deploy it on
> > > > > this PC's local default instance of MSDE without problems. It uses no managed
> > > > > code whatsoever, and is compiled without /clr. Totally unmanaged.
> > > > > I am trying to deploy it to a different PC with Win2K, and again - a local,
> > > > > default, instance of MSDE. I copied it to C:\program files\microsoft sql
> > > > > server\mssql\binn just as on my pc, added it using "sp_addextendedproc
> > > > > 'xp_myproc', 'xp_myproc.dll'", but it gave the error:
> > > > > ODBC: Msg 0, Level 16, State 1
> > > > > Cannot load the DLL xp_myproc.dll, or one of the DLLs it references. Reason:
> > > > > 126(error not found).
> > > > >
> > > > > The only thing I can think to be different is that one's got Win2000,
> > > > > whereas my dev. PC has got XP. This shouldn't matter though, should it?
> > > > >
> > > > > I checked that the names are the correct case, correct names of the
> > > > > procedure and the DLL filename and everything.
> > > > >
> > > > > What could be wrong?
> > > > >
> > > > > Thanks
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||As you suggested I created a default extended stored procedure using
VS.NET'03 on WinXP...I used the depends.exe to determine the dependencies.
Depends.exe reported and error trying to find the OPENDS60.DLL. Perhaps this
is part of your problem...
"Bonj" wrote:
> Like I say, it does it the same with a 'blank' extended stored procedure...
> i.e. just let the wizard create one, don't add any code, and compile. That
> doesn't work on it either.
>
> "Tibor Karaszi" wrote:
> > I see, it was only a wild guess...
> >
> > Re-reading the error message, it can also be that the DLL you produced in return references some
> > other DLL file, and this is the file that cannot be found. (I'm sorry if I'm stating the obvious
> > here... :-\).
> >
> > I'm sure that there are tools out there to check DLL dependencies etc to investigate what the
> > problem might be. Perhaps some of the VC persons can pick up on that. ;-)
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Bonj" <Bonj@.discussions.microsoft.com> wrote in message
> > news:18370E98-D316-4A4C-B5D7-BCCD553AC4FD@.microsoft.com...
> > > Nope. Like I say, the following is true of both dev machine, and deployment
> > > target:
> > > There's only one, default, instance of SQL Server (MSDE) on the machine. The
> > > path of the binaries is
> > > c:\program files\microsoft sql server\mssql\binn
> > > and this is the only location where sqlservr.exe is found on the machine.
> > >
> > >
> > > "Tibor Karaszi" wrote:
> > >
> > > > Sorry for the obvious question, but perhaps you added the dll file for the wrong instance of SQL
> > > > server?
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > http://www.karaszi.com/sqlserver/default.asp
> > > > http://www.solidqualitylearning.com/
> > > >
> > > >
> > > > "Bonj" <Bonj@.discussions.microsoft.com> wrote in message
> > > > news:A412072A-1157-4908-8D07-96CDBE6788AC@.microsoft.com...
> > > > > Hi
> > > > > I have written an extended stored procedure DLL, thanks to the people who
> > > > > helped me on this.
> > > > > however i wonder if anyone could shed any light on my deployment issues that
> > > > > I'm having with it.
> > > > > Basically, I built it on my XP development PC with VC7.1. I can deploy it on
> > > > > this PC's local default instance of MSDE without problems. It uses no managed
> > > > > code whatsoever, and is compiled without /clr. Totally unmanaged.
> > > > > I am trying to deploy it to a different PC with Win2K, and again - a local,
> > > > > default, instance of MSDE. I copied it to C:\program files\microsoft sql
> > > > > server\mssql\binn just as on my pc, added it using "sp_addextendedproc
> > > > > 'xp_myproc', 'xp_myproc.dll'", but it gave the error:
> > > > > ODBC: Msg 0, Level 16, State 1
> > > > > Cannot load the DLL xp_myproc.dll, or one of the DLLs it references. Reason:
> > > > > 126(error not found).
> > > > >
> > > > > The only thing I can think to be different is that one's got Win2000,
> > > > > whereas my dev. PC has got XP. This shouldn't matter though, should it?
> > > > >
> > > > > I checked that the names are the correct case, correct names of the
> > > > > procedure and the DLL filename and everything.
> > > > >
> > > > > What could be wrong?
> > > > >
> > > > > Thanks
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||oh right... i'll check it out when I get back to work..
Cheers for the investigation, appreciate it!
"Mike M" <Mike M@.discussions.microsoft.com> wrote in message
news:D91F9749-AED1-44C0-9AA0-6251A39EE6B2@.microsoft.com...
> As you suggested I created a default extended stored procedure using
> VS.NET'03 on WinXP...I used the depends.exe to determine the dependencies.
> Depends.exe reported and error trying to find the OPENDS60.DLL. Perhaps
> this
> is part of your problem...
> "Bonj" wrote:
>> Like I say, it does it the same with a 'blank' extended stored
>> procedure...
>> i.e. just let the wizard create one, don't add any code, and compile.
>> That
>> doesn't work on it either.
>>
>> "Tibor Karaszi" wrote:
>> > I see, it was only a wild guess...
>> >
>> > Re-reading the error message, it can also be that the DLL you produced
>> > in return references some
>> > other DLL file, and this is the file that cannot be found. (I'm sorry
>> > if I'm stating the obvious
>> > here... :-\).
>> >
>> > I'm sure that there are tools out there to check DLL dependencies etc
>> > to investigate what the
>> > problem might be. Perhaps some of the VC persons can pick up on that.
>> > ;-)
>> >
>> > --
>> > Tibor Karaszi, SQL Server MVP
>> > http://www.karaszi.com/sqlserver/default.asp
>> > http://www.solidqualitylearning.com/
>> >
>> >
>> > "Bonj" <Bonj@.discussions.microsoft.com> wrote in message
>> > news:18370E98-D316-4A4C-B5D7-BCCD553AC4FD@.microsoft.com...
>> > > Nope. Like I say, the following is true of both dev machine, and
>> > > deployment
>> > > target:
>> > > There's only one, default, instance of SQL Server (MSDE) on the
>> > > machine. The
>> > > path of the binaries is
>> > > c:\program files\microsoft sql server\mssql\binn
>> > > and this is the only location where sqlservr.exe is found on the
>> > > machine.
>> > >
>> > >
>> > > "Tibor Karaszi" wrote:
>> > >
>> > > > Sorry for the obvious question, but perhaps you added the dll file
>> > > > for the wrong instance of SQL
>> > > > server?
>> > > >
>> > > > --
>> > > > Tibor Karaszi, SQL Server MVP
>> > > > http://www.karaszi.com/sqlserver/default.asp
>> > > > http://www.solidqualitylearning.com/
>> > > >
>> > > >
>> > > > "Bonj" <Bonj@.discussions.microsoft.com> wrote in message
>> > > > news:A412072A-1157-4908-8D07-96CDBE6788AC@.microsoft.com...
>> > > > > Hi
>> > > > > I have written an extended stored procedure DLL, thanks to the
>> > > > > people who
>> > > > > helped me on this.
>> > > > > however i wonder if anyone could shed any light on my deployment
>> > > > > issues that
>> > > > > I'm having with it.
>> > > > > Basically, I built it on my XP development PC with VC7.1. I can
>> > > > > deploy it on
>> > > > > this PC's local default instance of MSDE without problems. It
>> > > > > uses no managed
>> > > > > code whatsoever, and is compiled without /clr. Totally unmanaged.
>> > > > > I am trying to deploy it to a different PC with Win2K, and
>> > > > > again - a local,
>> > > > > default, instance of MSDE. I copied it to C:\program
>> > > > > files\microsoft sql
>> > > > > server\mssql\binn just as on my pc, added it using
>> > > > > "sp_addextendedproc
>> > > > > 'xp_myproc', 'xp_myproc.dll'", but it gave the error:
>> > > > > ODBC: Msg 0, Level 16, State 1
>> > > > > Cannot load the DLL xp_myproc.dll, or one of the DLLs it
>> > > > > references. Reason:
>> > > > > 126(error not found).
>> > > > >
>> > > > > The only thing I can think to be different is that one's got
>> > > > > Win2000,
>> > > > > whereas my dev. PC has got XP. This shouldn't matter though,
>> > > > > should it?
>> > > > >
>> > > > > I checked that the names are the correct case, correct names of
>> > > > > the
>> > > > > procedure and the DLL filename and everything.
>> > > > >
>> > > > > What could be wrong?
>> > > > >
>> > > > > Thanks
>> > > > >
>> > > >
>> > > >
>> > > >
>> >
>> >
>> >|||I ran depends.exe on it and it said this at the bottom in red:
Warning: At least one module has an unresolved import due to a missing
export function in a delay-load dependent module.
How can I find which one, and resolve it?|||It didn't seem to be the opends60.dll, because the target machine has got it
in its SQL server "binn" directory.
"Bonj" <benjtaylor at hotpop d0t com> wrote in message
news:uXSnkr5tEHA.2072@.tk2msftngp13.phx.gbl...
> oh right... i'll check it out when I get back to work..
> Cheers for the investigation, appreciate it!
> "Mike M" <Mike M@.discussions.microsoft.com> wrote in message
> news:D91F9749-AED1-44C0-9AA0-6251A39EE6B2@.microsoft.com...
>> As you suggested I created a default extended stored procedure using
>> VS.NET'03 on WinXP...I used the depends.exe to determine the
>> dependencies.
>> Depends.exe reported and error trying to find the OPENDS60.DLL. Perhaps
>> this
>> is part of your problem...
>> "Bonj" wrote:
>> Like I say, it does it the same with a 'blank' extended stored
>> procedure...
>> i.e. just let the wizard create one, don't add any code, and compile.
>> That
>> doesn't work on it either.
>>
>> "Tibor Karaszi" wrote:
>> > I see, it was only a wild guess...
>> >
>> > Re-reading the error message, it can also be that the DLL you produced
>> > in return references some
>> > other DLL file, and this is the file that cannot be found. (I'm sorry
>> > if I'm stating the obvious
>> > here... :-\).
>> >
>> > I'm sure that there are tools out there to check DLL dependencies etc
>> > to investigate what the
>> > problem might be. Perhaps some of the VC persons can pick up on that.
>> > ;-)
>> >
>> > --
>> > Tibor Karaszi, SQL Server MVP
>> > http://www.karaszi.com/sqlserver/default.asp
>> > http://www.solidqualitylearning.com/
>> >
>> >
>> > "Bonj" <Bonj@.discussions.microsoft.com> wrote in message
>> > news:18370E98-D316-4A4C-B5D7-BCCD553AC4FD@.microsoft.com...
>> > > Nope. Like I say, the following is true of both dev machine, and
>> > > deployment
>> > > target:
>> > > There's only one, default, instance of SQL Server (MSDE) on the
>> > > machine. The
>> > > path of the binaries is
>> > > c:\program files\microsoft sql server\mssql\binn
>> > > and this is the only location where sqlservr.exe is found on the
>> > > machine.
>> > >
>> > >
>> > > "Tibor Karaszi" wrote:
>> > >
>> > > > Sorry for the obvious question, but perhaps you added the dll file
>> > > > for the wrong instance of SQL
>> > > > server?
>> > > >
>> > > > --
>> > > > Tibor Karaszi, SQL Server MVP
>> > > > http://www.karaszi.com/sqlserver/default.asp
>> > > > http://www.solidqualitylearning.com/
>> > > >
>> > > >
>> > > > "Bonj" <Bonj@.discussions.microsoft.com> wrote in message
>> > > > news:A412072A-1157-4908-8D07-96CDBE6788AC@.microsoft.com...
>> > > > > Hi
>> > > > > I have written an extended stored procedure DLL, thanks to the
>> > > > > people who
>> > > > > helped me on this.
>> > > > > however i wonder if anyone could shed any light on my deployment
>> > > > > issues that
>> > > > > I'm having with it.
>> > > > > Basically, I built it on my XP development PC with VC7.1. I can
>> > > > > deploy it on
>> > > > > this PC's local default instance of MSDE without problems. It
>> > > > > uses no managed
>> > > > > code whatsoever, and is compiled without /clr. Totally
>> > > > > unmanaged.
>> > > > > I am trying to deploy it to a different PC with Win2K, and
>> > > > > again - a local,
>> > > > > default, instance of MSDE. I copied it to C:\program
>> > > > > files\microsoft sql
>> > > > > server\mssql\binn just as on my pc, added it using
>> > > > > "sp_addextendedproc
>> > > > > 'xp_myproc', 'xp_myproc.dll'", but it gave the error:
>> > > > > ODBC: Msg 0, Level 16, State 1
>> > > > > Cannot load the DLL xp_myproc.dll, or one of the DLLs it
>> > > > > references. Reason:
>> > > > > 126(error not found).
>> > > > >
>> > > > > The only thing I can think to be different is that one's got
>> > > > > Win2000,
>> > > > > whereas my dev. PC has got XP. This shouldn't matter though,
>> > > > > should it?
>> > > > >
>> > > > > I checked that the names are the correct case, correct names of
>> > > > > the
>> > > > > procedure and the DLL filename and everything.
>> > > > >
>> > > > > What could be wrong?
>> > > > >
>> > > > > Thanks
>> > > > >
>> > > >
>> > > >
>> > > >
>> >
>> >
>> >
>

Deploying CLR Stored Procedures to a client, is there a better way

I'm developing an application locally, and after I complete major revisitions/bugfixes, I transfer the code/database changes to the client. If I change the code for a CLR stored procedure locally, I can just deploy the project to the local database, which handles everything for me.

Transferring to the client's database is more difficult, however. In order to update the CLR procedures, I need to drop my regular stored procedures, drop my functions, drop the CLR procs, and then re-add the CLR procs, functions and stored procedures to avoid errors based on dependencies. The stored procs/functions don't change, and it seems like there should be a way to just insert the new CLR stuff without dealing with the regular stored procedures or userdefined functions.

Basically, I'm asking whether there is a better way to do this, or if I'm stuck doing this excessive routine every time I need to update.

Thanks in advance

it sounds like your in the "debug" phase right now, so you could use multiple assemblies to seperate those SQLCLR routines that you know are still giving you problems and those that are not. Once fully tested you could then re-merge them back into one assembly.

You could also look at using the scripting wizard to script the assembly and all of it's dependencies from your local/dev box.

Derek

|||

Have you tried using ALTER ASSEMBLY from T-SQL to deploy changes to the assembly.

Detailed documentation on ALTER ASSEMBLY is available at http://msdn2.microsoft.com/en-us/library/ms186711(d=ide).aspx

Thanks,

-Vineet

Deploying CLR from VStudio -- issue with permissions

I have a CLR project in my VStudio solution.

Whenever I deploy the project to SQL, the deployment will be successful, but, because the stored proc wrappers that get made in SQL are remade, all permissions that I had applied to those stored procs are gone and have to be reentered through SQL. I have made a TransactSQL script to run to automate this. Basically, what I need is to give them a group permission that is associated with the tables that they work with.

I gotta figure I am missing something simple that would allow my permissions to stick.

?

You can create "postdeployscript.sql" with everything you need to be executed after deployment, and put it to the root of the SQL-CLR project. Same holds for "predeployscript.sql" - it will be executed before deployment

|||Thanks -- that solves my problem.

Deploying a website with user authentication - getting errors

I have built a site that uses form-based authentication. Am I correct in assuming that the users and passwords will be stored in the SQLServer database in the aspnetdb.mdf file? The website works great and the authentication works how I want it to. However, I've tried to copy over the file onto a remote machine. The first page is a login screen and this is displayed without any problems but as soon as I attempt to log in I get an error message of:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

I'm wondering if this is something to do with the fact I installed SQLServer Express edition on the remote machine. The website works no problem on the local machine in debug mode of Visual Studio 2005 (Professional Edition). I followed instructions to enable remote connections on SQLServer Express from a Microsoft support doc. If anybody can point me in the right direction I'll be very grateful as I've hit a wall here.

Did you check the connection string in web.config if it is pointing to the right server. Also, if you are using aspnetdb.mdf, you should have at least SQL Server Express edition installed on the machine hosting the website.

|||

Hi. Thanks for replying. The only connection string I have in my webconfig file is for an Oracle database that I access elsewhere in the website. Since the original website works in the debug mode of Vis Studio 2005 without any other connection string then I just copied the webconfig file over to the remote server as it was. Does that mean I have to add a connection string to the aspnetdb.mdf file? (The server has SQLServer Express installed on it.)

|||

Have a look at this and try adding a connectionstring to the server.
https://help.maximumasp.com/SmarterTicket/Customer/KBArticle.aspx?articleid=878

You might have to attach the local mdf file using this in the connection string.

AttachDBFilename=|DataDirectory|aspnet.mdf 
connectionStrings> <remove name="LocalSqlServer" /> <add name="LocalSqlServer" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient"/></connectionStrings>
 

Deploy on the hoster

Dears,

I have devoloped an application ASP.NET 2.0.

Before I have builded the aspnetdb throught the command then I built some tables and stored procedures on this db. (My db is sql express 2005 and my hoster db is sql 2005 workgroup)

My hoster doesn't allow connection Management studio express, doesn't allow attach or restore functionalities.

Than I have built my script db (contains Tables, Views and Stored Procedures), I have substituted the dbo with my user account in the script because my hoster doesn't allow the dbo access.

I have also transfered my site files (.aspx, img, etc.) on the server.

When I try to access the db I receive this error (for example when I push on the button create user):

The SSE Provider did not find the database file specified in the connection string. At the configured trust level (below High trust level), the SSE provider can not automatically create the database file.

Please could you help me?

Thank you.

I Forgot one thing!!

For allow the access throught an user different from dbo I have modified the "ASP.NET Provider Toolkit SQL Samples".

Thank you

Depending on the selection of the user of the asp.net web-form how can i do changes in the

Hi frdz,

  I have created the following stored procedure in sql server 2005.

 In my database i have one option for the payment mode which can be done thru cash or credit(cheque).  

 I have created my web-application in asp.net with C# 2005.

 There i have a dropdownlist box for the user to select the option whether wants to do the payment thru cash or cheque.

Depending on that selection if user selects cheque then all the reqt for cheque like it's no,dt,bankname etc...are visible.

but if user selects the option as cash then the cheque details become invisible.

Depending on the selection of the user of the asp.net web-form how can i do changes in the stored procedure...  

i can write the condition like

if paymentmode=cash

then ........

else

..........

but where and how can it be written ...pls tell me

thanxs in adv...u can go thru my below SP  

ALTER PROCEDURE MiscellaneousStoredProcedure @.miscidint output, @.storenamevarchar(20),--store name to storeid @.accountnamevarchar(20), @.groupnamevarchar(20), @.paymentdtdatetime,@.payeenamevarchar(30),@.paymodevarchar(20),@.banknamevarchar(50), @.chqdtdatetime, @.chqnovarchar(20), @.amtnumeric(10, 2),@.balnumeric(10, 2), @.remarksvarchar(50)asdeclare@.storeidint,@.accountidint,@.groupidintbeginset nocount onselect @.miscid =isnull(max(@.miscid),0) + 1from miscellaneourpayifexists (select *from storemasterwhere storename = @.storename)select @.storeid = storeidfrom storemasterwhere storename = @.storenameifexists (select *from accountmasterwhere accountname =@.accountname)select @.accountid = accountidfrom accountmasterwhere accountname =@.accountnameifexists (select *from accountgroupmasterwhere groupname=@.groupname)select @.groupid=groupidfrom accountgroupmasterwhere groupname=@.groupnamebegin transactioninsert into miscellaneourpay(miscid,storeid,accountid,groupid,paymentdt,paymode,payeename,bankname,chqdt,chqno,amt,bal, remarks)values( @.miscid,@.storeid,@.accountid,@.groupid,@.paymentdt,@.paymode,@.payeename,@.bankname,@.chqdt,@.chqno,@.amt,@.bal,@.remarks)commit transaction end

What about creating two different stored Proceedures for both cases?|||

Hi,

Try to use two different stroed procedure for cash and credit. The value from your DropDownList decides which one to use. In this way,you can make your stored procedure easier.

Thanks.

Dependencies.

I am trying to ascertain dependencies of a certain stored proc, but it
seems that the dependencies are out of date. How do I force the
dependencies to refresh? (SQL 2000)
Regards
Short of dropping and recreating all the objects you don't. Never trust the
dependencies to be up to date.
Andrew J. Kelly SQL MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:ePB8NVUIGHA.3936@.TK2MSFTNGP12.phx.gbl...
>I am trying to ascertain dependencies of a certain stored proc, but it
>seems that the dependencies are out of date. How do I force the
>dependencies to refresh? (SQL 2000)
> Regards

Dependencies.

I am trying to ascertain dependencies of a certain stored proc, but it
seems that the dependencies are out of date. How do I force the
dependencies to refresh? (SQL 2000)
RegardsShort of dropping and recreating all the objects you don't. Never trust the
dependencies to be up to date.
Andrew J. Kelly SQL MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:ePB8NVUIGHA.3936@.TK2MSFTNGP12.phx.gbl...
>I am trying to ascertain dependencies of a certain stored proc, but it
>seems that the dependencies are out of date. How do I force the
>dependencies to refresh? (SQL 2000)
> Regards

Dependencies.

I am trying to ascertain dependencies of a certain stored proc, but it
seems that the dependencies are out of date. How do I force the
dependencies to refresh? (SQL 2000)
RegardsShort of dropping and recreating all the objects you don't. Never trust the
dependencies to be up to date.
--
Andrew J. Kelly SQL MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:ePB8NVUIGHA.3936@.TK2MSFTNGP12.phx.gbl...
>I am trying to ascertain dependencies of a certain stored proc, but it
>seems that the dependencies are out of date. How do I force the
>dependencies to refresh? (SQL 2000)
> Regards

Dependencies not correct with temporary tables --> replication is failing

Hello all,

here is a stored procedure I have:

CREATE PROCEDURE spU_GUI_AppliqueConditionFinancementPourGuichet
(
@.GuichetId int,
@.Validateur nvarchar(40)
)
AS
CREATE TABLE #tReservations (ReservationId int)

IF (dbo.GetSiGuichetEnRegle(@.GuichetId) = 0)
INSERT #tReservations
EXECUTE spU_GUI_AppliquePerteFinancement @.GuichetID, @.Validateur
ELSE
INSERT #tReservations
EXECUTE spU_GUI_AppliquePerteAgrement @.GuichetID, @.Validateur

SELECT GR.Id,
dbo.FormateNoms(GR.Name) AS Names
FROM #tReservations
LEFT JOIN AnotherTable GR ON GR.Id = AnotherTable.id
DROP TABLE #tReservations
GO

The creation is ok but when I look to the dependencies, I see that it depends on GetSiGuichetEnRegle only.

For me, it shall also depend on

AnotherTable

spU_GUI_AppliquePerteFinancement

spU_GUI_AppliquePerteAgrement

FormateNoms

Apparently the dependencies are not calculated correctly because I'm using a temporary table.

My problem is that I have updated this stored procedures (and the two other that I call) to add a new parameter. As a consequence, when I do a replication, this is failing saying that I have an extra parameter. I imagine that because my dependencies are not correct, the replication is not occuring in the correct order and so it's still using the old definition of the stored procedure.

Do you have any idea on how I can force the dependencies to be calculated correctly ?

Thanks

Did the error happen when you are applying a snapshot or when the distribution agent is applying a DDL change for the stored procedure update? If this happened while the snapshot was being delivered to the subscriber, I have the following questions for you:

1) Are you using 'drop' as the pre-creation command for the stored procedure articles? If so, you should theoretically not be seeing the error that you saw when the snapshot was delivered to the subscriber since the referenced procedures would either have been drop (or recreated with the new definition).

2) Are the referenced stored procedures (spU_GUI_AppliquePerteFinancement and spU_GUI_AppliquePerteAgrement) included in the publication as well? (It never hurts to ask the obvious.)

3) It would be great if you can post history messages of the distribution agent when the snapshot was applied, I just want to see the relative order of how the objects that you mentioned are applied. I will also be interested to see the history messages from the snapshot agent.

There is an undocumented -EnableStoredProcedureDependenciesReevaluation 1 switch in the snapshot agent which forces the snapshot agent to compute dependency ordering for stored procedure articles in a more accurate manner but I have a feeling that the real problem is something else.

-Raymond

|||

Hello Raymond,

Indeed there seems to be problems with the dependencies between stored procedures and tables, but the main issue here seems to be to be related to the latest problem we spoke together

After isolating the problem, I've found that the stored procedure that could not be replicated contains a "INSERT INTO" statement.

I will try a work-around by playing with post-replication scripts, while waiting for SP2 ;-)

Anyway, I will investigate the solutions you give for the dependencies

Pierre-Emmanuel

Dependencies

how and when are dependencies created. I know when tables are linked in a
view then a dependency is created what about stored proceedures?
Thanks
Dependencies are created when objects are created altered. These include
the list of all objects in the current database that the object directly
references.
Note that dependency information can be inaccurate when objects are not
created in proper dependency order. For example, you can create a proc
before the referenced table is created. No record of this dependency is
created in this case. Similarly, if you drop and recreate the table, the
dependency info is deleted but not recreated.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:613A82D1-7040-4FE2-808E-4109340EEB26@.microsoft.com...
> how and when are dependencies created. I know when tables are linked in a
> view then a dependency is created what about stored proceedures?
> Thanks
|||The sp_rename will give you all sorts of errors about breaking dependencies
as well.
Sincerely,
Anthony Thomas

"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uNzDMJb2EHA.2568@.TK2MSFTNGP10.phx.gbl...
Dependencies are created when objects are created altered. These include
the list of all objects in the current database that the object directly
references.
Note that dependency information can be inaccurate when objects are not
created in proper dependency order. For example, you can create a proc
before the referenced table is created. No record of this dependency is
created in this case. Similarly, if you drop and recreate the table, the
dependency info is deleted but not recreated.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:613A82D1-7040-4FE2-808E-4109340EEB26@.microsoft.com...
> how and when are dependencies created. I know when tables are linked in a
> view then a dependency is created what about stored proceedures?
> Thanks

Dependencies

how and when are dependencies created. I know when tables are linked in a
view then a dependency is created what about stored proceedures?
ThanksDependencies are created when objects are created altered. These include
the list of all objects in the current database that the object directly
references.
Note that dependency information can be inaccurate when objects are not
created in proper dependency order. For example, you can create a proc
before the referenced table is created. No record of this dependency is
created in this case. Similarly, if you drop and recreate the table, the
dependency info is deleted but not recreated.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:613A82D1-7040-4FE2-808E-4109340EEB26@.microsoft.com...
> how and when are dependencies created. I know when tables are linked in a
> view then a dependency is created what about stored proceedures?
> Thanks|||The sp_rename will give you all sorts of errors about breaking dependencies
as well.
Sincerely,
Anthony Thomas
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uNzDMJb2EHA.2568@.TK2MSFTNGP10.phx.gbl...
Dependencies are created when objects are created altered. These include
the list of all objects in the current database that the object directly
references.
Note that dependency information can be inaccurate when objects are not
created in proper dependency order. For example, you can create a proc
before the referenced table is created. No record of this dependency is
created in this case. Similarly, if you drop and recreate the table, the
dependency info is deleted but not recreated.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:613A82D1-7040-4FE2-808E-4109340EEB26@.microsoft.com...
> how and when are dependencies created. I know when tables are linked in a
> view then a dependency is created what about stored proceedures?
> Thanks

Dependant assemblies in CLR

This is related to one of my previous posts.

I am running a CLR stored proc that goes to an EDS (Novell) server with LDAP and returns records into a SQL table.

I am using the Novell ldap library.

I want to do this with SSL so my code referneces the Mono security library as well.

However when I make the call to the stored proc to run in SSL, I get an object not found error. I do not think that the the Novell assembly can "find" the Mono assembly.

Two points:
1/ I can do the SSL if I run it as an asp.net page (so I know the SSL works)
2/ The proc runs and pulls all the records in non-SSL (so I know the proc works)

Any ideas?

Thanks,

BIG

Hi BIG,

Are you loading the Novell library into SQL? One restriction with CLR integration is that outside of a static list of "approved" assemblies that we access in the GAC, all assemblies must be loaded into SQL.

Cheers,

-Isaac

|||Yes, they are loaded assemblies.
New info:

System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoadFrom(String assemblyFile, Evidence securityEvidence, Byte[] hashValue, AssemblyHashAlgorithm hashAlgorithm, Boolean forIntrospection, StackCrawlMark& stackMark)
at System.Reflection.Assembly.LoadFrom(String assemblyFile)
at Novell.Directory.Ldap.Connection.connect(String host, Int32 port, Int32 semaphoreId)|

what the heck?|||

Hi BIG,

As you surmised, Novell is trying to load the Mono security assembly as part of the connect method. The problem is that dynamically loading assemblies under SQL CLR is explicitly disallowed as the error message states.

Unfortunately, Novell is explicitly calling Assembly.LoadFrom("Mono.Security.dll") which will always try to load the assembly from disk so there is no easy way to work around this problem. If instead the call had been Assembly.Load with the Mono.Security DLL's four-part name then you could solve this by preloading the Mono.Security.Dll into SQL Server yourself.

Steven

|||What if I use the sgen.exe tool and serialize the assembly and load teh serialized assembly?|||

That is the solution for a different problem where Xml Serialization also tries to load a dynamic assembly. Good try though.

Without changing the Novell source code to use Assembly.Load rather than Assembly.LoadFrom, I don't think there is any way around this. Sorry I can't be of any help.

Steven

|||Believe it or not, I got the source code for that .dll and did change it to Load now it does not want to work...gonna contact Novell.

Thanks,

BIG|||

So the Mono.Security assembly now loads correctly, but you're hitting a different error that prevents you from using it? Or are you still running into Loading issues?

|||Loading issue...
Original code: Assembly.LoadFrom("Mono.Security.dll");
My code: Assembly.Load("Mono.Security.dll");

Actually:

// Load Mono.Security.dll
Assembly a;
try
{
a = Assembly.Load("Mono.Security.dll");
}
catch(System.IO.FileNotFoundException)
{
throw new LdapException(ExceptionMessages.SSL_PROVIDER_MISSING,LdapException.SSL_PROVIDER_NOT_FOUND, null);
}

Error message is from the Exception thrown when the assembly is not found.

I am gonna research other ways to load an assembly, but I am sure I am screwed here, will try to post this in the relavant Novell forum as well.

What I may end up doing is trying to integrate these two assemblies into one (which I think Novell should have done in the first place :( )
OR
Making this into a standard exec and have SQL Server run the job calling that exec every night, so not in CLR, and lose portability with db.

Unless anyone has a better idea?

Thanks,

BIG S|||

Do you have the Mono.Security.Dll loaded in SQL Server yet? In order for the Assembly.Load to suceed, the assembly already needs to exist in your appdomain. So you need to do CREATE ASSEMBLY [Mono.Security] FROM 'path\Mono.Security.dll' with permission_set = unsafe first.

Also, you need to pass the full, 4-part assembly name to the Mono.Security assembly as documented: http://msdn2.microsoft.com/en-us/library/ky3942xh.aspx. You can get the 4-part name from sys.assemblies.

If this still doesn't work, you can verify that the Mono.Security.dll is reported as being loaded in your appdomain from the sys.dm_clr_loaded_assemblies dmv. If it's not there, try doing a dummy CREATE FUNCTION foo() returns int as external name [Mono.Security].bar.foo. (Sorry I can't verify if this is necessary right now - I just moved to Vista at home and don't have SQL Server installed yet).

Steven

|||ok working on it...

BTW: Even if I can't get it going, Steven u rock!

BIG S|||You are bloody brilliant...I got it working!

If you are ever in Toronto, beer is on me!

BIG S

Deny user roles from a Stored Procedure.

Hello,

I am trying to deny a user from processing a query, however the user still seems to be able to see the procedure.

I have executed the following command on both the 'Public' and specific user name.

DENY EXECUTE ON [OBJECT_NAME] TO [PUBLIC]

However when i run the command 'sp_helprotect OBJECT_NAME' afterwards it shows that deny is set on 'Public', yet the user can still run the procedure from a web report... I have tried restarting IIS and this also does nothing, any ideas?

Thanks,

RichAre you using windows authentication? If so, make sure that a Windows group does not have execute authority on the procedure. I would also check to make sure the user acount that is the problem does not have an elevated role like db_owner or a system role like sysadmin. I'm assuming that you verified that the application is actually using the login you have been denying permissions on.|||

Quote:

Originally Posted by rob313

Are you using windows authentication? If so, make sure that a Windows group does not have execute authority on the procedure. I would also check to make sure the user acount that is the problem does not have an elevated role like db_owner or a system role like sysadmin. I'm assuming that you verified that the application is actually using the login you have been denying permissions on.

the user might be a "dbo".|||If the user is dbo then SQL Server does not check any other permissions (grant or deny), so that would be why you are seeing this behavior. You will need to remove the user from dbo and grant that account the needed permissions or role.