This portal is to share my acquired knowledge related to Sql Server. I hope will be helpful for novice and professionals.

Monday, November 22, 2010

Inline queries in Dataset Reporting Services

Suppose we are planning to change database schema, we are sure that we can use syscomments table to find out that which Stored Procedure may get break after made changes.
But what about those reports having dataset with inline query.

Today I found a very good article on this topic.
(http://feodorgeorgiev.com/blog/2010/07/find-t-sql-in-your-report-server/)

We can find all dataset queries to dbo.Catalog table in ReportServer database.
Here is the query :


WITH XMLNAMESPACES
(
'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' AS REP
)

SELECT
c.Path,
c.Name,
DataSetXML.value('@Name','varchar(MAX)') DataSourceName,
DataSetXML.value('REP:Query[1]/REP:CommandText[1]','varchar(MAX)') CommandText
FROM
(SELECT
ItemID,
CAST(CAST(Content AS varbinary(max)) AS xml) ReportXML
FROM
dbo.Catalog
WHERE
Type = 2) ReportXML
CROSS APPLY ReportXML.nodes('//REP:DataSet') DataSetXML (DataSetXML)
INNER JOIN dbo.Catalog c
ON ReportXML.ItemID = c.ItemID
-- Search by part of the query text
WHERE (DataSetXML.value('REP:Query[1]/REP:CommandText[1]','varchar(MAX)')) LIKE '%...enter text here...%'

After running this query , we can check which report may get break.


Randhir

No comments: