July 11, 2016 at 7:28 am
Hi,
I have a requirement to identify reports where a range of values (customer preference codes) have been hard coded.
For reports that use stored procedures this is easy (using Red Gate's brilliant SQL Search tool), but a lot of our reports have inline code embedded in them that were written by a previous developer.
There's about 100 reports so I don't want to open each one and check manually.
Is there a way to find and extract inline SQL code from SSRS? Once I have this extracted I can do a quick search to see if any of the values I'm looking for exist.
Hope you can help!
Many thanks as always
Lins
July 11, 2016 at 7:30 am
Quick though, possibly the most straight forward thing to do is to run the Profiler and catch the actual statements being run.
😎
July 11, 2016 at 7:47 am
lindsayscott23 (7/11/2016)
Hi,I have a requirement to identify reports where a range of values (customer preference codes) have been hard coded.
For reports that use stored procedures this is easy (using Red Gate's brilliant SQL Search tool), but a lot of our reports have inline code embedded in them that were written by a previous developer.
There's about 100 reports so I don't want to open each one and check manually.
Is there a way to find and extract inline SQL code from SSRS? Once I have this extracted I can do a quick search to see if any of the values I'm looking for exist.
Hope you can help!
Many thanks as always
Lins
If you have access to the report server database then you can run this query to search through published report datasets:
WITH ItemContentBinaries AS
(
SELECT
ItemID
,CASE WHEN ParentID IS NOT NULL THEN Name ELSE 'Home' END AS Name
,CASE WHEN ParentID IS NOT NULL THEN [Path] ELSE '/' END AS [Path]
,ParentID
,[Type]
,CASE Type
WHEN 1 THEN 'Folder'
WHEN 2 THEN 'Report'
WHEN 3 THEN 'Resource'
WHEN 4 THEN 'Linked Report'
WHEN 5 THEN 'Data Source'
WHEN 6 THEN 'Report Model'
WHEN 7 THEN 'Report Part'
WHEN 8 THEN 'Shared Dataset'
ELSE 'Other'
END AS TypeDescription
,CONVERT(varbinary(max),Content) AS Content
FROM ReportServer.dbo.Catalog
WHERE Type IN (2,5,7,8, 5) --You could limit the query to return only certain types here....
),
-- The second CTE strips off the BOM if it exists from the
-- beginning of the XML.
ItemContentNoBOM AS
(
SELECT
ItemID
,Name
,[Path]
,ParentID
,[Type]
,TypeDescription
,CASE
WHEN LEFT(Content,3) = 0xEFBBBF
THEN CONVERT(varbinary(max),SUBSTRING(Content,4,LEN(Content)))
ELSE
Content
END AS Content
FROM ItemContentBinaries
),
--This CTE strips off the trailing 0x00 if there is one
ItemContentNoNullTerm AS
(
SELECT
ItemID
,Name
,[Path]
,ParentID
,[Type]
,TypeDescription
,CASE
WHEN RIGHT(Content,1) = 0x00
THEN CONVERT(varbinary(max),LEFT(Content,LEN(Content)-1))
ELSE
Content
END AS Content
FROM ItemContentNoBOM
),
--This CTE gets the content in its varbinary, varchar and xml representations...
CatalogContentView AS
(
SELECT
ItemID
,Name
,[Path]
,ParentID
,[Type]
,TypeDescription
,Content --varbinary
,CONVERT(varchar(max),Content) AS ContentVarchar --varchar
,CONVERT(xml,Content) AS ContentXML --xml
FROM ItemContentNoNullTerm)
,
AllReportsDataSets
AS
(
SELECT
ItemID
,Name
,[Path]
,ParentID
,[Type]
,TypeDescription
,ISNULL(DataSet.value('./@Name','nvarchar(1024)'),'Text') AS DataSetName
,ISNULL(DataSet.value('(./*:Query/*:CommandText/text())[1]','nvarchar(max)'),'') AS CommandText
,ContentXML
FROM CatalogContentView AS CCV
--Get all the Query elements (The "*:" ignores any xml namespaces)
CROSS APPLY CCV.ContentXML.nodes('//*:DataSet') DataSets(DataSet))
SELECT
*
FROM
AllReportsDataSets
WHERE
CommandText LIKE '%Whatever%'
July 11, 2016 at 8:59 am
You can use Notepad++ and the option "Search in files".
You could possibly use a Regular expression as "<CommandText>*.SELECT", without the quotes.
Remember that reports are simply xml files.
July 11, 2016 at 9:25 am
Hi,
Thanks everyone for your helpful suggestions. Some cool approaches that I wouldn't have thought of at all.
PB_BI: I ran your code and it works great. I found just 3 reports out of 100 contained what I was looking for. Glad I didn't have to find that out manually! Thanks loads for your solution.
Really appreciate all your help.
Cheers
lins
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply