Remote debugging of CLR Stored Procedures, two common errors and the reason behind them.

Remote debugging of CLR Stored Procedures, two common errors and the reason behind them.

Rate This
  • Comments 2

The Scenario:

You are attempting to remotely debug a CLR stored procedure from Visual Studio 2008.

 

The Setup:

Both debugger (VS 2008) and debugger target (Sql Server) is in the same domain but on different machines, which is a given, otherwise it would be hard to call it remote debugging.

 

An example on how to set this up (as you may have noted, I am a big fan of exemplifying):

This is what I use in this demo: 

Visual Studio Professional on Windows Vista 64bit / Sql Server 2005 on Windows 2003 32 bit / Both machines in the same Domain

 

1. Start VS 2008 and create a new Database/Sql Server project, call it MyDbProject.

2. Add a database reference to your database.

3. Right click the project and select Add->Stored Procedure, call it MyClrProcedure. This procedure will take two numbers and add them, so the full class should look like this:

 

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

 

public partial class StoredProcedures

{

    [Microsoft.SqlServer.Server.SqlProcedure]

    public static SqlInt32 MyClrProcedure(SqlInt32 firstValue, SqlInt32 secondValue)

    {

        using (SqlConnection connection = new SqlConnection("context connection=true"))

        {

            SqlParameter paramOne = new SqlParameter("@valueOne", SqlDbType.Int);

            paramOne.Value = firstValue;

            SqlParameter paramTwo = new SqlParameter("@valueTwo", SqlDbType.Int);

            paramTwo.Value = secondValue;

 

            SqlCommand command = new SqlCommand("SELECT @valueOne + @valueTwo", connection);

            command.Parameters.Add(paramOne);

            command.Parameters.Add(paramTwo);

 

            connection.Open();

            SqlInt32 returnValue = (int)command.ExecuteScalar();

            connection.Close();

            return returnValue;

        }

    }

};

 

4. Build and deploy it to the server.

5. Then verify in Sql Server Management Studio that the stored procedure works by running the following, should return 70:

 

DECLARE @valueOne int

DECLARE @valueTwo int

DECLARE @ret int

SET @valueOne = 15

SET @valueTwo = 55

 

EXECUTE @ret = Repros.dbo.MyClrProcedure @valueOne, @valueTwo

SELECT @ret

 

6. Copy the SQL above into the file in the project called Test.sql that is in the folder Test Scripts.

7. Right click the Test.sql file and select "Set as Default Debug Script"

8. Set breakpoints on the following rows in the MyClrProcedure.cs file

 

paramOne.Value = firstValue;

paramTwo.Value = secondValue;

 

9. Finally run the project in debug mode by just pressing F5. It should hit on the break points.

 

Now, the steps above should work. And it will in most cases, but if there weren’t problems occasionally, well, I wouldn’t be here writing about it.

There are two common errors that I have seen when doing remote CLR debugging.

 

The first one is this one:

--------------------------------------------------------------------------------------------

  Unable to debug .NET code. Could not connect to computer '<computername>'.

  Microsoft Visual Studio Remote Debugging Monitor (MSVSMON.EXE) is not running under your user account and MSVSMON

  could not be automatically started. MSVSMON must be manually started, or the Visual Studio remote debugging components

  must be installed on the remote computer. Please see help for assistance.

--------------------------------------------------------------------------------------------

 

This error commonly shows when mismatching versions of msvsmon.exe (Remote Debugging Monitor) is used.

They have to be of the same version in order to do remote CLR debugging. The locations:

 

2008 -> C:\Program Files\Microsoft Visual Studio 9\Common7\IDE\Remote Debugger\x86\msvsmon.exe, version 9.0.2

2005 -> C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\Remote Debugger\x86\msvsmon.exe, version 8.0.5

 

How to install the remote debugging monitor is found here:

"Visual Studio Debugger -> How to: Set Up Remote Debugging"

http://msdn.microsoft.com/en-us/library/bt727f1t.aspx 

 

The second one is this one:

  --------------------------------------------------------------------------------------------

  The debugger was unable to terminate one or more processes:

  [procNo] sqlservr.exe: The debugger is still attaching to the process.

  The debugger may be unstable now. It is recommended that you save all files and exit.

  --------------------------------------------------------------------------------------------

 

This error commonly shows when the debugger role is not a member of the sysadmin role in Sql Server.

This can easily be seen by removing your account from the sysadmin group on the server.

Do that, run the debugger (F5 in Visual Studio), it appears to hang, stop the debugger and you will get the error above.

Add yourself back to the sysadmin group, debugging goes fine.

 

This is described here.

"Visual Studio Debugger -> Remote Debugging Permissions"

http://msdn.microsoft.com/en-us/library/z3bxds0s.aspx

  "Your Windows user account on both the application computer and the SQL Server has to be a member of the SQL Server sysadmin group.

  The account that is used for connecting to SQL Server 2005 in Server explorer also has to be a member of the SQL Server sysadmin group"

 

So, to conclude. When remotely debugging a CLR stored procedure from Visual Studio, make sure that:

A - You have the correct version of MSVSMON.EXE installed on server machine.

B - You have the debugger account in the sysadmin role in SQL Server.

 

References:

“How to implement remote debugging in Visual Studio 2005”

http://support.microsoft.com/default.aspx?scid=kb;EN-US;910448

“SQL Server 2005 Books Online (September 2007) Debugging CLR Database Objects”

http://msdn.microsoft.com/en-us/library/ms131096.aspx

“Visual Studio Debugger - How to: Debug a SQL CLR Stored Procedure”

http://msdn.microsoft.com/en-us/library/ms165051.aspx

“Visual Studio Debugger - Remote Debugging Components”

http://msdn.microsoft.com/en-us/library/ey7ec813.aspx

“Visual Studio Debugger - Remote Debugging Setup”

http://msdn.microsoft.com/en-us/library/y7f5zaaa.aspx

Leave a Comment
  • Please add 2 and 2 and type the answer here:
  • Post