Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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

enter image description here

VS 2012 No Application Debugging

enter image description here

like image 884
nekno Avatar asked Aug 18 '12 06:08

nekno


People also ask

How do I enable debugging in SQL Server 2012?

To start debugging your T-SQL code, you can either: Click on the Debug button on the Query toolbar. Click on the Start Debugging menu item on the Debug menu as shown below. Press ALT+F5.

How do I run SQL in debug mode?

You can start the debugger by either clicking the Debug button on the Query toolbar or by clicking Start Debugging on the Debug menu. The Query Editor window stays in debug mode until either the last statement in the Query Editor window finishes or you stop debug mode.

How do I debug a SQL Server query?

To debug a function, open the procedure calling that function and insert a breakpoint for the function you want to debug. Then, start debugging. Step through the code using the F11 key or Step Into, or press CTRL+F5 to move directly to the breakpoint. Press F11 or click Step Into to get inside the stored function.


2 Answers

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".

like image 198
Hunter Avatar answered Nov 16 '22 02:11

Hunter


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.

like image 36
ES44AC SD70MAC Avatar answered Nov 16 '22 02:11

ES44AC SD70MAC