Adding Extended Stored Procedures to SQL Server

A DLL application file that contains extended stored procedure functions forms an extension to SQL Server itself. To install the DLL file, copy the file to the same directory as the standard SQL Server DLL files. As with other DLL files, once the extended stored procedure DLL is placed in the appropriate directory and the appropriate paths are set, you can make its functions available to users immediately ¾ it is not necessary to restart the server.

For each function provided in an extended stored procedure DLL, a SQL Server system administrator must run the sp_addextendedproc system procedure, specifying the name of the function and the name of the DLL in which that function resides. This registers the function with the SQL Server master database and updates the SQL Server system tables by adding the name of the DLL to the syscomments table and adding the specified function name to the sysobjects table as an extended stored procedure with X as the object type. For example:

sp_addextendedproc 'xp_proclist', 'xp.dll'

This command registers the function xp_proclist, located in the file XP.DLL, as a SQL Server extended stored procedure.

To drop individual extended stored procedures, a system administrator uses the system procedure sp_dropextendedproc.

SQL Server loads an extended stored procedure DLL as soon as a call is made to one of the DLL's functions. The DLL remains loaded until the server is shut down or until the system administrator uses the DBCC command to unload it. For example:

DBCC xp(FREE)

This command unloads XP.DLL, allowing the system administrator to copy in a newer version of this file without shutting down the server.

For more information about the administrative tasks described in this section, see the Microsoft SQL Server Administrator's Companion.