Walkthrough: Debug an Extended Stored Procedure
This topic applies to:
Pro and Team
Does not apply
Command or commands hidden by default.
Extended stored procedures have been deprecated. Although they continue to be supported for backward compatibility in ASP.NET, that support will be removed in a future release.
Extended stored procedures were introduced to enable actions not possible in T-SQL, such as accessing the file system, reading the registry, and so on. Now that you can do these things by writing SQL CLR procedures, the need to write extended stored procedures is gone. It is strongly recommended that you not write new Extended Stored Procedures, and consider replacing existing ones with equivalent, but safer, SQL CLR stored procedures.
Extended stored procedures are DLLs written in C++, or any other language, rather than SQL. Therefore, debugging an extended stored procedure is much like debugging any DLL in that language.
Extended Stored Procedures are usually written in native C++ and lack the protections afforded by managed code. They should be carefully tested because bugs have the potential to crash SQL Server. To avoid potential loss of data and other problems, do not debug an extended stored procedure on a production server. For more information, see ASP.NET Books Online.
The dialog boxes and menu commands you see might differ from those described in Help depending on your active settings or edition. To change your settings, choose Import and Export Settings on the Tools menu. For more information, see Visual Studio Settings.
To debug an extended stored procedure
Start with a debug build of an extended stored procedure DLL. You also need an application that calls the extended stored procedure you want to debug. If you do not have one ready, you do one of the following actions:
Create a Visual Studio database project with a data connection for the database that contains the extended stored procedure and code to call the extended stored procedure.
Create a SQL script file that calls the extended stored procedure.
Use an application such as ISQL/W, included with SQL Server, or ODBC Test, included with ODBC SDK.
If SQL Server is currently running as a service, stop it by opening the Services control panel, selecting SQL Server, and clicking Stop.
Copy the debug version of the DLL to the directory where sqlservr.exe resides or to any directory in the search path.
Set a Post-Build event to copy the DLL in your C++ project as follows:
Open the <Project> Property Pages dialog box.
In the <Project> Property Pages dialog box, open the Configuration Properties folder.
Under Configuration Properties, open the Build Events folder.
Select Post-Build Event.
In the grid control, next to Command Line, type a copy command, such as the following example:
Copy c:\MyProjects\MyXProc\debug\MyXProc.dll C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn
Register the extended stored procedure.
Specify SQL Server as the calling executable and working directory for the extended stored procedure DLL. Modify the settings in the <Project> Project Properties dialog box, which you can access in the Debugging category of Configuration Properties, as follows:
If SQL Server was installed in the default location, type C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\SQLSERVR.EXE in the Command box.
Set the Working directory entry to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn.
Set the Command arguments entry to -c. The -c option indicates to SQL Server that it is being started from the command line and not as a service, which will cause SQL Server to start faster. SQL Server will start not as a service but as a console application under the control of the Visual Studio debugging environment. This will enable breakpoints to be correctly trapped and handled.
Set breakpoints in the source code of the extended stored procedure.
Use an execution command to start the debugging session. For more information, see Execution Control.
A console window will appear as SQL Server starts. When text scrolling stops, the last message will say:
Launched startup procedure 'sp_sqlregister'
SQL Server starts and starts to process requests.
Execute the extended stored procedure.
The debugger breaks when it reaches a line that contains a breakpoint.
For additional tips about how to write extended stored procedures, consult the SQL Server section of the Microsoft Knowledge Base or the Programming Extended Stored Procedures section of the SQL Server documentation in the MSDN Library.