Home > IT Answers > SQL Server > SQL Server 2008 extended stored procedures
KH
50 pts.
Q:
SQL Server 2008 extended stored procedures
SQL Server 2008 extended stored procedures, SQL Server 2008, SQL Server extended stored procedures, SQL Server 2005
I have created several extended stored procedures in SQL 2008 which when i run always return a NULL. I call these XP's from within a stored procedure. They work fine in SQL 2005 and SQL 2000. One thing i have noticed is when i click on the properties of the xp the path to the DLL is not there is that normal in 2008 (cos it shows the path in sql 2005).

When i run the XP in Query mode without calling from another stored procedure i get error:
Could not load the DLL "C:\program files\....."


syntax i have used to create:
exec sp_addextendedproc N'xp_Commission', N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008TEST1\MSSQL\Binn\xp_commission.dll'


make call like so on the master DB:
Exec Master..xp_Commission

Thanks in Advance
ASKED: Mar 24 2009  10:41 AM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
KH
50 pts.
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • Bookmark and Share
Last Answered: Mar 24 2009  10:41 AM GMT by KH   50 pts.
  •   
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

mrdenny   49385 pts.  |   Mar 25 2009  4:45AM GMT

What’s the rest of the error message when you run the extended stored procedure by it self?

Any reason you aren’t writing these as SQLCLR procedures?

 

KH   50 pts.  |   Mar 25 2009  10:33AM GMT

Hi Thanks for you reply

I call the procedure like so:

Declare @sText as varchar(100)
Set @sText = ‘”123456789″
Exec master..xp_commission @text

Error i get is:

Msg 17750, Level 16, State 0, Procedure xp_commission, Line 1
Could not load the DLL C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008AMSSQLBinnxp_commission.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).

CLR - Im not a developer and also i cant seem to find the original source code in C++

Thanks again for you help

 

KH   50 pts.  |   Mar 25 2009  10:34AM GMT

Sorry i made a typo in the previous post

Exec master..xp_commission @text

Should be

Exec master..xp_commission @sText

 

KH   50 pts.  |   Mar 26 2009  11:32AM GMT

Hi

Thanks Mr Denny for your responses ,I have now sorted out the problem .

The SQL 2008 machine was a new build. So i ran my DLLs through dependency walker and it highlighted a dll that my commission.dll was referencing (MSVCR71.DLL) , i copied the missing Dll to the SQL2008 Machine and it now works. YES!!!

Thanks anyway…