shoayb,
I am glad you found this article helpful. In fact, you could easily edit that stored procedure and use it to trace deadlocks, do performence tuning and lots of other things.
>>exec sp_trace_setevent @traceident, XX, >>22, @on
>>however nothing is returned.
I am not too sure what you mean by "Nothing is returned"
1. If the ObjectID column is added to the trace output file but the data column is empty, then that is normal, because some columns are not applicable to certain events;
2. If the ObjectID column does not exist in the output file, that means this column was not added to the trace. There are 2 ways to resolve this:
a. You could add this column on the fly:
1. Use SELECT * FROM :: fn_trace_getinfo(default) to find out your trace ID;
2. Use sp_trace_setstatus @traceId, 0. This will pause the trace but not kill it;
3. You can then issue exec sp_trace_setevent @traceid, XX, 22, @on to add this column;
4. After you are done, issue SP_trace_setstatus @traceId, 1 to restart the trace;
b. This is the permanent solution
1. Use SELECT * FROM :: fn_trace_getinfo(default) to find out your trace ID;
2. Use sp_trace_setstatus @traceId, 0. This will pause the trace but not kill it;
3. Use sp_trace_setstatus @traceId, 2. This will kill the trace; NOTE: YOU MUST USE STEP 2 AND 3 TO KILL AND DELETE A TRACE. SIMPLY USING sp_trace_setstatus @traceId, 2 WILL NOT WORK;
4. Edit the your stored procedure and add the data column there and you are good to go.
Hope this helps.
Haidong