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

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 开发者_StackOverflow中文版this demoed by Microsoft staff, but can’t recall the 101 “magic” settings that are needed to get it to work.)

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

For those of you who are using VS 2012 and are confused as to why you cannot enable SQL/CLR Debugging from the Server Explorer window, you actually need to connect via the "SQL Server Object Explorer" window.

Once you have the connection in that window, the Allow SQL/CLR Debugging and Application Debugging setting appear on the right-click context menu of your server. This probably applies to 2013 as well but I cannot confirm.

Enable the SQL Server debugging in the project properties.

In Solution Explorer, right-click the project (not the solution) and open the Property pages. Click Configuration Properties in the tree and then click to select the SQL Server Debugging check box on the Debugging page to enable stored procedure debugging.

reference: http://support.microsoft.com/kb/316549