SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Capturing Missing Information From SQL Profiler


Capturing Missing Information From SQL Profiler

Author
Message
Solomon Rutzky
Solomon Rutzky
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2664 Visits: 3002
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/srutzky/3201.asp

SQL# - http://www.SQLsharp.com/
katesl
katesl
Old Hand
Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)

Group: General Forum Members
Points: 305 Visits: 473
Thank you. I never thought of doing this.

_________________
"Look, those sheep have been shorn."
data analyst replies, "On the sides that we can see.."
Anders Bendix Nielsen-392963
Anders Bendix Nielsen-392963
Old Hand
Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)

Group: General Forum Members
Points: 329 Visits: 134

Truely amazing!

If only I was able to produce ideas like this...


noeld
noeld
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16510 Visits: 2048
Excellent idea. There is one little problem though and is that the trigger is coded for "single-row" inserts. I don't really know if SQL Profiler ever is going to submit a batch of more than one row but in any case triggers should be coded *always* to handle multiple rows.

Thanks for sharing!


* Noel
Solomon Rutzky
Solomon Rutzky
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2664 Visits: 3002

Hello Noel and thanks. While I do agree that triggers 99% of the time should be coded to handle batches, I felt it was a good assumption here that since Profiler is event-based that it would have no facility to submit anything but one event at a time. Lemme think about updating this to handle batches since that would require a cursor due to the DBCC call and hence take additional resources on top of what SQL Profiler is already taking. In fact, I think I might have just thought of a way to do it without a cursor but it will be difficult to test. I will let you know.



SQL# - http://www.SQLsharp.com/
greenantim
greenantim
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 Visits: 245
I tried to use this on SQL2005 but every time I add the trigger, the trace ends without any indication of what caused it to fail. Has anyone got this to work on SQL2005 or does it only work on SQL2000?
Solomon Rutzky
Solomon Rutzky
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2664 Visits: 3002
greenantim (10/28/2008)
I tried to use this on SQL2005 but every time I add the trigger, the trace ends without any indication of what caused it to fail. Has anyone got this to work on SQL2005 or does it only work on SQL2000?


Yes, sorry. I keep meaning to update the article with this info, but here is how to get it working in SQL 2005:


1) In SQL Server 2005, the "EventInfo" field returned by the DBCC INPUTBUFFER command has increased in size from 255 to 4000. So, the CREATE TABLE #DBCCInfo line should look as follows:

CREATE TABLE #DBCCInfo (EventType NVARCHAR(30), Parameters INT, EventInfo NVARCHAR(4000))


2) For some reason, adding the trigger to the table while the trace is running causes the problem. I am not exactly sure why but that is definitely the problem. So, the trick to fix it is to follow these steps:

1) Start the trace (this creates the table)
2) Pause the trace
3) Run the script to create the trigger
4) UN-Pause the trace

SQL# - http://www.SQLsharp.com/
greenantim
greenantim
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 Visits: 245
That works great, Thanks!!!!!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search