Export (0) Print
Expand All

Debugging Extended Stored Procedures

Extended stored procedures are actually DLLs that are written in C++ (or any other language) and are not SQL-based. Therefore, debugging an extended stored procedure is much like debugging any application in C++ (or whatever language the extended stored procedure is written in).

Caution   To avoid potential loss of data and other problems, do not debug an extended stored procedure on a computer that is running as a production server.

To debug an extended stored procedure

  1. Use Visual Studio to create a debug build of an extended stored procedure DLL.
  2. You need an application that calls the extended stored procedure you want to debug. If you do not have one ready, you can:
    • 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.

      -or-

    • Use an application such as ISQL/W (included with SQL Server) or ODBC Test (included with ODBC SDK).
  3. If SQL Server is currently running as a service, stop it by opening the Services control panel, selecting MSSQLSERVER, and clicking Stop.
  4. Copy the debug version of the DLL to the directory where sqlservr.exe resides or to any directory in the search path.

    Tip   You can enter a command to copy the DLL in the <Project> Property Pages dialog box (Configuration Properties folder, Build Events subfolder Post-Build Event category, Command Line.) For example:

    Copy c:\MyProjects\MyXProc\debug\MyXProc.dll c:\mssql\binn.
    
  5. Register the extended stored procedure. For more information, see Registering an Extended Stored Procedure.
  6. 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 (Configuration Properties folder, Debugging category) as follows:
    • If SQL Server was installed in the default location, type C:\MSSQL\BINN\SQLSERVR.EXE in the Command box.
    • Set the Working directory entry to C:\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 up 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 allow breakpoints to be properly trapped and handled.
  7. Set breakpoints in the source code of the extended stored procedure.
  8. Use an execution command to begin 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 begins to process requests.

  9. Execute the extended stored procedure.
  10. The debugger breaks when it reaches a line containing a breakpoint.

For further tips on writing 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.

See Also

Debugging SQL

Show:
© 2014 Microsoft