Urgent Requirement: How to find ALL data sources in report server
We have tons of reports and a similar number of data sources. We are in the process of migrating databases between different version of SQL Server and need to know where all the data sources are that reference the old databases so we can update them to point
to the new SQL Server. This is proving to be difficult. Report Manager does not seem to provide an easy way to find all data sources. I came across another post where a response by Jin Chen provided a VB script, available here:
http://cid-3c7e963ff6ccd974.skydrive.live.com/self.aspx/.Public/GetPropertiesOfDataSources.rss
This script provides very useful information but required some updates to improve the format of the output and to include additional information such as the login credentials used by the data source and the path where the data
source can be found.
However, we discovered that the script does not actually find ALL data sources but we don't understand a) the architecture in report server sufficiently and b) VB well enough to update the script to be comprehensive.
If anyone can help to improve this script, or provide an alternative method by which we can retrieve ALL data sources, it would be very much appreciated as we are in desperate need of this functionality.
We updated the script created by Jin Chen, the code is provided below.
'=============================================================================
' File: PublishSampleReports.rss
'
' Summary: Demonstrates a script that can be used with RS.exe to
' publish the sample reports that ship with Reporting Services.
'
'---------------------------------------------------------------------
' This file is part of Microsoft SQL Server Code Samples.
'
' Copyright (C) Microsoft Corporation. All rights reserved.
'
' This source code is intended only as a supplement to Microsoft
' Development Tools and/or on-line documentation. See these other
' materials for detailed information regarding Microsoft code samples.
'
' THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
' KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
' IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
' PARTICULAR PURPOSE.
Public Sub Main()
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
'Create the parent folder
GetCatalogItems(String.Empty)
End Sub
' <summary>
' recursivly get folder items from report
' server catalog; render to treeview control
' </summary>
' <param name="catalogPath"></param>
' <param name="parentNode"></param>
Public Sub GetCatalogItems(ByVal catalogPath As String)
Dim items As CatalogItem()
Try
' if catalog path is empty, use root, if not pass the folder path
If catalogPath.Length = 0 Then
' no recursion (false)
items = rs.ListChildren("/", False)
Else
' no recursion (false)
items = rs.ListChildren(catalogPath, False)
End If
' iterate through catalog items and populate treeview control
For Each item As CatalogItem In items
' if folder is hidden, skip it
If item.Hidden <> True Then
' ensure only folders are rendered
If item.Type.Equals(ItemTypeEnum.DataSource) Then
'use the GetDataSourceContents to get the definition of the data source.
Dim definition As DataSourceDefinition = Nothing
Try
definition = rs.GetDataSourceContents(item.Path)
Console.WriteLine(item.Name)
Console.WriteLine(item.Path)
Console.WriteLine("Connection String: {0}", definition.ConnectString)
Console.WriteLine("Extension name: {0}", definition.Extension)
Console.WriteLine("UserName: {0}", definition.UserName)
Catch e As SoapException
Console.WriteLine(e.Detail.InnerXml.ToString())
End Try
Console.WriteLine(item.Name)
Console.WriteLine()
ElseIf item.Type.Equals(ItemTypeEnum.Folder) Then
' recurse
GetCatalogItems(item.Path)
End If
End If
Next
Catch ex As Exception
Console.WriteLine(ex.Message)
Finally
'Do nothing
End Try
End Sub
Michael MacGregor, Senior SQL Server DBA, Carlson Marketing
February 12th, 2011 1:03am
Hi Michael,
You can run the Query at the
Reportserver database to find the Datasource. Please run the
bwlow Query. Hope it fits the requirement. There are Other Queries to below.
--Find the procedure names that are being used along with report, datasource names
;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,
x.value('DataSourceName[1]','VARCHAR(50)')
AS DataSource
FROM
(
select name,
CAST(CAST(content
AS VARBINARY(MAX))
AS XML)
AS reportXML
from ReportServer.dbo.Catalog
where content
is not
null
and
type = 2
) a
CROSS
APPLY reportXML.nodes('/Report/DataSets/DataSet/Query') r(x)
WHERE x.value('CommandType[1]',
'VARCHAR(50)')
= 'StoredProcedure'
ORDER
BY name
--Find the procedure names that are used in reports
;WITH XMLNAMESPACES
(
DEFAULT
'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'
AS rd
)
SELECT
distinct 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
where content
is not
null
and
type = 2
) a
CROSS
APPLY reportXML.nodes('/Report/DataSets/DataSet/Query') r(x)
WHERE x.value('CommandType[1]',
'VARCHAR(50)')
= 'StoredProcedure'
--Get all text adhoc sql in the reports
;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,
x.value('DataSourceName[1]','VARCHAR(50)')
AS DataSource
FROM
(
select name,
CAST(CAST(content
AS VARBINARY(MAX))
AS XML)
AS reportXML
from ReportServer.dbo.Catalog
where content
is not
null
and
type != 3
) a
CROSS
APPLY reportXML.nodes('/Report/DataSets/DataSet/Query') r(x)
WHERE x.value('CommandType[1]',
'VARCHAR(50)')
is null --= 'CommandText'
ORDER
BY name--Find the distinct reports
;WITH XMLNAMESPACES
(
DEFAULT
'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'
AS rd
)
SELECT
distinct name
FROM
(
select name,
CAST(CAST(content
AS VARBINARY(MAX))
AS XML)
AS reportXML
from ReportServer.dbo.Catalog
where content
is not
null
and
type = 2
) a
ORDER
BY name
--Get the data sources
select
Path, Name
from ReportServer.dbo.Catalog
where
Type = 5
--Get the report names and their paths
select
Path, Name
from ReportServer.dbo.Catalog
where
Type = 2
--Get the supporting docs used in the reports
select
Path, Name
from ReportServer.dbo.Catalog
where
Type = 3
--Is there a cache policy to expire after n minutes?
select
Path, Name, ExpirationFlags, CacheExpiration
from ReportServer.dbo.Catalog
c join ReportServer.dbo.CachePolicy cp
on c.ItemID
= cp.reportID
where ExpirationFlags
= 1
--Is there a cache policy to expire on a report specific/shared schedule?
select
Path, Name, ExpirationFlags, CacheExpiration
from ReportServer.dbo.Catalog
c join ReportServer.dbo.CachePolicy cp
on c.ItemID
= cp.reportID
where ExpirationFlags
= 2
--Which users are using these reports
select UserName
from ReportServer.dbo.Users
where UserName
not in
('Everyone',
'NT AUTHORITY\SYSTEM',
'NT AUTHORITY\NETWORK SERVICE',
'BUILTIN\Administrators',
'Domain Users')
--find the favorite rendering types
select format,
count(1)
as cnt
from ReportServer.dbo.ExecutionLog
group
by format
order
by cnt
--Get the most frequently used reports
select c.Path,
c.Name,
count(1)
as cnt
from ReportServer.dbo.ExecutionLog
e join ReportServer.dbo.Catalog c
on e.ReportID
= c.ItemID
group
by c.Path, c.Name
order
by cnt desc
--Get the reports that take longer to retrieve the data
select c.Path,
c.Name,
max(TimeDataRetrieval)/1000.
as seconds
from ReportServer.dbo.ExecutionLog
e join ReportServer.dbo.Catalog c
on e.ReportID
= c.ItemID
group
by c.Path, c.Name
order
by seconds desc
--Get the most frequent users
select UserName,
count(1)
as cnt from ReportServer.dbo.ExecutionLog
group
by UserName
order
by cnt desc
--Get the latest subscription reports delivered
select c.Path,
c.Name, LastRunTime,
replace(LastStatus,
'Mail sent to ','')
as lastStatus
from ReportServer.dbo.Subscriptions
s join ReportServer.dbo.Catalog c
on s.Report_OID
= c.ItemID
order
by LastRunTime desc
--get users and thier roles
select UserName, RoleName,
Description
from ReportServer.dbo.Roles
r join ReportServer.dbo.PolicyUserRole pur
on r.roleid
= pur.roleid
join ReportServer.dbo.Users u
on pur.userid
= u.userid
where UserName
not in
('BUILTIN\Administrators',
'Domain Users',
'IUSR_REPORT02')
order
by UserName
Hope It works,
Regards,
PS
Regards, PS
Free Windows Admin Tool Kit Click here and download it now
February 13th, 2011 2:32am
Hi Michael,
You can run the Query at the
Reportserver database to find the Datasource. Please run the
bwlow Query. Hope it fits the requirement. There are Other Queries to below.
--Find the procedure names that are being used along with report, datasource names
;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,
x.value('DataSourceName[1]','VARCHAR(50)')
AS DataSource
FROM
(
select name,
CAST(CAST(content
AS VARBINARY(MAX))
AS XML)
AS reportXML
from ReportServer.dbo.Catalog
where content
is not
null
and
type = 2
) a
CROSS
APPLY reportXML.nodes('/Report/DataSets/DataSet/Query') r(x)
WHERE x.value('CommandType[1]',
'VARCHAR(50)')
= 'StoredProcedure'
ORDER
BY name
--Find the procedure names that are used in reports
;WITH XMLNAMESPACES
(
DEFAULT
'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'
AS rd
)
SELECT
distinct 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
where content
is not
null
and
type = 2
) a
CROSS
APPLY reportXML.nodes('/Report/DataSets/DataSet/Query') r(x)
WHERE x.value('CommandType[1]',
'VARCHAR(50)')
= 'StoredProcedure'
--Get all text adhoc sql in the reports
;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,
x.value('DataSourceName[1]','VARCHAR(50)')
AS DataSource
FROM
(
select name,
CAST(CAST(content
AS VARBINARY(MAX))
AS XML)
AS reportXML
from ReportServer.dbo.Catalog
where content
is not
null
and
type != 3
) a
CROSS
APPLY reportXML.nodes('/Report/DataSets/DataSet/Query') r(x)
WHERE x.value('CommandType[1]',
'VARCHAR(50)')
is null --= 'CommandText'
ORDER
BY name--Find the distinct reports
;WITH XMLNAMESPACES
(
DEFAULT
'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'
AS rd
)
SELECT
distinct name
FROM
(
select name,
CAST(CAST(content
AS VARBINARY(MAX))
AS XML)
AS reportXML
from ReportServer.dbo.Catalog
where content
is not
null
and
type = 2
) a
ORDER
BY name
--Get the data sources
select
Path, Name
from ReportServer.dbo.Catalog
where
Type = 5
--Get the report names and their paths
select
Path, Name
from ReportServer.dbo.Catalog
where
Type = 2
--Get the supporting docs used in the reports
select
Path, Name
from ReportServer.dbo.Catalog
where
Type = 3
--Is there a cache policy to expire after n minutes?
select
Path, Name, ExpirationFlags, CacheExpiration
from ReportServer.dbo.Catalog
c join ReportServer.dbo.CachePolicy cp
on c.ItemID
= cp.reportID
where ExpirationFlags
= 1
--Is there a cache policy to expire on a report specific/shared schedule?
select
Path, Name, ExpirationFlags, CacheExpiration
from ReportServer.dbo.Catalog
c join ReportServer.dbo.CachePolicy cp
on c.ItemID
= cp.reportID
where ExpirationFlags
= 2
--Which users are using these reports
select UserName
from ReportServer.dbo.Users
where UserName
not in
('Everyone',
'NT AUTHORITY\SYSTEM',
'NT AUTHORITY\NETWORK SERVICE',
'BUILTIN\Administrators',
'Domain Users')
--find the favorite rendering types
select format,
count(1)
as cnt
from ReportServer.dbo.ExecutionLog
group
by format
order
by cnt
--Get the most frequently used reports
select c.Path,
c.Name,
count(1)
as cnt
from ReportServer.dbo.ExecutionLog
e join ReportServer.dbo.Catalog c
on e.ReportID
= c.ItemID
group
by c.Path, c.Name
order
by cnt desc
--Get the reports that take longer to retrieve the data
select c.Path,
c.Name,
max(TimeDataRetrieval)/1000.
as seconds
from ReportServer.dbo.ExecutionLog
e join ReportServer.dbo.Catalog c
on e.ReportID
= c.ItemID
group
by c.Path, c.Name
order
by seconds desc
--Get the most frequent users
select UserName,
count(1)
as cnt from ReportServer.dbo.ExecutionLog
group
by UserName
order
by cnt desc
--Get the latest subscription reports delivered
select c.Path,
c.Name, LastRunTime,
replace(LastStatus,
'Mail sent to ','')
as lastStatus
from ReportServer.dbo.Subscriptions
s join ReportServer.dbo.Catalog c
on s.Report_OID
= c.ItemID
order
by LastRunTime desc
--get users and thier roles
select UserName, RoleName,
Description
from ReportServer.dbo.Roles
r join ReportServer.dbo.PolicyUserRole pur
on r.roleid
= pur.roleid
join ReportServer.dbo.Users u
on pur.userid
= u.userid
where UserName
not in
('BUILTIN\Administrators',
'Domain Users',
'IUSR_REPORT02')
order
by UserName
Hope It works,
Regards,
PS
Regards, PS
February 13th, 2011 2:32am
Hi PS,
Certainly getting closer but I need the actual data source, i.e. SQL Server name, and catalog being used. I'm really bad with XML and have tried a few things but cannot figure out how to get this information. Without that we cannot identify the data sources
that need to be updated due to migration from one SQL Server to another.
Thanks.
Michael MacGregor, Senior SQL Server DBA, Carlson Marketing
Free Windows Admin Tool Kit Click here and download it now
February 13th, 2011 12:56pm
Further clarification: All we need at this point in time is a list of the actual data sources, the name, path, SQL server, database, and login used.
It would then be useful to know which reports are using which data source, but first and foremost we need to identify data sources that need to be updated for server migrations.
Thanks.Michael MacGregor, Senior SQL Server DBA, Carlson Marketing
February 13th, 2011 1:16pm
Hi Michael,
Just to Clarify the need data sources(is it just the name of datasource ?), the name(Name of what actually ?), path (is it path of report ?), SQL server (i guess the link to which server), database (is it the data base within the server ?), and login used
(is it the login credentials ?).
Regards,
PS
Regards, PS
Free Windows Admin Tool Kit Click here and download it now
February 13th, 2011 1:23pm
Hi PS,
That's odd. I did post a reply to you yesterday but somehow it seems to have been lost. Oh well.
Yes, the name of the data source, the path where the data source can be found, the name of the SQL Server which the data source will connect to, the database that it will connect to, and the login credentials used to connect.
Thank you!Michael MacGregor, Senior SQL Server DBA, Carlson Marketing
February 14th, 2011 10:35am
Hi PS,
That's odd. I did post a reply to you yesterday but somehow it seems to have been lost. Oh well.
Yes, the name of the data source, the path where the data source can be found, the name of the SQL Server which the data source will connect to, the database that it will connect to, and the login credentials used to connect.
Thank you!Michael MacGregor, Senior SQL Server DBA, Carlson Marketing
Free Windows Admin Tool Kit Click here and download it now
February 14th, 2011 10:35am
Hi,
The code below may help you.
Public Sub Main()
Dim items As CatalogItem() = Nothing
Dim dataSource As DataSourceDefinition
Dim count as Integer = 0
Try
items = rs.ListChildren("/", True)
Console.WriteLine("Datasources:")
Console.WriteLine()
For Each catalogItem as CatalogItem in items
if (catalogItem.Type = ItemTypeEnum.DataSource)
Console.WriteLine(catalogItem.Path)
dataSource = rs.GetDataSourceContents(catalogItem.Path)
If Not (dataSource Is Nothing) Then
Console.WriteLine(" Connection String: {0}", dataSource.ConnectString)
Console.WriteLine(" Extension name: {0}", dataSource.Extension)
Console.WriteLine(" Credential retrieval: {0}", dataSource.CredentialRetrieval)
Console.WriteLine(" Windows credentials: {0}", dataSource.WindowsCredentials)
Console.WriteLine(" Username: {0}", dataSource.UserName)
Console.WriteLine(" Password: {0}", dataSource.Password)
Console.WriteLine("===================================")
End If
count = count + 1
end if
Next catalogItem
Console.WriteLine()
Console.WriteLine("Total {0} datasources", count)
Catch e As IOException
Console.WriteLine(e.Message)
End Try
End Sub
Remember to mark as an answer if this post has helped you.
February 15th, 2011 2:15pm
Igor,
Thanks, that indeed does the job.
I'd still really appreciate being able to directly query the reportserver catalog for this informatino as well though.
Michael MacGregor, Senior SQL Server DBA, Carlson Marketing
Free Windows Admin Tool Kit Click here and download it now
February 16th, 2011 12:54pm
Hi Michael,
Data source connect string in report database is encrypted, so we can't query the data source definition from the report database directly (but we can
query their name and path). The only way I can think to get decrypted connect string is through SOAP, just as Jin Chen and Igor Gelin have done so.
In addition, operation of Reporting Services database is not supported by Microsoft, so this approach is not recommended.
Thanks,
Albert Ye
February 17th, 2011 4:03am
Hi Michael,
Data source connect string in report database is encrypted, so we can't query the data source definition from the report database directly (but we can
query their name and path). The only way I can think to get decrypted connect string is through SOAP, just as Jin Chen and Igor Gelin have done so.
In addition, operation of Reporting Services database is not supported by Microsoft, so this approach is not recommended.
Thanks,
Albert Ye
Free Windows Admin Tool Kit Click here and download it now
February 17th, 2011 4:03am
Hi Albert,
If you run the following:
SELECT top 10 CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
FROM ReportServer.dbo.Catalog
WHERE content is not null
AND type = 2
You will see in the XML that much of the information is available, however I find querying XML baffling and cannot figure out how to get the information I need from that XML.
As for MS not supporting Reporting Services database, that's just plain silly, it's their product. From what I have been told since MS moved all the funcationality to the Report Manager, some useful information is no longer easily accessible, such as getting
all the data source information. IMHO MS should provide the means by which this information can still be retrieved.
However, at this time the script provided by Igor works fine and is good enough, but I'd still like to get the query against the XML to work as well.
Michael MacGregor, Senior SQL Server DBA, Carlson Marketing
February 17th, 2011 2:09pm
Hi Michael,
The below will give the Information on the data source Name,
Path and Connection String (Server name + Database Name). But login Credential are encrypted saved in the Report server Db and .Net environment, so you
cant run the query to get it.
;WITH XMLNAMESPACES
(
DEFAULT
'http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource'
)
SELECT
name,Path
, x.value('ConnectString[1]',
'VARCHAR(100)')
AS ConnectString
FROM
(
Select
CAST(CAST(content
AS VARBINARY(MAX))
AS XML)
AS reportXML ,Name,Path
from ReportServer.dbo.Catalog
Where Type
= 5 and content
is not Null
) a
CROSS
APPLY reportXML.nodes('/DataSourceDefinition') r(x)
ORDER
BY name
Hope It works a bit,
Regards,
PS
Regards, PS
Free Windows Admin Tool Kit Click here and download it now
February 19th, 2011 4:41am
Thanks PS. I guess I shall have to use the script provided by Igor to get the information I need. Thanks anyway.
Michael MacGregor, Senior SQL Server DBA, Carlson Marketing
February 23rd, 2011 2:36pm
This topic is archived. No further replies will be accepted.
Other recent topics
