InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 18:42:36 GMT20http://www.sqlservercentral.com/Forums/Topic392283-403-1.aspxThat works great, Thanks!!!!!Wed, 29 Oct 2008 07:54:28 GMTgreenantimhttp://www.sqlservercentral.com/Forums/Topic392283-403-1.aspx[quote][b]greenantim (10/28/2008)[/b][hr]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?[/quote]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 trace3) Run the script to create the trigger4) UN-Pause the traceTue, 28 Oct 2008 13:59:54 GMTSolomon Rutzkyhttp://www.sqlservercentral.com/Forums/Topic392283-403-1.aspxI 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?Tue, 28 Oct 2008 13:44:07 GMTgreenantimhttp://www.sqlservercentral.com/Forums/Topic392283-403-1.aspx<P>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.</P><P> </P><P> </P>Thu, 06 Sep 2007 11:17:00 GMTSolomon Rutzkyhttp://www.sqlservercentral.com/Forums/Topic392283-403-1.aspxExcellent 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! Thu, 06 Sep 2007 09:48:00 GMTnoeldhttp://www.sqlservercentral.com/Forums/Topic392283-403-1.aspx<P>Truely amazing!</P><P>If only I was able to produce ideas like this...</P>Thu, 06 Sep 2007 02:56:00 GMTAnders Bendix Nielsen-392963http://www.sqlservercentral.com/Forums/Topic392283-403-1.aspxThank you. I never thought of doing this.Thu, 06 Sep 2007 01:35:00 GMTkateslhttp://www.sqlservercentral.com/Forums/Topic392283-403-1.aspxComments posted here are about the content posted at <A HREF="http://www.sqlservercentral.com/columnists/srutzky/3201.asp">http://www.sqlservercentral.com/columnists/srutzky/3201.asp</A>Mon, 20 Aug 2007 17:20:00 GMTSolomon Rutzky