Argh! I spoke too soon. Even with the VARCHAR(max), I get "Msg 9403, Level 16, State 1, Line 1 XML parsing: line 0, character 0, unrecognized input signature"
I doubt you have a spelling mistake, specifically in the part where the namespace is declared. Could you post the code you are running and I will try to spot if there is something wrong with it.
I ran the code from the blog: ;WITH XMLNAMESPACES (
DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition', 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
) SELECT
name, x.value('CommandType[1]', 'VARCHAR(50)') AS CommandType, x.value('CommandText[1]','VARCHAR(50)') AS CommandText
FROM (
select name, CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML from ReportServer.dbo.Catalog
) a CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet/Query') r(x)
Are you sure that you are connected to an instance of SQL Server 2005? The code provided in this post uses XQuery that is available only on SQL Server 2005 and later versions.
Yes, we're definitely on SQL Server 2005. The code works on our dev box, but not in production. In production, it returns a partial result set (about 200 records, but I would expect well over 700 records) and the aforementioned error.
Hi Jacob. I did apply the filter suggested. I am now trying to use the query below, which tweaks yours slightly to add the data source and data set (obtained from http://tejasnshah.wordpress.com/2009/02/21/sql-server-how-to-stored-procedure-names-used-by-reports-sql-reporting-services/). However, I am still getting the same error (Msg 9403, Level 16, State 1, Line 2 XML parsing: line 0, character 0, unrecognized input signature). It bombs out at the same number of records as your original query, returning results for 135 reports (240 records total), although we actually have 262 reports. I've tried to research the error, but I'm not finding anything that is helping me understand it - probably due to my lack of knowledge of XML.
Do you have any ideas of where I can look to try to decipher the error, as well as good resources basic xml queries? I would really appreciate any guidance.
Here's the query: ;WITH xmlnamespaces ( default 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition', 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd ) SELECT NAME AS reportname, x.value('DataSourceName[1]', 'VARCHAR(max)') AS datasourcename, q.value('@Name[1]', 'VARCHAR(max)') AS datasetname, x.value('CommandType[1]', 'VARCHAR(max)') AS CommandType, x.value('CommandText[1]','VARCHAR(max)') AS CommandText FROM ( SELECT NAME, CAST(CAST(content AS varbinary(MAX)) AS xml) AS reportxml FROM reportserver.dbo.catalog ) a CROSS apply reportxml.nodes('/Report/DataSets/DataSet') d(q) CROSS apply q.nodes('Query') r(x) WHERE NOT patindex('%.%', name) > 0
Note: when I execute SELECT @@VERSION, I get this: Microsoft SQL Server 2005 - 9.00.3186.00 (X64) Aug 11 2007 05:31:24 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2) .
Marianne, I have written a collection of XQuery tutorials that might help you. You can find them here: http://blog.beyondrelational.com/2008/06/xquery-labs-collection-of-xquery-sample.html
FYI - just in case anyone else runs into the same issue I had, I wanted to post the resolution. Once I limited the subquery to Type = 2 (for reports only), the query ran successfully.
Here's the revised code: ;WITH xmlnamespaces ( default 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition', 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd ) SELECT NAME AS reportname, x.value('DataSourceName[1]', 'VARCHAR(max)') AS datasourcename, q.value('@Name[1]', 'VARCHAR(max)') AS datasetname, x.value('CommandType[1]', 'VARCHAR(max)') AS CommandType, x.value('CommandText[1]','VARCHAR(max)') AS CommandText FROM ( SELECT NAME, CAST(CAST(content AS varbinary(MAX)) AS xml) AS reportxml FROM reportserver.dbo.catalog where [type] = 2 ) a CROSS apply reportxml.nodes('/Report/DataSets/DataSet') d(q) CROSS apply q.nodes('Query') r(x) WHERE NOT patindex('%.%', name) > 0
Posts
Good stuff. Add the following to stop it failing when it comes across an embedded image file...
ReplyDeleteWHERE NOT patindex('%.%', name) > 0
Thanks! This reduced a lot of work for me! /Sven
ReplyDeleteHi. I'm getting an error when I run this:
ReplyDeleteMsg 9403, Level 16, State 1, Line 1
XML parsing: line 0, character 0, unrecognized input signature
I admit to knowing nothing about XML, so I'm not quite sure what could be the problem. Any ideas?
Thanks!
Hi. I'm getting an error when I run this:
ReplyDeleteMsg 9403, Level 16, State 1, Line 1
XML parsing: line 0, character 0, unrecognized input signature
I admit to knowing nothing about XML, so I'm not quite sure what could be the problem. Any ideas?
Thanks!
Never mind my previous comment - I missed missed where you said to change to VARCHAR(max).
ReplyDeleteThanks!
Argh! I spoke too soon. Even with the VARCHAR(max), I get "Msg 9403, Level 16, State 1, Line 1
ReplyDeleteXML parsing: line 0, character 0, unrecognized input signature"
I doubt you have a spelling mistake, specifically in the part where the namespace is declared. Could you post the code you are running and I will try to spot if there is something wrong with it.
ReplyDeleteI ran the code from the blog:
ReplyDelete;WITH XMLNAMESPACES (
DEFAULT
'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
)
SELECT
name,
x.value('CommandType[1]', 'VARCHAR(50)') AS CommandType,
x.value('CommandText[1]','VARCHAR(50)') AS CommandText
FROM (
select name,
CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
from ReportServer.dbo.Catalog
) a
CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet/Query') r(x)
The syntax looks correct.
ReplyDeleteAre you sure that you are connected to an instance of SQL Server 2005? The code provided in this post uses XQuery that is available only on SQL Server 2005 and later versions.
Yes, we're definitely on SQL Server 2005. The code works on our dev box, but not in production. In production, it returns a partial result set (about 200 records, but I would expect well over 700 records) and the aforementioned error.
ReplyDeleteDid you try applying the filter suggested by "anonymous" at the top of the comment list?
ReplyDeleteHi Jacob. I did apply the filter suggested. I am now trying to use the query below, which tweaks yours slightly to add the data source and data set (obtained from http://tejasnshah.wordpress.com/2009/02/21/sql-server-how-to-stored-procedure-names-used-by-reports-sql-reporting-services/). However, I am still getting the same error (Msg 9403, Level 16, State 1, Line 2
ReplyDeleteXML parsing: line 0, character 0, unrecognized input signature). It bombs out at the same number of records as your original query, returning results for 135 reports (240 records total), although we actually have 262 reports.
I've tried to research the error, but I'm not finding anything that is helping me understand it - probably due to my lack of knowledge of XML.
Do you have any ideas of where I can look to try to decipher the error, as well as good resources basic xml queries? I would really appreciate any guidance.
Here's the query:
;WITH xmlnamespaces (
default
'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
)
SELECT
NAME AS reportname,
x.value('DataSourceName[1]', 'VARCHAR(max)') AS datasourcename,
q.value('@Name[1]', 'VARCHAR(max)') AS datasetname,
x.value('CommandType[1]', 'VARCHAR(max)') AS CommandType,
x.value('CommandText[1]','VARCHAR(max)') AS CommandText
FROM
(
SELECT NAME,
CAST(CAST(content AS varbinary(MAX)) AS xml) AS reportxml
FROM reportserver.dbo.catalog
) a
CROSS apply reportxml.nodes('/Report/DataSets/DataSet') d(q)
CROSS apply q.nodes('Query') r(x)
WHERE NOT patindex('%.%', name) > 0
Note: when I execute SELECT @@VERSION, I get this:
Microsoft SQL Server 2005 - 9.00.3186.00 (X64) Aug 11 2007 05:31:24 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2) .
Marianne,
ReplyDeleteI have written a collection of XQuery tutorials that might help you. You can find them here: http://blog.beyondrelational.com/2008/06/xquery-labs-collection-of-xquery-sample.html
Jacob,
ReplyDeleteThanks. I'll definitely check out that link
FYI - just in case anyone else runs into the same issue I had, I wanted to post the resolution. Once I limited the subquery to Type = 2 (for reports only), the query ran successfully.
ReplyDeleteHere's the revised code:
;WITH xmlnamespaces (
default
'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
)
SELECT
NAME AS reportname,
x.value('DataSourceName[1]', 'VARCHAR(max)') AS datasourcename,
q.value('@Name[1]', 'VARCHAR(max)') AS datasetname,
x.value('CommandType[1]', 'VARCHAR(max)') AS CommandType,
x.value('CommandText[1]','VARCHAR(max)') AS CommandText
FROM
(
SELECT NAME,
CAST(CAST(content AS varbinary(MAX)) AS xml) AS reportxml
FROM reportserver.dbo.catalog
where [type] = 2
) a
CROSS apply reportxml.nodes('/Report/DataSets/DataSet') d(q)
CROSS apply q.nodes('Query') r(x)
WHERE NOT patindex('%.%', name) > 0
Marianne, Thanks for posting this.
ReplyDelete