Geek Answers Handbook

Debugging a stored procedure in SQL Server 2005 from Visual Studio?

I see a lot of frustrated questions here and elsewhere without a clear answer. I am trying to get stored procs to debug, but to no avail.

Client: VS2005 or VS2008, does not work in any. When I select "Step in Stored Procedure" from the sproc context menu, I get "User Canceled" in the debug window and at the end of it.

I did the following to help myself (all to no avail):

  • Right-click on the connection and select "Debug Applications" and "Allow SQL / CRL Debugging"
  • Make sure all fields are in the same domain.
  • Make sure that I log in to SQL Server with the same domain account with which I log in to my workstation.
  • Make sure my domain account is both an administrator in the SQL block and an administrator in SQL Server.
  • I ran msvsmon.exe in the SQL field and successfully bound to the process from my client window (in VS).

What am I missing?

+4
source share
2 answers

One of the important issues is that this will not work if the SQL Server process is running as the local system, which is the default installation. It should work as an account in the administrator group on the local computer.

What I did was set up a local user in my field named "sqlserver" and put it in the local administrators group. Then go to the service control panel, stop the sql server service. Change it and set it to run as the specified user account, and not as local. Specify the local sqlserver account and password that you created. Then restart the service.

+2
source

What version of your SQL Server? SP debugging is available only in Pro and Team versions (not Express and Standard).

Also make sure that you use Windows authentication to log in and your account is sysadmin on the SQL server.

Since you can connect to the process in the SQL box, I believe that DCOM is working correctly and there is no blocking firewall between them.

+1
source

More articles:


All Articles