locked
Extract metadata from report server database RRS feed

  • Question

  • We are going to have 500+ reports in SSRS, converting from Crystal, and we need to know everything about these reports. I have been using Business Objects, and their SDK allows you to extract every piece of meta data about a report - from the SQL statement to each and every field in the report, and the mapping of these fields to the data source.

    I have tried in vain to do this using a number of methods - XMLTextReder, XDocument, ReportingService2005.asmx, and I just can not get the data that I need.

    For starters, I need all the datasources, the connection string (not the PWD, just where is this report pointed), all the field mappings, and the server and data set SQL statement.

    Seems the ReporServer database is unusable (why is this!), and there are little to no samples on the web service, al least what I need. Thanks for any pointers on how to accomplish this.

    Rick

    Thursday, May 26, 2011 3:44 AM

Answers

  • The below two queries will give you what you need

    Note : You may have to change the schema definition 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition' based on your report version. This you can get by checking the Content field in catalog table.

    WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition', 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd )
    SELECT ReportName		 = name
        ,DataSourceName	 = x.value('(@Name)[1]', 'VARCHAR(250)') 
    	  ,DataProvider	 = x.value('(ConnectionProperties/DataProvider)[1]','VARCHAR(250)')
    	  ,ConnectionString = x.value('(ConnectionProperties/ConnectString)[1]','VARCHAR(250)')
     FROM ( SELECT C.Name,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML
          FROM ReportServer.dbo.Catalog C
         WHERE C.Content is not null
          AND C.Type = 2
    
     ) a
     CROSS APPLY reportXML.nodes('/Report/DataSources/DataSource') r ( x )
    ORDER BY name ;
    
    
    WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition', 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd )
    SELECT ReportName		= name
        ,DataSetName		= x.value('(@Name)[1]', 'VARCHAR(250)') 
    	  ,DataSourceName	= x.value('(Query/DataSourceName)[1]','VARCHAR(250)')
    	  ,CommandText		= x.value('(Query/CommandText)[1]','VARCHAR(250)')
    	  ,Fields			= df.value('(@Name)[1]','VARCHAR(250)')
    	  ,DataField		= df.value('(DataField)[1]','VARCHAR(250)')
    	  ,DataType		= df.value('(rd:TypeName)[1]','VARCHAR(250)')
    	  --,ConnectionString = x.value('(ConnectionProperties/ConnectString)[1]','VARCHAR(250)')
     FROM ( SELECT C.Name,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML
          FROM ReportServer.dbo.Catalog C
         WHERE C.Content is not null
          AND C.Type = 2
    	  ) a
     CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet') r ( x )
     CROSS APPLY x.nodes('Fields/Field') f(df) 
    ORDER BY name 
    
    • Marked as answer by rparge Thursday, May 26, 2011 5:55 AM
    Thursday, May 26, 2011 4:07 AM

