How can I step into a SQL Server stored proc from my C# code?

Question

I am debugging some C# code that uses Ado.net to call a stored proc (TSQL) in SQL Server. How can I step into the stored proc?

(I think I have seen this demoed by Microsoft staff, but can’t recall the 101 “magic” settings that are needed to get it to work.)

Answer 1

I'll build on Davide's answer and Ian's comment to it because that was the exactly the process I have gone through.

Assumption: answer is based on VS2008 & VS2010.

To step through a T-SQL stored procedure on SQL Server while debugging a .NET app you need to do the following:

1. Follow the MS Support instructions "How to debug stored procedures in Visual Studio .NET (Option 2)"

   • Ensure that you have enabled SQL Server debugging in the project's properties (Step 4 in the linked instructions).
   • Ensure you have set a breakpoint in the stored procedure itself (Steps 6-7)

2. Enable the Allow SQL/CLR Debugging on the Data Connection on which the stored procedure is called:

   • (without this second step you'll be getting "The breakpoint will not currently be hit. Unable to bind SQL breakponit at this time. Object containing the breakpoint not loaded." as reported by Ian)
   • Server Explorer > Data Connections
   • Right-click on the connection that you are using in the code
   • Tick the Allow SQL/CLR Debugging option in the menu

---

Added from comments: (This seem to be key to getting it to work)

It works with a programmatically created connection, too. You just have to use the exact same connection string that is displayed when you right-click the server instance, click on 'Properties' and scroll down to 'Connection string' (Verbindungszeichenfolge in German).