22 May 2012

Debugging stored procedures in VS2010 / SQL Express

Debugging stored procs in a local SQL Express install with Visual Studio 2010.

In Visual Studio, Server Explorer, Connect to your server as localhost instead of .\SQLEXPRESS so that you connect through TCP/IP and not shared memory (which doesn't allow debugging for some reason)

Find the project in your solution which actually executes the stored procedure, right-click > properties > debug > "Enable SQL Server debugging"

Run your project

You may need to hit "stop" and re-attached (debug > attach to process) explicitly selecting "T-SQL code" in the "attach to" box (and optionally managed as well). It *should* automatically select T-SQL but it seems to be hit and miss.

Set a breakpoint in your stored procedure:
  • Server explorer, 
  • the connection you added,
  • stored procs,
  • right-click the proc name > open
  • set a break point in the text of the stored proc
    • if it is not a solid red dot then something went wrong
Run the part of your program / website that will cause the proc to be called.

If the breakpoint isn't hit check the type's in the attach to process list include T-SQL (doesn't seem to always work).

I only got the damn thing to work once. If it doesn't work you get no reason at all which is just crap. The main problem I have is that the attach just quietly drops T-SQL even if you explicitly request it. Shoddy coding from Microsoft in my opinion.

The next best thing is to right-click the stored proc, click "step into" and input the values manually. (Which also requires a tcp/ip connection to the local sql express and is fussy).

Another message encountered a couple of days later without changing anything at all when attaching to the already running web dev process: "User Could Not Execute Stored Procedure sp_enable_sql_debug"

Enabling TCP/IP in SQL Express 2008 R2

Programs > .. R2 > SQL Server Configuration Manager
Network config > Protocols > tcp/ip > enable & properties

Clear the dynamic port under IPAll
Set the TCP Port to 1433 (which is the standard sql server port).

You can then connect to "localhost" (with no instance specified) in management studio.