I’ve been using VS2005 to debug stored procedures – this is easier than it used to be with SQL Server 2000, but it still might be worth sharing these brief instructions. Also, once you get it working it is, as Elliot might say…. OK, wait; I don’t think I’d better go there.

1. First of all, although I understand that it is possible to debug a stored procedure on a remote server (e.g. DEVSQLSRV1\SQL2005CS) I have never gotten that to work. So you’ll want to have the database installed on your machine – I generally stop the database instance when I’m not running locally to free up a little memory.
2. Restore a copy of your database of interest to your local SQL Server 2005 instance.
3. Set up your config files (web.config, MyTestLibrary.dll.config) to point to the database on your machine.
4. Bring up the VS2005 Server Explorer; View ==> Server Explorer will do this.
5. Right-click on Data Connections and choose Add Connection…
6. You’ll probably get a dialog box the first time you do this asking you to pick a Data source type and Data provider. I chose Microsoft SQL Server and .NET Framework Data Provider for SQL Server
7. In the Add Connection dialog, choose your server name from the drop down – I chose RKASKAN-WXP\RRKSQL2005. Select the appropriate database name as well, depending on what you did in step 2 above – in my case this is PecosGoldLocal. You can click the handy “Test Connection” button if you are a nervous or even merely prudent person. Click OK when happy.
8. Attach to the process of interest, say nunit-gui.exe. You may or may not default to debugging T-SQL – if the “Attach to Process” dialog doesn’t include T-SQL in the “Attach to” label, click “Select…” to fix this.
9. From here you can either step into stored procedures from C# code, or you can set break points just as you would in C#. Open your database from Server Explorer, open the Stored Procedures node, double-click the procedure of interest and set a break point. You can hover over T-SQL variables to see the values – it is all pretty cool.

When you are finished and you go back your normal life, you might want to remove the SQL Server data connection you set up in steps 5-7, particularly if you plan to stop your local server process. If you do not, you’ll probably hang for several seconds the next time you try to attach the debugger as VS2005 tries to connect to your stopped database.

There is one thing I used to do when debugging with Query Analyzer that I haven’t figured out how to do in this environment. There was a setting for that debugger that allowed you to rollback changes at the end of the procedure – that meant you could step through the thing repeatedly if (like me) you are a little slow in understanding what is going on. But I wouldn’t want to return to the Query Analyzer mode of debugging just because of that.

3 August 2006 Update: recently I've had a little trouble getting this to work, and I have found some discussion of this on the net at http://blogs.msdn.com/sqlclr/. I think I was having problems because I was attempting to attach to aspnet_wp.exe as opposed to the nunit executable. Apparently you have to attach the debugger to your favorite process before that process opens a database connection. I'm sure you could get this to work with the ASP.Net worker process, but it was just as easy for me to debug an nUnit test and I had much more direct control over when the database connection is opened, so that's what I did.