Adding an Extended Stored Procedure to SQL Server
Important
This feature will be removed in a future version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use CLR Integration instead.
A DLL that contains extended stored procedure functions acts as an extension to Microsoft SQL Server. To install the DLL, copy the file to a directory, such as the one containing the standard SQL Server DLL files (C:\Program Files\Microsoft SQL Server\MSSQL.x\MSSQL\Binn by default).
After the extended stored procedure DLL has been copied to the server, a SQL Server system administrator must register to SQL Server each extended stored procedure function in the DLL. This is done using the sp_addextendedproc system stored procedure.
.gif "ms164653.security(en-US,SQL.90).gif") Security Note: |
|---|
| The system administrator should thoroughly review an extended stored procedure to ensure that it does not contain harmful or malicious code before adding it to the server and granting execute permissions to other users. Validate all user input. Do not concatenate user input before validating it. Never execute a command constructed from unvalidated user input. For more information about validating input, see SQL Injection. |
The first parameter of sp_addextendedproc specifies the name of the function, and the second parameter specifies the name of the DLL in which that function resides. It is recommended that you specify the complete path of the DLL.
Important
Existing DLLs that were not registered with a complete path will not work after upgrading to SQL Server 2005. To correct the problem, use sp_dropextendedproc to unregister the DLL, and then reregister it with sp_addextendedproc, specifying the complete path.
The name of the function specified in sp_addextendedproc must be exactly the same, including the case, as the function's name in the DLL. For example, this command registers a function xp_hello, located in a dll named xp_hello.dll, as a SQL Server extended stored procedure:
sp_addextendedproc 'xp_hello', 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\xp_hello.dll'
If the name of the function specified in sp_addextendedproc does not exactly match the function name in the DLL, the new name will be registered in SQL Server, but the name will not be usable. For example, although xp_Hello is registered as a SQL Server extended stored procedure located in xp_hello.dll, SQL Server will not be able to find the function in the DLL if you use xp_Hello to call the function later.
--Register the function (xp_hello) with an initial upper case
sp_addextendedproc 'xp_Hello', 'c:\xp_hello.dll'
--Use the newly registered name to call the function
DECLARE @txt varchar(33)
EXEC xp_Hello @txt OUTPUT
--This is the error message
Server: Msg 17750, Level 16, State 1, Procedure xp_Hello, Line 1
Could not load the DLL xp_hello.dll, or one of the DLLs it references. Reason: 127(The specified procedure could not be found.).
If the name of the function specified in sp_addextendedproc matches exactly the function name in the DLL, and the collation of the SQL Server instance is case-insensitive, the user can call the extended stored procedure using any combination of lower- and upper-case letters of the name.
--Register the function (xp_hello)
sp_addextendedproc 'xp_hello', 'c:\xp_hello.dll'
--The following will succeed in calling xp_hello
DECLARE @txt varchar(33)
EXEC xp_Hello @txt OUTPUT
DECLARE @txt varchar(33)
EXEC xp_HelLO @txt OUTPUT
DECLARE @txt varchar(33)
EXEC xp_HELLO @txt OUTPUT
When the collation of the SQL Server instance is case-sensitive, SQL Server will not be able to call the extended stored procedure -- even if it was registered with exactly the same name and collation as the function in the DLL -- if the procedure is called with a different case.
--Register the function (xp_hello)
sp_addextendedproc 'xp_hello', 'c:\xp_hello.dll'
--The following will result in an error
DECLARE @txt varchar(33)
EXEC xp_HELLO @txt OUTPUT
--This is the error
Server: Msg 2812, Level 16, State 62, Line 1
It is not necessary to stop and restart SQL Server.
See Also
Other Resources
sp_addextendedproc (Transact-SQL)
sp_dropextendedproc (Transact-SQL)