August 20, 2007 at 5:20 pm
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/srutzky/3201.asp
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
September 6, 2007 at 1:35 am
Thank you. I never thought of doing this.
_________________
"Look, those sheep have been shorn."
data analyst replies, "On the sides that we can see.."
September 6, 2007 at 2:56 am
Truely amazing!
If only I was able to produce ideas like this...
September 6, 2007 at 9:48 am
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
September 6, 2007 at 11:17 am
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# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
October 28, 2008 at 1:44 pm
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?
October 28, 2008 at 1:59 pm
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# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
October 29, 2008 at 7:54 am
That works great, Thanks!!!!!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply