HOW TO: Debug an Extended Stored Procedure

This article describes how to properly debug an extended stored procedure.

Debug an Extended Stored Procedure

1. Make a debug build of the extended stored procedure DLL by using Microsoft Visual C. Make sure to generate the .pdb file.
2. After you correctly build the DLL, copy it to the C:\Mssql\Binn folder. The DLL that SQL Server is loading and the .pdb file must be synchronized to properly handle breakpoints.
3. Register the extended stored procedure by using the sp_addextendedproc stored procedure. For more information about the sp_addextendedproc stored procedure, see the "sp_addextendedproc" topic in SQL Server Books Online.
4. In the Project Settings dialog box, on the Debug tab, add the following:

        Executable for debug session: C:\MSSQL\BINN\SQLSERVR.EXE
        Program arguments: -c
   
   						

   This starts SQL Server not as a service but in the Visual C Debug environment. By doing so, you can properly trap and handle your breakpoints.
5. Stop SQL Server if it is currently running by opening the Services tool in Control Panel, and then stopping the MSSQLSERVER service.
6. Set your breakpoint, and then begin the debugging session.
7. After you properly start SQL Server and the cursor is blinking in the SQL Server command prompt window, minimize the window.
8. Use the ISQL/w query tool or Query Analyzer to run the extended stored procedure that you are going to debug.

When the breakpoint is encountered, the MSDEV environment stops, and you can begin the normal debugging process.

For more information about the sp_addextendedproc stored procedure, see the "sp_addextendedproc" topic in SQL Server Books Online.