Capturing Missing Information From SQL Profiler

  • Solomon Rutzky

    SSCoach

    Points: 15924

    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 Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sites — Collations     •     Module Signing     •     SQLCLR

  • katesl

    SSCommitted

    Points: 1743

    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

    SSC-Addicted

    Points: 425

    Truely amazing!

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

  • noeld

    SSC Guru

    Points: 96590

    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

    SSCoach

    Points: 15924

    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 Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sites — Collations     •     Module Signing     •     SQLCLR

  • greenantim

    Old Hand

    Points: 381

    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

    SSCoach

    Points: 15924

    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 Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sites — Collations     •     Module Signing     •     SQLCLR

  • greenantim

    Old Hand

    Points: 381

    That works great, Thanks!!!!!

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply