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

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.

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

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.

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!

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