Export (0) Print
Expand All

Adding an Extended Stored Procedure to SQL Server

SQL Server 2000

A DLL that contains extended stored procedure functions acts as an extension to Microsoft® SQL Server™. To install the DLL, copy the file to the directory containing the standard SQL Server DLL files (C:\Program Files\Microsoft SQL Server\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. 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. 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 the function xp_hello, located in xp_hello.dll, as a SQL Server extended stored procedure:

sp_addextendedproc 'xp_hello', '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', '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
Cannot find the function xp_Hello in the library c:\xp_hello.dll. 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', '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 was called with a different case.

--Register the function (xp_hello)
sp_addextendedproc 'xp_hello', 'xp_hello.dll'

--The following will result in an error
DECLARE @txt varchar(33)
EXEC xp_HELLO @txt OUTPUT

--This is the error
Could not find stored procedure 'xp_HELLO'.

It is not necessary to stop and restart SQL Server. This is in a sample only available if you select Dev Tools during setup.

See Also

sp_addextendedproc

Show:
© 2014 Microsoft