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