March 27, 2013 at 7:09 pm
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.
April 9, 2013 at 11:08 pm
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
April 9, 2013 at 11:15 pm
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