How can I attach to and debug a running SQL Server stored procedure?

Question

I am investigating an odd error from a SQL Server 2005 stored procedure which I cannot reproduce by calling it directly from Management Studio.

Therefore I'd like to be able to:

• set a breakpoint in the stored procedure
• wait for the procedure to be called externally and the breakpoint hit
• see the values of the passed-in parameters
• step through the stored procedure

Is this possible, and if so how?

Answer 1

You could try the "server explorer" from visual studio but the sqlserver needs to be configured to allow debugging. Here is some info: http://www.4guysfromrolla.com/articles/051607-1.aspx. But I think that you first should try Profiler like Eppz say. :)

Answer 2

Use SQL Profiler to view what is happening when the procedure gets called and what params are passed in.