The following T-SQL User Defined Functions running against Reporting Services database serve the following purposes
• Post deployment audit of reports and data sets
• Analysis of report dependencies across one or multiple reports
PRINT 'Date: ' + CONVERT(VARCHAR(24),GETDATE(),13) + ' Server Name: ' + @@servername + ' Service Name: ' + @@servicename
GO
IF EXISTS
(SELECT *
FROM sys.objects
WHERE name = 'Extract_Report_Info')
DROP FUNCTION dbo.Extract_Report_Info
PRINT 'DROP FUNCTION dbo.Extract_Report_Info'
GO
PRINT 'CREATE FUNCTION dbo.Extract_Report_Info'
GO
CREATE FUNCTION dbo.Extract_Report_Info(
/*****************************************************************************
Purpose: Extracts the contents of all Reports, Data Sources, Report Parts and Shared Datasets (Types 2,5,7, & 8) from the database
Change Log
# Date Who Change
******************************************************************************/
@rptName NVARCHAR(255)
,@rptPath NVARCHAR(255))
RETURNS @tempTBL TABLE (
ItemID NVARCHAR(255)
,Name NVARCHAR(255)
,[Path] NVARCHAR(MAX)
,[Type] NVARCHAR(255)
,TypeDescription NVARCHAR(MAX)
,Content varbinary(MAX)
,ContentVarchar NVARCHAR(MAX)
,ContentXML xml
)
AS
BEGIN
--The first CTE gets the content AS a varbinary(MAX)
--as well AS the other important columns for all reports,
--data sources and shared datasets.
WITH ItemContentBinaries AS
(
SELECT
ItemID
,Name
,[Path]
,[Type]
,CASE Type
WHEN 2 THEN 'Report'
WHEN 5 THEN 'Data Source'
WHEN 7 THEN 'Report Part'
WHEN 8 THEN 'Shared Dataset'
ELSE 'Other'
END AS TypeDescription
,CONVERT(varbinary(MAX),Content) AS Content
--FROM OPENROWSET('SQLNCLI', 'Server=COA-DARC-SQL05\DEV_INTEGRATION;Trusted_Connection=yes;',
-- 'SELECT * FROM ReportServer.dbo.Catalog')
FROM ReportServer.dbo.Catalog
WHERE Type IN (2,5,7,8)
),
--The second CTE strips off the BOM if it exists...
ItemContentNoBOM AS
(
SELECT
ItemID
,Name
,[Path]
,[Type]
,TypeDescription
,CASE
WHEN LEFT(Content,3) = 0xEFBBBF
THEN CONVERT(varbinary(MAX),SUBSTRING(Content,4,LEN(Content)))
ELSE
Content
END AS Content
FROM ItemContentBinaries
)
--The outer query gets the content in its varbinary, varchar and xml representations...
INSERT INTO @tempTBL
SELECT
ItemID
,Name
,[Path]
,[Type]
,TypeDescription
,Content --varbinary
,CONVERT(varCHAR(MAX),Content) AS ContentVarchar --varchar
,CONVERT(xml,Content) AS ContentXML --xml
FROM ItemContentNoBOM
WHERE (ISNULL(@rptName,'') = ''
OR
name LIKE '%' + @rptName + '%')
AND
(ISNULL(@rptPath,'') = ''
OR
[Path] LIKE '%' + @rptPath + '%')
RETURN
END
GO
/*******************************************************************
Test Sample:
SELECT * FROM dbo.Extract_Report_Info('{Report Name},'{Report Path}')
SELECT * FROM dbo.Extract_Report_Info('{Report Name},',NULL)
SELECT * FROM dbo.Extract_Report_Info(NULL,Null)
********************************************************************/
----------------------------------------------------------------------------------------------------------
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOPRINT 'Date: ' + CONVERT(VARCHAR(24),GETDATE(),13) + ' Server Name: ' + @@servername + ' Service Name: ' + @@servicename
GOIF EXISTS
(SELECT *
FROM sys.objects
WHERE name = 'Extract_Dataset_Commands')
DROP FUNCTION dbo.Extract_Dataset_Commands
PRINT 'DROP FUNCTION dbo.Extract_Dataset_Commands'
GOPRINT 'CREATE FUNCTION dbo.Extract_Dataset_Commands'
GOCREATE FUNCTION dbo.Extract_Dataset_Commands(
/****************************************************************************
Purpose : extracts the actual commands used for the each dataSET in each Report or Shared Dataset.Change Log
# Date Who Change
*****************************************************************************/
@rptName NVARCHAR(255)
,@rptPath NVARCHAR(255)
,@cmdText NVARCHAR(255))
RETURNS @tempTBL TABLE ( ItemID NVARCHAR(255)
,Name NVARCHAR(255)
,[Path] NVARCHAR(MAX)
,[Type] NVARCHAR(255)
,TypeDescription NVARCHAR(MAX)
,ContentXML xml
,CommandType NVARCHAR(MAX)
,CommandText NVARCHAR(MAX)
)
AS
BEGIN
--The first CTE gets the content AS a varbinary(MAX)
--as well AS the other important columns for all reports,
--data sources and shared datasets.
WITH ItemContentBinaries AS
(
SELECT
ItemID
,Name
,[Path]
,[Type]
,CASE Type
WHEN 2 THEN 'Report'
WHEN 5 THEN 'Data Source'
WHEN 7 THEN 'Report Part'
WHEN 8 THEN 'Shared Dataset'
ELSE 'Other'
END AS TypeDescription
,CONVERT(varbinary(MAX),Content) AS Content
--FROM OPENROWSET('SQLNCLI', 'Server=COA-DARC-SQL05\DEV_INTEGRATION;Trusted_Connection=yes;',
-- 'SELECT * FROM ReportServer.dbo.Catalog')
FROM ReportServer.dbo.Catalog
WHERE Type IN (2,5,7,8)
),
--The second CTE strips off the BOM if it exists...
ItemContentNoBOM AS
(
SELECT
ItemID
,Name
,[Path]
,[Type]
,TypeDescription
,CASE
WHEN LEFT(Content,3) = 0xEFBBBF
THEN CONVERT(varbinary(MAX),SUBSTRING(Content,4,LEN(Content)))
ELSE
Content
END AS Content
FROM ItemContentBinaries
)
--The old outer query is now a CTE to get the content in its xml form only...
,ItemContentXML AS
(
SELECT ItemID
,Name
,[Path]
,[Type]
,TypeDescription
,CONVERT(xml,Content) AS ContentXML
FROM ItemContentNoBOM
)
--now use the XML data type to extract the queries, and their command types and text....
INSERT INTO @tempTBL
SELECT
ItemID
,Name
,[Path]
,[Type]
,TypeDescription
,ContentXML
,ISNULL(Query.value('(./*:CommandType/text())[1]','NVARCHAR(1024)'),'Query') AS CommandType
,Query.value('(./*:CommandText/text())[1]','NVARCHAR(MAX)') AS CommandText
FROM ItemContentXML
--Get all the Query elements (The "*:" ignores any xml namespaces)
CROSS APPLY ItemContentXML.ContentXML.nodes('//*:Query') Queries(Query)
WHERE (ISNULL(@rptName,'') = ''
OR
name LIKE '%' + @rptName + '%')
AND
(ISNULL(@rptPath,'') = ''
OR
[Path] LIKE '%' + @rptPath + '%')
AND
(ISNULL(@cmdText,'') = ''
OR
Query.value('(./*:CommandText/text())[1]','NVARCHAR(MAX)') LIKE '%' + @cmdText + '%')
RETURN
END
GO
/*****************************************************************************
Test Sample:
SELECT * FROM dbo.Extract_Dataset_Commands(‘{Report Name}','{Report Path}',NULL)
SELECT * FROM dbo.Extract_Dataset_Commands('{Report Name}',NULL, 'select')
SELECT * FROM dbo.Extract_Dataset_Commands(NULL,'{Report Path}’','SELECT')
*****************************************************************************/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOPRINT 'Date: ' + CONVERT(VARCHAR(24),GETDATE(),13) + ' Server Name: ' + @@servername + ' Service Name: ' + @@servicename
GOIF EXISTS( SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
WHERE VIEW_DEFINITION LIKE '%dbo.Shared_Data_Sources%')
BEGIN
DROP VIEW dbo.Shared_Data_Sources
PRINT 'DROP VIEW dbo.Shared_Data_Sources'
END
GOPRINT 'CREATE VIEW dbo.Shared_Data_Sources'
GO/************************************************************************************************************************************
Reports Audit - Extract Shared Data SourcesChange Log
# Date Who Change
*************************************************************************************************************************************/
CREATE VIEW dbo.Shared_Data_Sources
AS
WITH XMLNAMESPACES
-- XML namespace def must be the first in with clause.
(
DEFAULT
'http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource'
,
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'
AS rd
)
,SDS
AS (
SELECT SDS.NAME AS SharedDsName
,SDS.[Path]
,CONVERT(XML, CONVERT(VARBINARY(max), content)) AS DEF
FROM dbo.[Catalog] AS SDS
WHERE SDS.Type = 5
) -- 5 = Shared Datasource
SELECT CON.[Path]
,CON.SharedDsName
,CON.ConnString
FROM (
SELECT SDS.[Path]
,SDS.SharedDsName
,DSN.value('ConnectString[1]', 'varchar(150)') AS
ConnString
FROM SDS
CROSS APPLY SDS.DEF.nodes('/DataSourceDefinition') AS R(DSN
)
) AS CON
GOSET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOPRINT 'Date: ' + CONVERT(VARCHAR(24),GETDATE(),13) + ' Server Name: ' + @@servername + ' Service Name: ' + @@servicename
GOIF EXISTS( SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
WHERE VIEW_DEFINITION LIKE '%dbo.Report_Data_Sources%')
BEGIN
DROP VIEW dbo.Report_Data_Sources
PRINT 'DROP VIEW dbo.Report_Data_Sources'
END
GOPRINT 'CREATE VIEW dbo.Report_Data_Sources'
GO/************************************************************************************************************************************
Reports Audit - Extract Report Data SourcesChange Log
# Date Who Change
*************************************************************************************************************************************/
CREATE VIEW dbo.Report_Data_Sources
AS
SELECT C.ItemID
,C.Name
,C.Path
,C.Type
,CASE C.Type WHEN 2 THEN 'Report'
WHEN 5 THEN 'Data Source'
WHEN 7 THEN 'Report Part'
WHEN 8 THEN 'Shared Dataset'
ELSE 'Other' END AS TypeDescription
, CONVERT(varbinary(MAX), C.[Content]) AS Content
, D.Name DataSourceName
FROM dbo.Catalog C LEFT JOIN
dbo.DataSource D
ON C.ItemID = D.ItemID
WHERE (C.Type IN (2, 5, 7, 8))
GO
It allows:· Extract from shared data source the connection string for each report
SELECT R.ItemID
,R.Name
,R.Path
,R.Type
,R.TypeDescription
,R.[Content]
,R.DataSourceName
FROM dbo.Report_Data_Sources R LEFT OUTER JOIN
dbo.Shared_Data_Sources S
ON R.DataSourceName = S.SharedDsName
WHERE R.Name = '{Report Name}'· Identify reports which have no shared data sources attachedSELECT R.ItemID
,R.Name
,R.Path
,R.Type
,R.TypeDescription
,R.[Content]
,R.DataSourceName
FROM dbo.Report_Data_Sources R LEFT OUTER JOIN
dbo.Shared_Data_Sources S
ON R.DataSourceName = S.SharedDsName
WHERE DataSourceName IS NULL
The above udf’s can be executed from the following PowerShell Script: #Query a SQL database
$SQLServer = "DB\INSTANCE";
$SQLDBName = "ReportServer";
$rptName = "Report Name";
$rptPath = "Report Path";
$SqlQuery = "SELECT * FROM [dbo].[Extract_Report_Info]('" + $rptName + "'," + "'" + $rptPath + "');";
$smtp = "smtp address";
$From = "noreply email address";
$To = "destination email address";
$connectionName = "Extract_RS_Info";
$commandTimeout = 15; clear;
$SqlQuery; Function Extract_RS_Info { Param ( $server = $SQLServer
,$database = $SQLDBName
,$smtp
,$From
,$To
,$rptName
,$rptPath
,$SqlQuery
,$connectionName
,$commandTimeout)
Try{
$conn =new-object ('System.Data.SqlClient.SqlConnection')
$connString = "Server=$server;Integrated Security=SSPI;Database=$database;Application Name=$connectionName"
$conn.ConnectionString = $connString
Write-Debug ("Function: Query-SQL: $server $database")
if (test-path variable:\conn) {
$conn.close()
} else {
$conn =new-object ('System.Data.SqlClient.SqlConnection')
}
$conn.Open()
$sqlCmd =New-Object System.Data.SqlClient.SqlCommand
$sqlCmd.CommandTimeout = $CommandTimeout
$sqlCmd.CommandText = $SqlQuery
$sqlCmd.Connection = $conn
$data = $sqlCmd.ExecuteReader()
while ($data.read() -eq $true) {
$max = $data.FieldCount -1
$obj =New-Object Object
For ($i = 0; $i -le $max; $i++) {
$name = $data.GetName($i)
if ($name.length -eq 0) {
$name = "field$i"
}
$obj |Add-Member Noteproperty $name -value $data.GetValue($i) -Force
}
$obj
}
$conn.close()
$conn = $null
} Catch {
$ErrorMessage = $_.Exception.Message;
$FailedItem = $_.Exception.ItemName;
$body = "We failed to read file $FailedItem. The error message was $ErrorMessage";
Send-MailMessage -From $From -To $To -Subject "Extract_RS_Info.ps1 failed!" -SmtpServer $smtp -Body $body;
Break
}
} Extract_RS_Info $SQLServer $SQLDBName $smtp $From $To $rptName $rptPath $SqlQuery $connectionName $commandTimeout; $str = "*" * 80;
write-host $str;
$SqlQuery = "SELECT * FROM [dbo].[Extract_Dataset_Commands]('" + $rptName + "','" + $rptPath + "','" + $cmdText + "');";
$SqlQuery; Extract_RS_Info $SQLServer $SQLDBName $smtp $From $To $rptName $rptPath $SqlQuery $connectionName $commandTimeout;
If you find this script useful please donate generously.
