Getting SQLHandle from XML into sys.dm_exec_sql_text

  • Hi all,

    Ive ran some Event Notifications collecting some blocking data, im now trying to extract the sqlhandle from the xml that its created and put it into sys.dm_exec_sql_text.

    what ever i try is coming up short though.

    To get the SQLHandle you can do the following:

    SELECT FullLog.value('(/EVENT_INSTANCE/TextData/blocked-process-report/blocking-process[1]/process/executionStack/frame/@sqlhandle)[1]',

    'nvarchar(max)') AS 'sqlhandle'

    FROM [dbo].[EventNotification]

    WHERE [EventType] = 'BLOCKED_PROCESS_REPORT'

    If I cut and paste that into

    select *

    from sys.dm_exec_sql_text (0x030010004b525b7ba8648e00d0a100000100000000000000)

    i get results! happy days.

    If i try the following

    WITH cte ( sqlhandle )

    AS ( SELECT FullLog.value('(/EVENT_INSTANCE/TextData/blocked-process-report/blocking-process[1]/process/executionStack/frame/@sqlhandle)[1]',

    'nvarchar(max)') AS 'sqlhandle'

    FROM [DBA].[dbo].[EventNotification2]

    WHERE [EventType] = 'BLOCKED_PROCESS_REPORT'

    )

    SELECT sql_text.*

    FROM cte

    CROSS APPLY sys.dm_exec_sql_text(CONVERT(VARBINARY(MAX), sqlhandle))

    AS sql_text

    I get the error

    Msg 569, Level 16, State 4, Line 1

    The handle that was passed to dm_exec_sql_text was invalid.

    If i take out the CONVERT from the cross apply i get the error

    Msg 257, Level 16, State 3, Line 2

    Implicit conversion from data type nvarchar(max) to varbinary is not allowed. Use the CONVERT function to run this query.

    Any ideas what im missing? ive tried adding the convert into the cte and its still not getting the same error sayings its not valid..

    EventNotifcation Table design:

    CREATE TABLE EventNotification

    (

    LoggingID INT IDENTITY(1, 1)

    PRIMARY KEY CLUSTERED ,

    SQLInstance VARCHAR(100) ,

    DatabaseName VARCHAR(100) ,

    EventTime DATETIME ,

    EventType VARCHAR(100) ,

    LoginName VARCHAR(100) ,

    DatabaseUser VARCHAR(100) ,

    ClientHostName VARCHAR(100) ,

    NTUserName VARCHAR(100) ,

    NTDomainName VARCHAR(100) ,

    SchemaName VARCHAR(100) ,

    ObjectName VARCHAR(100) ,

    ObjectType VARCHAR(100) ,

    Success INT ,

    FullSQL VARCHAR(MAX) ,

    FullLog XML ,

    Archived BIT NOT NULL

    )

    Full XML example from FullLog :

    <EVENT_INSTANCE>

    <EventType>BLOCKED_PROCESS_REPORT</EventType>

    <PostTime>2014-03-06T11:51:15.583</PostTime>

    <SPID>5</SPID>

    <TextData>

    <blocked-process-report>

    <blocked-process>

    <process id="process37e03d498" taskpriority="0" logused="0" waitresource="PAGE: 15:1:1737 " waittime="895387" ownerId="856691" transactionname="SELECT" lasttranstarted="2014-03-06T11:36:20.163" XDES="0x36aa12d00" lockMode="S" schedulerid="6" kpid="9500" status="suspended" spid="55" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2014-03-06T11:36:19.960" lastbatchcompleted="2014-03-06T11:36:12.230" lastattention="1900-01-01T00:00:00.230" clientapp="Microsoft SQL Server Management Studio - Query" hostname="BLQGV4J" hostpid="9844" loginname="staggerlee" isolationlevel="read committed (2)" xactid="856691" currentdb="15" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">

    <executionStack>

    <frame line="2" stmtstart="86" sqlhandle="0x020000001328ea139dbaf2a4d9d57b31247285837357cded0000000000000000000000000000000000000000" />

    </executionStack>

    <inputbuf>

    -- run this query in a different window!

    SELECT FirstName ,

    LastName ,

    SUM(soh.TotalDue) AS TotalDue ,

    MAX(OrderDate) AS LastOrder

    FROM Sales.SalesOrderHeader AS soh

    INNER JOIN Sales.Customer AS c ON soh.CustomerID = c.CustomerID

    INNER JOIN Person.Person AS p ON c.PersonID = p.BusinessEntityID

    WHERE soh.OrderDate > = '2011/ 01/ 01'

    GROUP BY c.CustomerID ,

    FirstName ,

    LastName </inputbuf>

    </process>

    </blocked-process>

    <blocking-process>

    <process status="sleeping" spid="52" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2014-03-06T11:35:54.087" lastbatchcompleted="2014-03-06T11:35:58.820" lastattention="1900-01-01T00:00:00.820" clientapp="Microsoft SQL Server Management Studio - Query" hostname="BLQGV4J" hostpid="9844" loginname="staggerlee" isolationlevel="read committed (2)" xactid="856039" currentdb="15" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">

    <executionStack />

    <inputbuf>

    DECLARE @SalesOrderHeaderID INT

    BEGIN TRANSACTION

    INSERT INTO Sales.SalesOrderHeader

    ( RevisionNumber ,

    OrderDate ,

    DueDate ,

    ShipDate ,

    Status ,

    OnlineOrderFlag ,

    PurchaseOrderNumber ,

    AccountNumber ,

    CustomerID ,

    SalesPersonID ,

    TerritoryID ,

    BillToAddressID ,

    ShipToAddressID ,

    ShipMethodID ,

    CreditCardID ,

    CreditCardApprovalCode ,

    CurrencyRateID ,

    Comment ,

    rowguid ,

    ModifiedDate

    )

    VALUES ( 5 ,

    '2011/ 06/ 20' ,

    '2011/ 06/ 25' ,

    '2011/ 06/ 30' ,

    5 ,

    0 ,

    NULL ,

    '10-4030-018749' ,

    18749 ,

    NULL ,

    6 ,

    28374 ,

    28374 ,

    1 ,

    8925 ,

    '929849Vi46003' ,

    NULL ,

    NULL ,

    NEWID() ,

    GETD </inputbuf>

    </process>

    </blocking-process>

    </blocked-process-report>

    </TextData>

    <DatabaseID>15</DatabaseID>

    <TransactionID>856691</TransactionID>

    <Duration>895381000</Duration>

    <StartTime>2014-03-06T11:36:20.203</StartTime>

    <EndTime>2014-03-06T11:51:15.583</EndTime>

    <ObjectID>0</ObjectID>

    <IndexID>0</IndexID>

    <ServerName>BLQGV4J</ServerName>

    <Mode>3</Mode>

    <LoginSid>AQ==</LoginSid>

    <EventSequence>502</EventSequence>

    <IsSystem>1</IsSystem>

    <SessionLoginName />

    </EVENT_INSTANCE>

  • While I don't have a workaround for your solution, the second error appears to be the most telling. It looks like SQL Server doesn't like the NVARCHAR to VARBINARY conversion.

    In the example below I try to create a NVARCHAR(MAX) variable and convert it to VARBINARY(MAX). When I print it as an NVARCHAR, it looks fine. When trying to convert it to VARBINARY, however, it gives us something entirely different which is probably what is getting passed into your example.

    DECLARE @testvar NVARCHAR(MAX)

    SET @testvar = '0x01000400EB08F51F40C012F00200000000000000'

    PRINT @testvar

    PRINT CONVERT(VARBINARY(MAX), @testvar)

    Results:

    0x01000400EB08F51F40C012F00200000000000000

    0x300078003000310030003000300034003000300045004200300038004600350031004600340030004300300031003200460030003000320030003000300030003000300030003000300030003000300030003000

    I'm sure there are others that can provide a more detailed explanation though.

  • You can try a convert to varbinary(64) (sqlhandle as expected by sys.dm_exec_query_text is looking for the varbinary(64)).

    In looking at your deadlock graph, you have a sqlhandle that is too large. The sqlhandle should have ended at the cded.

    Since you are trying to get the text of the queries involved in the deadlock, you could just trap that from the inputbuf node.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • hey,

    thanks the replies..

    Yes its definitely messing with the string when its converts into varbinary, i've tried converting to varbinary max and 64, always the same results of its not a valid..

    SQLRNNR its not a deadlock graph, its a blocking info from capturing BLOCKING_PROCRESS_THRESHOLD with service broker and event notifications, so no alternatives that i can see at present to this..

    cheers

    s

  • Yup correct, I misspoke on the deadlock graph. It is obviously just a blocked process report. Look at your XML, you have the <inputbuf> node there with the query that is participating in the blocking. You can just extract the query from the XML that you have already trapped.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Hi all,

    sorry for the delay in replying, I have it working now,

    it was down to the convert.

    using

    CONVERT ( varbinary(64), sql_handle, 1)

    works.

    http://msdn.microsoft.com/en-us/library/ms187928.aspx

    Thanks for the help

    S

  • 😛 Great Help, just what I was looking for!

Viewing 7 posts - 1 through 6 (of 6 total)

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