Debugging stored procedures in VS2010 / SQL Express

Tim Abell · May 22, 2012


<div>Debugging stored procs in a local SQL Express install with Visual Studio 2010.</div><div>
</div><div>Enable TCP/IP - see http://timwise.blogspot.co.uk/2012/05/enabling-tcpip-in-sql-express-2008-r2.html</div><div>
</div><div>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)</div><div>
</div><div>Find the project in your solution which actually executes the stored procedure, right-click > properties > debug > “Enable SQL Server debugging”</div><div>
</div><div>Run your project</div><div>
</div><div>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.</div><div>
</div><div>Set a breakpoint in your stored procedure:</div><div><ul><li>Server explorer, </li><li>the connection you added,</li><li>stored procs,</li><li>right-click the proc name > open</li><li>set a break point in the text of the stored proc</li><ul><li>if it is not a solid red dot then something went wrong</li></ul></ul><div>Run the part of your program / website that will cause the proc to be called.</div></div><div>
</div><div>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).</div><div>
</div><div>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).</div><div>
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

</div><div>refs:

<ul><li>http://stackoverflow.com/questions/4737175/the-breakpoint-will-not-currently-be-hit-error-when-trying-to-debug-a-tsql</li><li>http://social.msdn.microsoft.com/forums/en-US/vstsdb/thread/f5247d99-06f0-4ae3-9371-04c70f750647/</li><li>http://support.microsoft.com/kb/316549/en-us (from comment below)</li></ul></div><div><div class="separator" style="clear: both; text-align: center;"></div>
<div class="separator" style="clear: both; text-align: center;"></div>
<div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: center;">
</div><div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: center;">
</div><div class="separator" style="clear: both; text-align: center;"></div>
</div>

Share: Tweet | LinkedIn
Suggest improvments: page source on github