Tutorial :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?


Solution: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. :)


Solution:2

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


Solution:3

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?


Solution:4

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


Solution:5

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.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »