August 16, 2013 at 1:38 pm
Hi Folks- thanks in advance for any help you can provide.
I'm capturing a blocked processes report to a file one disk and I'm trying to query against it and parse the xml into readable information. Most of that is fairly well documented and I'm having success except for one small snag.
I'm trying to take the sqlhandle from the report and return the sql statement associated with it. When I do so, I'm getting the error:
Implicit conversion from data type varchar(max) to varbinary is not allowed. Use the CONVERT function to run this query.
here's what I'm doing:
1.) capture a blocked processes trace to a file called :\trace_blockedprocesses_2013-08-16_0400.trc and create a couple of entries by blocking (blocked processes threshold is set)
2.) drop that into a table, just to make things a little simple for now (will eventually just use a CTE)
SELECT CAST(textdata AS XML) as fullblockedprocessereportXML
, CAST(textdata AS XML).value( '(/blocked-process-report/blocked-process/process/executionStack/frame/@sqlhandle)[1]','varchar(max)') as blockedprocess
, CAST(textdata AS XML).value('(/blocked-process-report/blocking-process/process/executionStack/frame/@sqlhandle)[1]','varchar(max)') AS BlockingProcess1
, CAST(textdata AS XML).value('(/blocked-process-report/blocking-process/process/executionStack/frame/@sqlhandle)[2]','varchar(max)') AS BlockingProcess2
, CAST(textdata AS XML).value('(/blocked-process-report/blocking-process/process/executionStack/frame/@sqlhandle)[3]','varchar(max)') AS BlockingProcess3
, CAST(textdata AS XML).value('(/blocked-process-report/blocking-process/process/executionStack/frame/@sqlhandle)[4]','varchar(max)') AS BlockingProcess4
, CAST(textdata AS XML).value('(/blocked-process-report/blocking-process/process/executionStack/frame/@sqlhandle)[5]','varchar(max)') AS BlockingProcess5
, CAST(textdata AS XML).value('(/blocked-process-report/blocking-process/process/executionStack/frame/@sqlhandle)[6]','varchar(max)') AS BlockingProcess6
, CAST(textdata AS XML).value('(/blocked-process-report/blocking-process/process/executionStack/frame/@sqlhandle)[7]','varchar(max)') AS BlockingProcess7
,@@servername as servername,Duration/1000000.0 as durationsecs,DatabaseName
into #tempwc1
from fn_trace_gettable(N'd:\trace_blockedprocesses_2013-08-16_0400.trc', default)
3.) attempt to return results like this and error is produced:
select * from #tempwc1 wc
cross apply sys.dm_exec_sql_text(wc.blockedprocess) AS st
What I have tried already:
When I do select * from #tempwc1 and take the value returned for blockedprocess and manually plug it into sys.dm_exec_sql_text like:
select text from sys.dm_exec_sql_text(0x03000d008702963122150300aca100000100000000000000)
I do then get the correct blocking query.
When I change the data type, or convert the value to varbinary like this:
.... CAST(textdata AS XML).value( '(/blocked-process-report/blocked-process/process/executionStack/frame/@sqlhandle)[1]','varbinary)') as blockedprocess .....
in that case, the acutal value of the sqlhandle changes so I dont get any result...
So to summarize my question, how can return the sql statements involved in a blocked process along with the rest of the relevant information in the blocked processes report without having to manaully plug in the sqlhandle.
Thanks!
August 19, 2013 at 7:01 am
So, I posted on a Friday afternoon and go no responses... maybe this reply on a Monday morning will help? Still can't get it, would appreciate a tip.
January 16, 2014 at 4:14 am
try this
CAST(textdata AS XML).value('xs:hexBinary(substring((/blocked-process-report[1]/blocked-process[1]/process[1]/executionStack[1]/frame[1]/@sqlhandle)[1],3))',
'varbinary(max)')
etc
that would help
January 16, 2014 at 4:55 am
I think you misunderstand the question... but I figured it out some time ago. this is what I'm doing:
USE [DBA]
GO
/****** Object: Table [dbo].[blockingreport2013] Script Date: 01/16/2014 06:56:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[blockingreport2013](
[waitresourcetype] [varchar](500) NULL,
[waitresourcedatabase] [nvarchar](128) NULL,
[waitresourceid] [varchar](507) NULL,
[WAITTABLENAME] [nvarchar](500) NULL,
[WAITINDEXNAME] [varchar](500) NULL,
[starttime] [varchar](50) NULL,
[waittime] [varchar](1000) NULL,
[waitresource] [varchar](500) NULL,
[objectid] [varchar](500) NULL,
[databaseid] [varchar](10) NULL,
[indexid] [varchar](10) NULL,
[blockingdatabasename] [varchar](500) NULL,
[blockingobjectname] [varchar](4000) NULL,
[blockeddatabasename] [varchar](500) NULL,
[blockedobjectname] [varchar](4000) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [DBA]
GO
/****** Object: StoredProcedure [dbo].[Record_blockedProcesses] Script Date: 01/16/2014 06:49:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc
[dbo].[Record_blockedProcesses]
@pathandfilename varchar (500)=null
as
begin
--declare @pathandfilename varchar (500)
--set @pathandfilename='d:\trace_blockedprocesses_2013-12-10_0400.trc'
if (@pathandfilename is null)
begin
select @pathandfilename=path from sys.traces where path like '%blocked%'
end
select
WaitTime = cast( textdata as xml ).value( '/blocked-process-report[1]/blocked-process[1]/process[1]/@waittime','varchar(20)' ),
WaitResource = cast( textdata as xml ).value( '/blocked-process-report[1]/blocked-process[1]/process[1]/@waitresource','varchar(50)' ),
BlockingQuery = cast( textdata as xml ).value( '(/blocked-process-report/blocking-process/process/inputbuf)[1]','varchar(200)'),
BlockingSpid = cast( textdata as xml ).value( '/blocked-process-report[1]/blocking-process[1]/process[1]/@spid','varchar(20)' ),
BlockingLastBatchStarted = cast( textdata as xml ).value( '/blocked-process-report[1]/blocking-process[1]/process[1]/@lastbatchstarted','varchar(50)' ),
BlockingSQLHandle = cast( textdata as xml ).value( '/blocked-process-report[1]/blocking-process[1]/process[1]/executionStack[1]/frame[1]/@sqlhandle','varchar(50)' ),
BlockingStmtstart = cast( textdata as xml ).value( '/blocked-process-report[1]/blocking-process[1]/process[1]/executionStack[1]/frame[1]/@stmtstart','varchar(50)' ),
BlockingStmtend = cast( textdata as xml ).value( '/blocked-process-report[1]/blocking-process[1]/process[1]/executionStack[1]/frame[1]/@stmtend','varchar(50)' ),
BlockedQuery = cast( textdata as xml ).value( '(/blocked-process-report/blocked-process/process/inputbuf)[1]','varchar(200)'),
BlockedSpid = cast( textdata as xml ).value( '/blocked-process-report[1]/blocked-process[1]/process[1]/@spid','varchar(20)' ),
BlockedLastBatchStarted = cast( textdata as xml ).value( '/blocked-process-report[1]/blocked-process[1]/process[1]/@lastbatchstarted','varchar(50)' ),
BlockedSQLHandle = cast( textdata as xml ).value( '/blocked-process-report[1]/blocked-process[1]/process[1]/executionStack[1]/frame[1]/@sqlhandle','varchar(50)' ),
BlockedStmtstart = cast( textdata as xml ).value( '/blocked-process-report[1]/blocked-process[1]/process[1]/executionStack[1]/frame[1]/@stmtstart','varchar(50)' ),
BlockedStmtend = cast( textdata as xml ).value( '/blocked-process-report[1]/blocked-process[1]/process[1]/executionStack[1]/frame[1]/@stmtend','varchar(50)' ),
OBJECTID,IndexID,databaseid
into #temp1
from fn_trace_gettable (@pathandfilename, default)
where eventclass = 137
and TextData not like '%Database Id = 32767%'
order by BlockedLastBatchStarted
/**
select
db_name(cast (Right(LEFT([blockingquery],23),2) as int)) as blockingdatabasename
,replace (replace( (Right(blockingquery,(CHARINDEX('Object Id = ',BlockingQuery)-10))),'=',''),']','')as blockingobjectid
,db_name(cast (Right(LEFT([blockedquery],23),2) as int)) as blockeddatabasename
,replace (replace( (Right(blockedquery,(CHARINDEX('Object Id = ',BlockedQuery)-10))),'=',''),']','')as blockedobjectid,
*
from #temp1**/
declare @blockingdatabasename varchar(100), @blockingobjectid varchar(100),@sql nvarchar(4000)
declare @blockeddatabasename varchar(100), @blockedobjectid varchar(100)
declare @blockedquery varchar(4000), @blockingquery varchar(4000),@waitresource varchar(500)
declare @results table(starttime varchar(50),
waittime varchar(1000),
waitresource varchar(500),
objectid varchar(500)
,databaseid varchar(10)
,indexid varchar(10)
,blockingdatabasename varchar(500)
,blockingobjectname varchar(4000)
,blockeddatabasename varchar(500)
,blockedobjectname varchar(4000)
)
declare @waittime varchar(1000)
declare @BlockingLastBatchStarted varchar(50)
declare @objectid varchar(500), @databaseid varchar(10), @indexid varchar(10)
declare dbcursor cursor for
select
BlockingLastBatchStarted,
waittime,
waitresource,
CASE
WHEN blockingquery like '%Proc %' THEN
db_name(cast (Right(LEFT([blockingquery],23),2) as int))
ELSE 'NA'
end
,CASE
WHEN blockingquery like '%Proc %' THEN
replace (replace( (Right(blockingquery,(CHARINDEX('Object Id = ',BlockingQuery)-10))),'=',''),']','')
ELSE '-9999'
END
,CASE
WHEN blockedquery like '%Proc %' THEN
db_name(cast (Right(LEFT([blockedquery],23),2) as int))
ELSE 'NA'
END
,CASE
WHEN blockedquery like '%Proc %' THEN
replace (replace( (Right(blockedquery,(CHARINDEX('Object Id = ',BlockedQuery)-10))),'=',''),']','')
ELSE '-9999'
END
,blockingquery,blockedquery,objectid,indexid,databaseid
from #temp1
open dbcursor
fetch next from dbcursor into @BlockingLastBatchStarted,@waittime,@waitresource,@blockingdatabasename,@blockingobjectid,@blockeddatabasename,@blockedobjectid,@blockingquery,@blockedquery,@objectid,@indexid,@databaseid
while @@FETCH_STATUS =0
begin
--select @blockingdatabasename,@blockingobjectid,@blockeddatabasename,@blockedobjectid
if (@blockingdatabasename='NA' and @blockeddatabasename!='NA')
begin
set @sql= 'select '''+@BlockingLastBatchStarted+''','+@waittime+','''+@waitresource+''','''+@objectid+''','''+@databaseid+''','''+@indexid+''', ''NA'','''+ REPLACE(@blockingquery, '''', '"')+''' ,'''+@blockeddatabasename+''',b.name from '+@blockeddatabasename+'.dbo.sysobjects b where b.id='+@blockedobjectid
--select @sql
insert @results
exec (@sql)
end
if (@blockingdatabasename!='NA' and @blockeddatabasename!='NA')
begin
set @sql= 'select '''+@BlockingLastBatchStarted+''','+@waittime+','''+@waitresource+''','''+@objectid+''','''+@databaseid+''','''+@indexid+''', '''+@blockingdatabasename+''',a.name ,'''+@blockeddatabasename+''',b.name from '+@blockingdatabasename+'.dbo.sysobjects a, '+@blockeddatabasename+'.dbo.sysobjects b where a.id='+@blockingobjectid+'and b.id='+@blockedobjectid
--select @sql
insert @results
exec (@sql)
end
if (@blockingdatabasename!='NA' and @blockeddatabasename='NA')
begin
set @sql= 'select '''+@BlockingLastBatchStarted+''','+@waittime+','''+@waitresource+''','''+@objectid+''','''+@databaseid+''','''+@indexid+''', '''+@blockingdatabasename+''',a.name ,''NA'',''"'+REPLACE(@blockedquery, '''', '"')+'"'' from '+@blockingdatabasename+'.dbo.sysobjects a where a.id='+@blockingobjectid
--select @sql
insert @results
exec (@sql)
end
if (@blockingdatabasename='NA' and @blockeddatabasename='NA')
begin
set @sql= 'select '''+@BlockingLastBatchStarted+''','+@waittime+','''+@waitresource+''','''+@objectid+''','''+@databaseid+''','''+@indexid+''', ''NA'',''"'+REPLACE(@blockedquery, '''', '"')+'"'',''NA'','''+ REPLACE(@blockingquery, '''', '"')+''''
--select @sql
insert @results
exec (@sql)
end
fetch next from dbcursor into @BlockingLastBatchStarted,@waittime,@waitresource,@blockingdatabasename,@blockingobjectid,@blockeddatabasename,@blockedobjectid,@blockingquery,@blockedquery,@objectid,@indexid,@databaseid
end
select * into #temp3 from @results
order by starttime
close dbcursor
deallocate dbcursor
drop table #temp1
--end
select LEFT(waitresource,(charindex(':',waitresource))-1 ) as waitresourcetype,
DB_NAME(
CASE WHEN waitresource like 'KEY%' then
SUBSTRING( waitresource,
CHARINDEX(':', waitresource) + 1,
LEN(waitresource) - CHARINDEX(':', waitresource) - CHARINDEX(':', REVERSE(waitresource))
)
ELSE
LEFT(
SUBSTRING( waitresource,
CHARINDEX(':', waitresource) + 1,
LEN(waitresource) - CHARINDEX(':', waitresource) - CHARINDEX(':', REVERSE(waitresource))
),
(charindex(':',SUBSTRING( waitresource,
CHARINDEX(':', waitresource) + 1,
LEN(waitresource) - CHARINDEX(':', waitresource) - CHARINDEX(':', REVERSE(waitresource))
)
))-1)
END) as waitresourcedatabase,
CASE WHEN waitresource like 'PAGE%' then waitresource
WHEN waitresource like 'KEY%' then
LEFT(
RIGHT(waitresource,charindex(':',reverse(waitresource))-1)
,CHARINDEX (' ', RIGHT(waitresource,charindex(':',reverse(waitresource))-1)))
WHEN waitresource like 'OBJECT%' then
objectid
ELSE 'OTHER -'+waitresource
END
AS waitresourceid
,CASE when waitresource like 'OBJECT%' then
OBJECT_NAME(objectid,databaseid)
When waitresource like 'KEY%' then 'notdone'
When waitresource like 'PAGE' then waitresource
end
as WAITTABLENAME
,'page_or_object_lock_no_index_captured' as WAITINDEXNAME,*
into #temp4
from #temp3
drop table #temp3
--select * from #temp4
-------------------------------------------
--begin block to update waitresource columns
----
declare @sql2 nvarchar(400)
declare updatecursor cursor for
select waitresourcedatabase, waitresourceid,waittablename from #temp4
where waittablename='notdone'
declare @waittable varchar(200)
declare @waitindex varchar(500)
declare @waitresourcedatabase varchar(200)
declare @waitresourceid varchar(200)
DECLARE @ParmDefinition nvarchar(500)
open updatecursor
fetch next from updatecursor into @waitresourcedatabase, @waitresourceid,@waittable
while @@FETCH_STATUS =0
begin
--select @waitresourceid
SET @ParmDefinition = N' @waitresourceidIN varchar(200), @waittableout nvarchar(25) OUTPUT'
set @sql2='SELECT @waittableout=obj.name FROM '+@waitresourcedatabase+'.sys.partitions par JOIN '+@waitresourcedatabase+'.sys.objects obj ON par.OBJECT_ID = obj.OBJECT_ID
JOIN '+@waitresourcedatabase+'.sys.indexes ind ON par.OBJECT_ID = ind.OBJECT_ID AND par.index_id = ind.index_id
WHERE par.hobt_id =@waitresourceidIN'
exec
sp_executesql @sql2,@ParmDefinition,@waitresourceidIN=@waitresourceid,@waittableout=@waittable output;
--select @waittable
update #temp4
set waittablename=@waittable
where waitresourcedatabase=@waitresourcedatabase
and waitresourceid=@waitresourceid and waittablename='notdone'
SET @ParmDefinition = N' @waitresourceidIN varchar(200), @waitindexout nvarchar(500) OUTPUT'
set @sql2='SELECT @waitindexout=ind.name FROM '+@waitresourcedatabase+'.sys.partitions par JOIN '+@waitresourcedatabase+'.sys.objects obj ON par.OBJECT_ID = obj.OBJECT_ID
JOIN '+@waitresourcedatabase+'.sys.indexes ind ON par.OBJECT_ID = ind.OBJECT_ID AND par.index_id = ind.index_id
WHERE par.hobt_id =@waitresourceidIN'
exec
sp_executesql @sql2,@ParmDefinition,@waitresourceidIN=@waitresourceid,@waitindexout=@waitindex output;
if @waitindex is null
set @waitindex='notfound'
update #temp4
set waitindexname=@waitindex
where waitresourcedatabase=@waitresourcedatabase
and waitresourceid=@waitresourceid and waitindexname='page_or_object_lock_no_index_captured'
fetch next from updatecursor into @waitresourcedatabase, @waitresourceid,@waittable
end
close updatecursor
deallocate updatecursor
--select * from #temp4
----START BLOCK TO UPDATE PAGE LOCK ROWS
--declare variables
declare @pagenumber varchar(100)
declare @command varchar(500)
declare @pageobjectid varchar(100)
declare @pageindexid varchar(100)
CREATE TABLE #PageData
(
ParentObject VARCHAR(1000)NULL,
Object VARCHAR(4000)NULL,
Field VARCHAR(1000)NULL,
ObjectValue VARCHAR(4000)NULL,
)
--define cursor
declare pagecursor cursor for
select distinct waitresourceid, waitresourcedatabase from #temp4 where waitresourcetype='PAGE'
open pagecursor
fetch next from pagecursor into @pagenumber, @waitresourcedatabase
while @@FETCH_STATUS =0
begin
set @command=REPLACE(@pagenumber,'PAGE: ' ,'DBCC PAGE(')+') WITH TABLERESULTS'
set @command=REPLACE(@command,':',',')
insert into #PageData (ParentObject, Object, Field, ObjectValue)
exec (@command)
--select * from #PageData
select top 1 @pageobjectid=ObjectValue from #PageData where Field ='Metadata: ObjectId'
select top 1 @pageindexid= ObjectValue from #PageData where Field ='Metadata: IndexId'
--select @pageobjectid
update #temp4 set objectid=@pageobjectid, indexid=@pageindexid where waitresource=@pagenumber
delete from #PageData
SET @ParmDefinition = N' @waitresourceidIN varchar(200), @waittableout nvarchar(25) OUTPUT'
set @sql2='SELECT top 1 @waittableout=obj.name FROM '+@waitresourcedatabase+'.sys.partitions par JOIN '+@waitresourcedatabase+'.sys.objects obj ON par.OBJECT_ID = obj.OBJECT_ID
JOIN '+@waitresourcedatabase+'.sys.indexes ind ON par.OBJECT_ID = ind.OBJECT_ID AND par.index_id = ind.index_id
WHERE obj.object_id =@waitresourceidIN'
exec
sp_executesql @sql2,@ParmDefinition,@waitresourceidIN=@pageobjectid,@waittableout=@waittable output;
--select @waittable
update #temp4
set waittablename=@waittable
where waitresourcedatabase=@waitresourcedatabase
and waitresourceid=@pagenumber and (waittablename='notdone' OR waittablename is null)
SET @ParmDefinition = N' @waitresourceidIN varchar(200), @objin varchar(100), @waitindexout nvarchar(500) OUTPUT'
set @sql2='SELECT @waitindexout=ind.name FROM '+@waitresourcedatabase+'.sys.partitions par JOIN '+@waitresourcedatabase+'.sys.objects obj ON par.OBJECT_ID = obj.OBJECT_ID
JOIN '+@waitresourcedatabase+'.sys.indexes ind ON par.OBJECT_ID = ind.OBJECT_ID AND par.index_id = ind.index_id
WHERE ind.index_id=@waitresourceidIN and obj.object_id=@objin'
-- select @sql2
exec
sp_executesql @sql2,@ParmDefinition,@waitresourceidIN=@pageindexid,@objin=@pageobjectid,@waitindexout=@waitindex output;
if @waitindex is null
set @waitindex='notfound'
update #temp4
set waitindexname=@waitindex
where waitresourcedatabase=@waitresourcedatabase
and waitresourceid=@pagenumber and (waitindexname='page_or_object_lock_no_index_captured' or waitindexname is null)
fetch next from pagecursor into @pagenumber, @waitresourcedatabase
end
close pagecursor
deallocate pagecursor
drop table #pagedata
--select * from #temp4
insert
dba.dbo.blockingreport2013
select * from #temp4 where starttime >(select max(starttime) from dba.dbo.blockingreport2013)
drop table #temp4
delete from dba.dbo.blockingreport2013
where starttime <getdate()-60
--drop table #temp1
--drop table #temp3
end
GO
January 16, 2014 at 10:30 am
i dont care what you're doing now. i've just provided you with the exact answer to you question. Read it carefully
January 16, 2014 at 11:35 am
Actually you didn't answer my question at all. My question is how to return the SQL statement.
Jerk.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply