How to debug stored procedure on a remote sql server from Visual Studio 2005

Question

Hi,

Can anyone suggest how to debug stored procedure from a remote sql server using visual studio.net 2005?

I am able to debug my stored procedure from local server, but I'm unable to do so if the sql server is remote. I will often get an error that

"Unable to start T-SQL Debugging.Could not attach to SQL Server process on [Remote SQL Server name]".Click help for more information."

What are the steps I should follow to activate remote debugging of store procedure in visual studio 2005?

Please suggest any good articles.

Thanks in advance SA

Answer 1

To be honest the remote debugging stored procedures from VS2005 was less than a stellar experience (it is much improved in VS2008).

Here is a walkthrough: Debugging Stored Procedures in Visual Studio 2005.

Also: Debugging SQL Server 2005 Stored Procedures in Visual Studio

Also see SO question: Debugging stored procedure in SQL Server 2005 from Visual Studio?

Answer 2

I simply sprinkle a bunch of select and print statement (with a marker on top that says SELECT 'annon was here') that shows the value of the variables etch in the stored procedure code and run it from the SSMS. I find this to be much easier than using the debugger. The Visual studio T-SQL debugger leaves a lot to be desired.

And I believe I am not the only one who does this :-).

Similarly for debugging triggers, I tend to isolate most of the functional code in stored procs and call them instead from the trigger.