How do I remotely debug a stored procedure within the same domain?

Question

What are the steps needed to successfully be able to remotely debug a stored procedure (SQL Server 2005) from within VS 2005?

Both client and server machines are on the same domain. I have never done this so step by step instructions would greatly be appreciated.

Answer 1

Great Question! If I'm not mistaken I don't think debugging is possible inside of SQL Management Studio anymore (as it was back in the SQL Server 2000, Enterprise Studio days).

Instructions to Remote Debug MS SQL Stored Procedures within Visual Studio 2005

1. Launch Visual Studio (If you're running from Vista, Run As Administrator)
2. Within Visual Studio 2005 click View->Server Explorer, which you'll notice brings up a panel with a Data Connections element.
3. Right click on Data Connections and select Add Connection
4. Ensure the Data Source is set to SqlClient.
5. Fill out the Server connection information, filling in the database name where the stored procedure that you wish to debug lives.
6. Once a successful connection is made you'll notice the a tree for the database is populated that gives you the list of Tables, Views, Stored Procedures, Functions, etc.
7. Expand Stored Procedures, finding the one you wish to debug and right click on it and select Step Into Stored Procedure.
8. If the stored procedure has parameters a dialog will come up and you can specify what those parameters are.
9. At this point, depending on your firewall settings and what not, you maybe be prompted to make modifications to your firewall to allow for the necessary ports to be opened up. However, Visual Studio seems to handle this for you.
10. Once completed, Visual Studio should place you at the beginning of the stored procedure so you can starting the act of debugging!

Happy Debugging!