extracting waitresource from a blocked process report

  • Hi,

    I've got a "blocked process report" trace and want to get all the waitresoure values for a blocked process:

    select top 5 CHARINDEX('waitresource', convert(varchar, TextData)), substring(convert(varchar, TextData), CHARINDEX('waitresource', convert(varchar, TextData)), 20)

    FROM [dbo].[Blocking_25032013] (nolock)

    I am getting the below results, meaning that the query can't find the 'waitresource' substring in the TextData column:

    (No column name)(No column name)

    0<blocked-process-re

    0<blocked-process-re

    0<blocked-process-re

    0<blocked-process-re

    0<blocked-process-re

    All I want is to get "OBJECT: 5:142623551:3 " string out of:0

    <blocked-process-report>

    <blocked-process>

    <process id="processae27948" taskpriority="0" logused="0" waitresource="OBJECT: 5:142623551:3 " waittime="5042" ownerId="634429087" transactionname="SELECT" lasttranstarted="2013-03-27T12:36:57.410" XDES="0x167c6ac90" lockMode="IS" schedulerid="4" kpid="3652" status="suspended" spid="250" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2013-03-27T12:36:56.133" lastbatchcompleted="2013-03-27T12:36:56.133" clientapp=".Net SqlClient Data Provider" hostname="RBISYDIISP003" hostpid="16360" loginname="kenticouser" isolationlevel="read committed (2)" xactid="634429087" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">

    <executionStack>

    Any ideas?

    Thanks.

  • convert(varchar, TextData)) does not have the length of varchar soecified.

    By default it's equal to convert(varchar(30), TextData)).

    And actually you do not need to convert Textdata at all.

    Use PATINDEX instead of CHARINDEX.

    _____________
    Code for TallyGenerator

  • select top 5 PATINDEX ('%waitresource=OBJECT%', TextData)

    , substring(TextData, PATINDEX ('%waitresource="OBJECT%', TextData) + LEN('waitresource="OBJECT')+1, 20)

    _____________
    Code for TallyGenerator

Viewing 3 posts - 1 through 2 (of 2 total)

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