How to debug SQL Server T-SQL in Visual Studio 201

Question

How does one debug a T-SQL stored procedure in a multi-tier application in Visual Studio 2012?

To be clear, I want to set a breakpoint in a sproc in VS 2012, and hit it when the sproc is called from an ASP.NET WebForms app in the same debugging session.

When following the same steps as for VS 2010, the breakpoints aren't hit inside the sproc.

Debugging T-SQL in a sproc on a SQL Server 2008 R2 Express database works as expected in Visual Studio 2010.

To be sure everything was enabled properly, I went over the instructions for VS 2010 (here), but no such page exists for VS 2012 or .NET 4.5.

It seems the missing step is to enable "Application Debugging", but no such option exists in the Server Explorer > Data Connections context menu in VS 2012.

VS 2010 Application Debugging

VS 2012 No Application Debugging

Answer 1

You need to open "SQL Server Object Explorer. Not "Server Explorer". That is what is different between 2010 & 2012. Then right click on the server and select "Application Debugging".

Answer 2

The following in detail article explains exactly how to enable debugging of a Stored Procedure when a .Net application is executed.

http://www.sqlmag.com/content1/topic/debugging-stored-procedures-142054/catpath/sql-server

Answer 3

This is for VS2012 and SQL2012. Yes things are somewhat different for other versions, but kinda follow similar setup. It is tricky because one has to have various settings just right or it won't work.

• vanilla install of both VS and SQL with all correct options (how to do this is outside scope of this article)

• full admin rights to entire environment and sysadmin rights to sql (fundamentally a development environment; you would not want to do this in a production environment)

• i always debug .net web apps under full local IIS which is a windows feature installed from control panel

  • go .net project properties, web, use local iis web server (i also stick to default port 80)

• while i develop my databases etc using VS sql server database project, i always work under full standalone local SQL; i use the schema compare to refresh that sql with changes made in VS (how to do this is outside scope of this article)

  • do not place your breakpoint on that copy of the sp; that version of the sp is only a blueprint and not the runtime version

• go .net project properties, web, debuggers (at bottom) enabled for .net and sql server

• view sql server object explorer (not server explorer)

  • click add sql server, add your full local sql server using sysadmin credentials (i always use sa for such work)
  • right click on your full local sql server and enable application debugging
  • there go find the sp and right click and view code, then add your breakpoint there; this is runtime version of sp (note that i never make changes to sp there, i go do these in my master copy under the database project)

• rebuild solution, and execute .net web app in debug mode from within ide by clicking green arrow internet explorer

I hope I have not forgotten anything. If I have I'll come revised my post.

All this may sound complicated. It is. But with a little discipline and patience it is priceless. Good luck.

Answer 4

I believe the easiest way to do this would be to add DB as a project to your solution. You can do this by right clicking on the DB in the SQL Server Object Explorer (SSOX). This is a really good way to develop and debug your DB's. After you have added the DB to your project, you can add breakpoints anywhere you want, and debug against LocalDB (or another target if you wish). The largest drawback to this approach is that your existing data will not migrate with you (although you will be able to easily publish any changes back to the SQL DB at will). See the documentation on SSDT on MSDN for further guidance.