How to: Debug a SQL Server CLR Integration Stored Procedure

This topic applies to:

Edition	Visual Basic	C#	C++	Web Developer
Express
Visual Studio Professional, Visual Studio Premium, and Visual Studio Ultimate

You can debug an existing SQL Server common language runtime (SQL Server CLR) integration stored procedure by using direct database debugging, the same way you would debug a Transact-SQL procedure. However, that will not work if you need to create or modify a SQL Server CLR integration procedure, because you need to compile and deploy it. These steps that do not exist for the Transact-SQL procedure. In this case, you need to create a SQL Server project in Visual Studio.

The following task creates a new SQL Server CLR integration stored procedure in the AdventureWorks database, one of the databases installed with SQL Server 2005, and then shows how to debug it. You create a stored procedure that adds a new currency to the Sales.Currency table.

This example focuses on debugging within a SQL Server project. Once you have created the stored procedure, you can debug it using direct database debugging. For more information, see How to: Step into an Object Using Server Explorer.

If, when you try to debug a SQL Server CLR integration object, the message "Canceled by user" appears, you must manually configure both the computer on which you are running Visual Studio as well as the computer that is running SQL Server. For more information, see How to: Configure Your Computers to Enable Transact-SQL and SQL Server CLR Integration Debugging.

To debug a SQL Server CLR integration stored procedure

1. Enable remote debugging. For more information, see How to: Set Up Remote Debugging.

2. In a new SQL Server project, establish a connection to the AdventureWorks sample database. For more information, see How to: Connecting to a Database.

3. Create a new stored procedure using the code from the first example section that follows, and name it InsertCurrency.cs. For more information, see How to: Develop with the SQL Server Project Type.

4. Add a script that tests the stored procedure by calling it. In Solution Explorer, right-click the TestScripts directory, click Add Test Script, and insert the code from the second Example section that follows. Save the file with the name InsertCurrency.sql. Right-click the file name, and click Set as Default Debug Script.

5. Set breakpoints in InsertCurrency.cs, and then on the Debug menu, click Start to compile, deploy, and unit-test the project. When the instruction pointer, designated by a yellow arrow, appears on a breakpoint, you are debugging your stored procedure.

6. Try different debugging features.

   1. Open the Locals window, and on the Debug menu, click Step Into to step one line in the stored procedure. Notice that the value of the variable @mynvarchar has changed in the Locals window and its value is now displayed in red, indicating it has changed. For more information, see Using the Locals Window.

      Note

      The server may not reflect changes to values of variables in the debugger windows. For more information, see SQL Debugging Limitations.

   2. Open the Watch window. In the Text Editor, drag the InsertCurrencyCommand variable to any location in the Watch window.

      The variable is now added to the list of watched variables. For more information, see How to: Use Debugger Variable Windows.

      Note   You can edit the values of variables in the Watch window also.

   3. In the Text Editor, right-click the InsertCurrencyCommand.ExecuteNonQuery line, and on the shortcut menu, click Insert Breakpoint.

   4. On the Debug menu, click Continue and the debugger will run the code up to the new breakpoint.

7. Click Continue again to finish debugging the stored procedure.

   A message appears in the Output window stating that the stored procedure was successfully deployed, and displaying the result of executing the commands in the InsertCurrency.sql file.

Example

Replace the stored procedure template with this code.

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlServer;
using System.Data.SqlTypes;

public partial class StoredProcedures
{
    [SqlProcedure]
    public static void InsertCurrency(SqlString currencyCode, 
                                               SqlString name)
    {
        using(SqlConnection conn = new SqlConnection("context connection=true")) {
        SqlCommand cmd = new SqlCommand([your SQL statement], conn);
        }
        InsertCurrencyCommand.CommandText = "insert Sales.Currency"
                     + " (CurrencyCode, Name, ModifiedDate) values('" 
                     + currencyCode.ToString() + "', '" 
                     + name.ToString() + "', '" 
                     + DateTime.Now.ToString() + "')";
        InsertCurrencyCommand.ExecuteNonQuery();
    }
}

This is the test script that is used to execute the stored procedure.

- Delete any row that might exist with a key value
- that matches the one we are going to insert
DELETE Sales.Currency
WHERE CurrencyCode = 'eee'
EXEC InsertCurrency 'eee', 'MyCurr4'
SELECT * FROM Sales.Currency WHERE CurrencyCode = 'eee'

See Also

Tasks

How to: Create and Run a SQL Server Stored Procedure by using Common Language Run-time Integration

How to: Configure Your Computers to Enable Transact-SQL and SQL Server CLR Integration Debugging