Debugging Stored Procedures from Code

You can debug stored procedures interactively (as I've been discussing) or from your applications. There aren't many mysteries here once you're able to debug stored procedures on non-local SQL Server instances. Here are a few guidelines:

• Again, make sure that you can open a connection to the instance of SQL Server hosting the stored procedure. This might be a local or remote instance. I typically use the Server Explorer and navigate to the stored procedure and check to see if the required breakpoints are set.
  
  
• Make sure you're executing Visual Studio with the same credentials as used to log on the target SQL Server instance. See the previous discussion in this chapter for details.
  
  
• Make sure the application settings enable SQL Server debugging. This setting is available from the "Settings" dialogright-click on the project in the Solution Explorer and choose "Properties". Click on the "Debug" tab and check "Enable SQL Server Debugging", as shown in Figure 5.23.
  
  

  Figure 5.23. Enable SQL Server debugging in your project settings.

  
  
  
• Make sure that the Visual Studio IDE is set to "debug" and not "release" mode. This is easy to set or determine when you use the "General Development" or "C# Developer" IDE settingsjust check the Solution Configuration toolbar drop down. However, it's not so easy when using the "Visual Basic Developer" IDE settings, as I discussed early in the chapter. If you don't see the Solution Configurations menu on your toolbar, you'll need to enable it. T-SQL debugging is disabled (for obvious reasons) in the "release" configuration.
  
  

Once you're ready, start your application as usualof course, it should include code to call the stored procedure to be debugged. When the logic reaches the line of T-SQL code containing the breakpoint, the Visual Studio IDE opens the stored procedure window and positions to the line about to be executed.

Note that the stored procedure to be debugged might not be the stored procedure called by your applicationit might be a stored procedure called by some other stored procedure or trigger that your application invokes directly or indirectly. For example, if the stored procedure to be debugged is called by (or is) a trigger, SQL Server might not execute the target stored procedure until a row is added, deleted, or updated (firing the trigger).

No, in most cases, Visual Studio won't complain about not being able to access a remote instance of SQL Serverit simply does not show the stored procedure window.

A word of caution: When debugging stored procedures, a considerable amount of TDS traffic is generated along with an equally heavy load on the target SQL Server. Do not enable T-SQL debugging if you don't plan to use it.