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

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

Answer 2

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 3

I would use a combination of SQL Profiler and print statements inside of your SQL statement. Not sure of a way to step thru line by line but using profiler in combination with print and select statements (if using temp tables) to view their contents as the proc runs will quickly shed light on what's happening.

Answer 4

Update: I am not sure if there is a way to attach to a running stored proc. You can use profiler to get a real time trace of the statements getting executed (SP:StmtStarting). Also check out Apex SQL Debug which seems to have more capabilities and is available as an Add-in to Management Studio.

If you have Visual Studio, it is easy to debug:

Debugging Stored Procedures in Visual Studio 2005

More answers here: What’s your favored method for debugging MS SQL stored procedures?

Answer 5

If you can't step through the code, here are two ways:

#1 concatenate a string, and insert into a log file.

Declare a variable like:

DECLARE @Loginfo varchar(7500)

append debug info into it as you progress through the code:

SET @LogInfo=ISNULL(@LogInfo)+'#01> @x='+COALESCE(CONVERT(varchar(10),@x),'NULL')
..
SET @LogInfo=ISNULL(@LogInfo)+'#02>'
..
SET @LogInfo=ISNULL(@LogInfo)+'#03> top loop'

at all exit points (after any rollbacks) add:

INSERT INTO YourLogTable VALUES (... ,@LogInfo )

depending on the transaction usage and you error in particular, you may be able to just insert many times with no fear of rollback, so you will need change this to your situation.

#2 write to a text file on the sq server

this may not be an option because it uses the very insecure xp_cmdshell stored procedure. However, if you can use that and if transactions from the calling app are causing a problem try creating this stored procedure:

CREATE PROC log_message
     @Message         varchar(255)
    ,@FileName        varchar(100)
    ,@OverWrite       char(1) = 'N'
AS

/*
Log messages to server side text files from stored procedures/triggers/sql scripts

  Input parameters:
      Message   - message to put in the log file 
      FileName  - path and name of the file to log the message into
      OverWrite - 'Y'=overwrite entire file with current message
                  'N'=append current message onto end of file

  Return code:
      0 - everything was fine
      1 - there was an error


        NOTE: the command to log the message can not be longer than 255 characters,
              as a result the message and file name should be less than 245 chars combined


  Example: EXEC log_message 'Duplicates found','C:\logfile.txt', 'N'
      append the "Duplicates found" message onto the server's "C:\logfile.txt" file

*/

BEGIN
    SET NOCOUNT ON

    DECLARE @ExecuteString    VARCHAR(255)   --command string can only be 255 chars long
    DECLARE @ReturnValue           int

    --build command string
    SET @ExecuteString = RTRIM('echo ' + COALESCE(LTRIM(@Message),'-')
        + CASE WHEN (@OverWrite = 'Y') THEN ' > ' ELSE ' >> ' END + RTRIM(@FileName))

    --run command string
    EXEC @ReturnValue=master..xp_cmdshell @ExecuteString
    --IF @ReturnValue!=0
    --    PRINT 'command failed, return value='+CONVERT(varchar(40),@ReturnValue)

    RETURN @ReturnValue

    SET NOCOUNT OFF
END

sprinkle calls to this procedure these through your code write what you need into a file on the server

Answer 6

A link