How to see the value of a variable being used in an sql statement during a server trace?

All we need is an easy explanation of the problem, so here it is.

I have a stored procedure which has SQL statements that take variables. For example,

update customer set cust_id = @newcust_id where issuedate < @transferdate and cust_id = @oldcust_id

The values of the variables are assigned earlier in the SP.

How do I show the values of these variables during a server trace?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

For debugging purposes you can trace them explicitly into the Profiler using a custom event, via sp_trace_generateevent:

declare @tracedata varbinary(8000);
set @tracedata = cast(@transferdate as varbinary(8000));
exec sp_trace_generateevent 82, 
        N'@transferdate', 
        @tracedata );
set @tracedata = cast(@oldcust_id as varbinary(8000));
exec sp_trace_generateevent 82, 
        N'@oldcust_id', 
        @tracedata);

You need to modify your Profiler session to monitor for the user User-Configurable Event Class, otherwise you won’t see the generated events. I would also comment out the call to sp_trace_generateevent in production once the debugging is done. The call is not critically expensive (specially if there is no monitoring listening for it), but it should be removed none-the-less.

Updated

The exec call doe snot allow for a CAST to be inlined in the parameter list. I modified the code to show how to cast it before the exec. The value will be in the BinaryData column, in HEX representation (so 'daf' will show up as 0x646166).

Method 2

If you are tracing you will catch all params in your trace.

If you want to see the last input from a given connection use DBCC INPUTBUFFER

Method 3

Change the stored procedure to PRINT them before the UPDATE runs, or use the debugger built in to SSMS rather than Profiler.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply