posted on Thursday, June 12, 2008 12:05 AM by thomasswilliams

Quick Tip: Retrieving Report Definitions from the Catalog Table in the ReportServer Database

The Reporting Services Catalog table in the ReportServer database contains the RDL XML in the Content column, serialised to binary format.

I had an issue recently where I needed to retrieve the RDL XML from the Content column from a backup of the ReportServer database using SQL Server 2005. Here's the code that did the job:

SELECT  [Name], 
        CAST(CAST([Content] AS VARBINARY(MAX)) AS XML) AS reportXML 
FROM    ReportServer_Backup.dbo.[Catalog] WITH (NOLOCK) 
WHERE   --get only reports, not folders or other resources 
        [Type] = 2

I would not recommend running this against the entire table. I've used the NOLOCK hint to avoid locking up the table, and also screened our non-reports by adding Type = 2 to the where clause.

I originally found this great little snippet by at the SQL Server and XML blog. Thanks Jacob! It came in so useful that I thought I'd write it down for later.

Tags: sql server, reporting services, database, xml

Comments