Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Capturing Missing Information From SQL Profiler Expand / Collapse
Author
Message
Posted Monday, August 20, 2007 5:20 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, September 27, 2014 8:10 PM
Points: 368, Visits: 1,948
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/srutzky/3201.asp




SQL# - http://www.SQLsharp.com/
Post #392283
Posted Thursday, September 6, 2007 1:35 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, May 16, 2014 7:20 PM
Points: 63, Visits: 470
Thank you.  I never thought of doing this.


_________________
"Look, those sheep have been shorn."
data analyst replies, "On the sides that we can see.."
Post #396755
Posted Thursday, September 6, 2007 2:56 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, March 3, 2010 5:43 AM
Points: 305, Visits: 134

Truely amazing!

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

Post #396782
Posted Thursday, September 6, 2007 9:48 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 5:47 AM
Points: 6,259, Visits: 2,029
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
Post #396977
Posted Thursday, September 6, 2007 11:17 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, September 27, 2014 8:10 PM
Points: 368, Visits: 1,948

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/
Post #397022
Posted Tuesday, October 28, 2008 1:44 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 7, 2014 11:51 AM
Points: 147, Visits: 220
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?
Post #593220
Posted Tuesday, October 28, 2008 1:59 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, September 27, 2014 8:10 PM
Points: 368, Visits: 1,948
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/
Post #593238
Posted Wednesday, October 29, 2008 7:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 7, 2014 11:51 AM
Points: 147, Visits: 220
That works great, Thanks!!!!!
Post #593619
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse