Sql Advice: proc

Showing posts with label proc. Show all posts

Friday, March 9, 2012

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
>> > > > >
>> > > >
>> > > >
>> > > >
>> >
>> >
>> >
>

Wednesday, March 7, 2012

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.

Friday, February 17, 2012

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

Tuesday, February 14, 2012

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

Friday, March 9, 2012

deploying extended stored proc

deploying extended stored proc

deploying extended stored proc

Wednesday, March 7, 2012

Deploying CLR from VStudio -- issue with permissions

Friday, February 17, 2012

Dependencies.

Dependencies.

Dependencies.

Tuesday, February 14, 2012

Dependant assemblies in CLR

Sql Advice

Blog Archive

About Me