Debugging stored procedures in VS2010 / SQL Express

May 22, 2012

1 minute read

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

Enable TCP/IP - see http://timwise.blogspot.co.uk/2012/05/enabling-tcpip-in-sql-express-2008-r2.html

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

References

Screenshots


Share as Tweet || Share on LinkedIn
Post source code on GitHub - suggestions & PRs welcome!
Subscribe for updates on software development, contracting, side projects, blog posts and who knows what else. Read the archives for an idea of content.

Mailing list powered by the excellent buttondown.email.