All replies

  • Please take a look at Datasource table in ReportServer Database. There some more tables like Catalog; configurationinfo; Executionlog; Users; Snapshot data etc etc. Query each of these tables and then you will find the information what you are looking for. It is not fair to make statements like ReportServer database is unusable.

    Regards,


    Phani Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem.
    Thursday, May 26, 2011 3:55 AM
  • The below two queries will give you what you need

    Note : You may have to change the schema definition 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition' based on your report version. This you can get by checking the Content field in catalog table.

    WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition', 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd )
    SELECT ReportName		 = name
        ,DataSourceName	 = x.value('(@Name)[1]', 'VARCHAR(250)') 
    	  ,DataProvider	 = x.value('(ConnectionProperties/DataProvider)[1]','VARCHAR(250)')
    	  ,ConnectionString = x.value('(ConnectionProperties/ConnectString)[1]','VARCHAR(250)')
     FROM ( SELECT C.Name,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML
          FROM ReportServer.dbo.Catalog C
         WHERE C.Content is not null
          AND C.Type = 2
    
     ) a
     CROSS APPLY reportXML.nodes('/Report/DataSources/DataSource') r ( x )
    ORDER BY name ;
    
    
    WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition', 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd )
    SELECT ReportName		= name
        ,DataSetName		= x.value('(@Name)[1]', 'VARCHAR(250)') 
    	  ,DataSourceName	= x.value('(Query/DataSourceName)[1]','VARCHAR(250)')
    	  ,CommandText		= x.value('(Query/CommandText)[1]','VARCHAR(250)')
    	  ,Fields			= df.value('(@Name)[1]','VARCHAR(250)')
    	  ,DataField		= df.value('(DataField)[1]','VARCHAR(250)')
    	  ,DataType		= df.value('(rd:TypeName)[1]','VARCHAR(250)')
    	  --,ConnectionString = x.value('(ConnectionProperties/ConnectString)[1]','VARCHAR(250)')
     FROM ( SELECT C.Name,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML
          FROM ReportServer.dbo.Catalog C
         WHERE C.Content is not null
          AND C.Type = 2
    	  ) a
     CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet') r ( x )
     CROSS APPLY x.nodes('Fields/Field') f(df) 
    ORDER BY name 
    
    • Marked as answer by rparge Thursday, May 26, 2011 5:55 AM
    Thursday, May 26, 2011 4:07 AM
  • Thank you, this is super, what are the details on this SQL syntax, if you don't mind? What language is this?

    I had to change the namespaces to 2008/01, but I had to DL an RDL and open in up in notepad, as the content column in the CATALOG table has data like this:

    0x3C3F786D6C2076657273696F6E3D22312E....lots more.

     

    Thursday, May 26, 2011 5:55 AM
  • I have, there is nothing there that I can use, and I have read many threads that state that the ReportServer DB is not th be queried, as it is undocumentated and encrypted, and is subject to change.

    I started a thread some time ago asking this question, and never really got much discussion on it.

    I would like to understand this further.

    http://social.technet.microsoft.com/Forums/en/sqlreportingservices/thread/c42679bd-88d6-4a99-bd00-505c3297a1cc

    Thanks!

     

    Thursday, May 26, 2011 6:00 AM
  • The catalog table content column is varbinary. You should convert it into XML to read it

    SELECT CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML
       FROM ReportServer.dbo.Catalog C
    
    
    

    The above mentioned 2 queries parse the XML data in content column using Xquery.

    Refer to the below link for Awesome series on Xquery by Jacob Sebestian

    http://beyondrelational.com/blogs/jacob/archive/2008/06/26/xquery-labs-a-collection-of-xquery-sample-scripts.aspx

     

    Thursday, May 26, 2011 6:25 AM
  • These queries have helped me a lot. Thanks a lot. But with the above information, I should also extract the table name. It would be great if you can help me in getting table name.

    Thanks in advance.

    Monday, April 28, 2014 11:33 AM
  • The table name is contained in the <CommandText> element.

    I am using three queries to get this, one to get the Shared Data Sources, one to get the local Data Source, and then one to get the Data Set from the RDL. From there, you can parse the SQL query.

    DECLARE @rsDataSource TABLE
    (
    	[Name] [varchar](150) NULL,
    	[ConnectString] [varchar](255) NULL,
    	[Extension] [varchar](50) NULL,
    	[CredentialRetrieval] [varchar](50) NULL,
    	[WindowsCredentials] [bit] NULL,
    	[ImpersonateUser] [bit] NULL,
    	[Enabled] [bit] NULL,
    	[Server] [varchar](25) NULL,
    	[InitialCatalog] [varchar](25) NULL,
    	[LoadTS] [smalldatetime] NULL
    );
    
    DECLARE @rsReportDataSource TABLE 
    (
    	[ItemId] [char](36) NULL,
    	[Name] [varchar](255) NULL,
    	[DataSourceReference] [varchar](250) NULL,
    	[DataProvider] [varchar](250) NULL,
    	[ConnectString] [varchar](250) NULL,
    	[Server] [varchar](50) NULL,
    	[InitialCatalog] [varchar](50) NULL,
    	[LoadTS] [smalldatetime] NULL
    );
    
    DECLARE @rsDataset TABLE 
    (
    	[ItemId] [char](36) NOT NULL,
    	[Name] [varchar](255) NULL,
    	[DataSourceName] [varchar](255) NULL,
    	[CommandType] [varchar](255) NULL,
    	[Commandtext] [varchar](max) NULL,
    	[QueryParameter] [varchar](255) NULL,
    	[QueryValue] [varchar](255) NULL,
    	[LoadTS] [smalldatetime] NULL
    );
    
    
    --**********************************************************************************************************
    -- Shared Data sources
    --**********************************************************************************************************
    
    WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource')
    
    INSERT @rsDataSource (Name, Extension, ConnectString, CredentialRetrieval, WindowsCredentials, ImpersonateUser, Enabled)
    
    SELECT	a.name,
    		Extension = sd.value('(Extension)[1]','VARCHAR(250)')
    		,ConnectString = sd.value('(ConnectString)[1]','VARCHAR(250)')
    		,CredentialRetrieval = sd.value('(CredentialRetrieval)[1]','VARCHAR(250)')
    		,WindowsCredentials = sd.value('(WindowsCredentials)[1]','VARCHAR(250)')
    		,ImpersonateUser = sd.value('(ImpersonateUser)[1]','VARCHAR(250)')
    		,Enabled = sd.value('(Enabled)[1]','VARCHAR(250)')
    
    FROM	(SELECT c.Name, 
    				reportXML = CONVERT(XML,CONVERT(VARBINARY(MAX), c.Content)) 
    		FROM	reportserver..catalog c
    		WHERE	c.Content is not null
    		AND c.Type = 5) a
    
    CROSS APPLY a.reportXML.nodes('/DataSourceDefinition') r(sd);
    
    UPDATE @rsDataSource SET 
    		[Server] = CASE WHEN LEN(Connectstring) > 0 AND LEFT(Connectstring,11) = 'Data Source' THEN substring(Connectstring, 1 + charindex('=', connectstring), charindex(';', connectstring) - charindex('=', connectstring) - 1) ELSE '' END
    		,InitialCatalog = CASE WHEN LEN(Connectstring) > 0 AND LEFT(Connectstring,11) = 'Data Source' THEN reverse(left(reverse(Connectstring), charindex('=', reverse(Connectstring)) - 1)) ELSE '' END;
    
    SELECT * FROM @rsDataSource;
    
    --**********************************************************************************************************
    -- Data Sources
    --**********************************************************************************************************
    
    WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition', 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS "rd" )
    
    INSERT @rsReportDataSource (ItemId, Name, DataSourceReference, DataProvider, ConnectString)
    
    SELECT	a.ItemId
    		,Name = x.value('(DataSources/DataSource/@Name)[1]', 'VARCHAR(250)')
    		,DataSouceRefernce = COALESCE(x.value('(DataSources/DataSource/DataSourceReference)[1]', 'VarChar(250)'), 'N/A')
    		,DataProvider = x.value('(DataSources/DataSource/ConnectionProperties/DataProvider)[1]','VARCHAR(250)')
    		,ConnectionString = x.value('(DataSources/DataSource/ConnectionProperties/ConnectString)[1]','VARCHAR(250)')
    		
     FROM (
    
    	SELECT	c.ItemId,
    			reportXML = CONVERT(XML,CONVERT(VARBINARY(MAX), c.Content))
    	FROM	reportserver..catalog c
    	WHERE	c.Content is not null
    	AND c.Type = 2
    	
    ) a
    
    CROSS APPLY a.reportXML.nodes('Report') d(x);
    
    ----------------------------------------
    
    WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition', 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd )
    
    INSERT @rsReportDataSource (ItemId, Name, DataSourceReference, DataProvider, ConnectString)
    
    SELECT	a.ItemId
    		,Name = x.value('(DataSources/DataSource/@Name)[1]', 'VARCHAR(250)')
    		,DataSouceRefernce = COALESCE(x.value('(DataSources/DataSource/DataSourceReference)[1]', 'VarChar(250)'), 'N/A')
    		,DataProvider = x.value('(DataSources/DataSource/ConnectionProperties/DataProvider)[1]','VARCHAR(250)')
    		,ConnectionString = x.value('(DataSources/DataSource/ConnectionProperties/ConnectString)[1]','VARCHAR(250)')
    		
     FROM (
    	SELECT	c.ItemId,
    			reportXML = CONVERT(XML,CONVERT(VARBINARY(MAX), c.Content))
    	FROM	reportserver..catalog c
    	WHERE	c.Content is not null
    	AND		c.Type = 2
    ) a
    
    CROSS APPLY a.reportXML.nodes('Report') d(x);
    
    UPDATE @rsReportDataSource SET 
    		[Server] = substring(Connectstring, 1 + charindex('=', connectstring), charindex(';', connectstring) - charindex('=', connectstring) - 1),
    		InitialCatalog = reverse(left(reverse(Connectstring), charindex('=', reverse(Connectstring)) - 1))
    FROM @rsReportDataSource
    WHERE LEN(ConnectString) > 0 
    and charindex(';', ConnectString) > 0;
    
    update @rsReportDataSource SET 
    	[ConnectString] = ds.ConnectString
    	,[Server] = ds.Server
    	,[InitialCatalog] = ds.InitialCatalog
    FROM @rsReportDataSource rds
    INNER JOIN @rsDataSource ds ON rds.DataSourceReference = ds.Name;
    
    update @rsReportDataSource SET 
    	Server = CASE WHEN Server IS NULL THEN 'N/A' ELSE Server END
    	,InitialCatalog = CASE WHEN InitialCatalog IS NULL THEN 'N/A' ELSE InitialCatalog END;
    
    SELECT * FROM @rsReportDataSource;
    
    --**********************************************************************************************************
    -- Data sets and data set params
    --**********************************************************************************************************
    
    WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition', 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd )
    
    INSERT @rsDataSet (ItemId, Name, DataSourceName, CommandType, Commandtext, QueryParameter, QueryValue)
    
    SELECT	a.ItemId
    		,Name		= x.value('(@Name)[1]', 'VARCHAR(250)')
    		,DataSourceName	= x.value('(Query/DataSourceName)[1]','VARCHAR(250)')
    		,CommandType		= x.value('(Query/CommandType)[1]','VARCHAR(250)')
    		,CommandText		= x.value('(Query/CommandText)[1]','VARCHAR(max)')
    		,QueryParameter = d.value('(@Name)[1]', 'VarChar(250)')
    		,QueryValue		= d.value('(Value)[1]', 'VarChar(250)')
    
     FROM (SELECT	c.ItemId,
    				reportXML = CONVERT(XML,CONVERT(VARBINARY(MAX), c.Content))
    	FROM	reportserver..Catalog c
    	WHERE	c.Content is not null
    	AND		c.Type = 2
    ) a
    CROSS APPLY a.reportXML.nodes('/Report/DataSets/DataSet') r(x)
    OUTER APPLY x.nodes('Query/QueryParameters/QueryParameter') param(d);
    
    ---------------------------------------
    
    WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition', 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd )
    
    INSERT @rsDataSet (ItemId, Name, DataSourceName, CommandType, Commandtext, QueryParameter, QueryValue)
    
    SELECT	a.ItemId
    		,Name		= x.value('(@Name)[1]', 'VARCHAR(250)')
    		,DataSourceName	= x.value('(Query/DataSourceName)[1]','VARCHAR(250)')
    		,CommandType		= x.value('(Query/CommandType)[1]','VARCHAR(250)')
    		,CommandText		= x.value('(Query/CommandText)[1]','VARCHAR(max)')
    		,QueryParameter = d.value('(@Name)[1]', 'VarChar(250)')
    		,QueryValue		= d.value('(Value)[1]', 'VarChar(250)')
    
    FROM (SELECT	c.ItemId,
    
    			reportXML = CONVERT(XML,CONVERT(VARBINARY(MAX), c.Content))
    	FROM	reportserver..Catalog c
    	WHERE	c.Content is not null
    	AND		c.Type = 2
    ) a
    CROSS APPLY a.reportXML.nodes('/Report/DataSets/DataSet') r(x)
    OUTER APPLY x.nodes('Query/QueryParameters/QueryParameter') param(d);
    
    SELECT * FROM @rsDataset;
    
    

    Wednesday, May 7, 2014 8:24 PM
  • Hi Sorna

    I get the below error when trying to run the “Get Datasource Information of specific report” script.

    Msg 9436, Level 16, State 1, Line 6
    XML parsing: line 12, character 618, end tag does not match start tag

    please help

    regards

    Wednesday, March 22, 2017 1:11 PM