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"

1 comment:

Anonymous said...

Hi Tim,
Just came across this and have been fighting the same problem. Finally got it working, sort of, all of the links and info where i tried to use the stored proc in a database project didn't work. But using this guide:

worked. I had to set the break point in the stored proc in the server window..
But as you say broken code...