You must register an extended stored procedure with SQLВ Server before it can be used. You can register the extended stored procedure using a Visual Studio Database Project.
To register an extended stored procedure
Create an extended stored procedure project as described in .
Copy the extended stored procedure DLL to your SQLВ Server's Binn directory (Microsoft SQLВ Server\...\Binn) or a directory included in the system search path for that server. Because SQLВ Server uses the Win32 API LoadLibrary to locate extended stored procedure DLLs, your new DLL needs to reside in the system path. On the , you can use a Post-Build step (see ) to automate copying the DLL to the correct directory.
You can add the new Extended Stored Procedure in one of the following ways:
Using a SQLВ Script. Create a Database Project (in the New Projects dialog, look in Other Projects). In Solution Explorer, right-click Create Scripts, and select Add SQL Script. In the Add New Item dialog box, select SQL Script. In the text editor enter a script such as:
В Copy Code
USE master EXEC sp_addextendedproc 'xp_proc', 'ExtSP01.DLL'
Save the script file, right-click the change script text, and then click Run.
In SQLВ Server Enterprise Manager, open the Database node, and then open the master database. Right-click Extended Stored Procedures, and then select New Extended Stored Procedure. A dialog box prompts you for the name of your extended stored procedure and the name of the DLL. It is best to include just the file name of the DLL, not the full path, such as MyXp.dll instead of c:\MyVCProjects\MyXp\debug\MyXp.dll.
The ReadMe.txt file in the extended stored procedure project gives you further instructions about how to do this and how to drop and release the extended stored procedure. For more information, see .