locked
Reconstructing RDL from the ReportServer database RRS feed

  • Question

  • In my shop we hired a contractor who build our SQL Server 2005 Reporting Services reports on his laptop. After a tiff between him and our manager, he left with the laptop and we no longer have the source code for these reports. We have backups, but not the latest.

     

    These reports ARE deployed to our production server, but I need to know if it's possible to reconstruct the RDL for the reports from the database contents. I understand that the report contents are encrypted and I'm guessing the RDL is in the Catalog.Content column.

     

    Is there a way to recover the RDL for deployed reports or do we have to resort to the legal system?

    Thursday, April 5, 2007 7:33 PM

Answers

  • Sorry to hear about your plight.  You can open SQL Server Management Studio to connect to your report server.  In there, find your report files, right-click on them and say Edit Report...This will save the .rdl file to a location that you choose.

     

    Hope this helps.

    Thursday, April 5, 2007 8:01 PM
  • Hello Charlie,

     

    Here's a query you can use to get the XML (for the RPT file) from the database, but you may have some issues with the length of the field.

     

    select convert(varchar(max), convert(varbinary(max), content))

    from reportserver.dbo.catalog

    where content is not null

     

    Another way, is to get them straight from Report Manager (http://ServerName/Reports).  Drill down to one of your reports and go to the Properties tab.  From here, there is an Edit link.  Click on this and it will ask you to download the rdl file.

     

    Hope this helps.

     

    Jarret

    Thursday, April 5, 2007 8:08 PM

All replies

  • Sorry to hear about your plight.  You can open SQL Server Management Studio to connect to your report server.  In there, find your report files, right-click on them and say Edit Report...This will save the .rdl file to a location that you choose.

     

    Hope this helps.

    Thursday, April 5, 2007 8:01 PM
  • Hello Charlie,

     

    Here's a query you can use to get the XML (for the RPT file) from the database, but you may have some issues with the length of the field.

     

    select convert(varchar(max), convert(varbinary(max), content))

    from reportserver.dbo.catalog

    where content is not null

     

    Another way, is to get them straight from Report Manager (http://ServerName/Reports).  Drill down to one of your reports and go to the Properties tab.  From here, there is an Edit link.  Click on this and it will ask you to download the rdl file.

     

    Hope this helps.

     

    Jarret

    Thursday, April 5, 2007 8:08 PM
  • Thanks guyinkalamazoo3. This is a really good and simple idea!
    Thursday, April 5, 2007 8:43 PM
  • Thanks, Jarret. This is a great idea!

    Charlie

    Thursday, April 5, 2007 8:47 PM
  • Here's a quick way to bcp out the XML to files:

        DECLARE @dir nvarchar(425)
        DECLARE @name nvarchar(425)
        DECLARE @mdCommand varchar(100)
        DECLARE @bcpCommand varchar(1000)

        DECLARE catalog_cursor CURSOR FOR
        SELECT substring(path, 2, CHARINDEX('/', path, CHARINDEX('/', path)+1 ) - 2) as dir, name as name
        FROM ReportServer.dbo.Catalog
        where content is not null and type = 2 and CHARINDEX('/', path, CHARINDEX('/', path)+1 ) - 2 > 0

        OPEN catalog_cursor 
        FETCH NEXT FROM catalog_cursor INTO @dir, @name

        WHILE @@FETCH_STATUS = 0 
        BEGIN
            SET @mdCommand = 'md "C:/temp/' + @dir + '"'
            SET @bcpCommand = 'bcp "SELECT convert(varchar(max), convert(varbinary(max), content)) from reportserver.dbo.catalog'
            SET @bcpCommand = @bcpCommand + ' where type = 2 and name = ''' + @name + ''' " queryout'
            SET @bcpCommand = @bcpCommand + ' "C:/temp/' + @dir + '/' + @name + '.rdl" ' + '-T -c -t,'
            EXEC master..xp_cmdshell @mdCommand
            EXEC master..xp_cmdshell @bcpCommand
            FETCH NEXT FROM catalog_cursor INTO @dir, @name
        END 

        CLOSE catalog_cursor 
        DEALLOCATE catalog_cursor

    • Proposed as answer by rrozema Wednesday, July 26, 2017 9:32 AM
    Tuesday, April 19, 2011 9:18 PM
  • This is what worked for me.

    SELECT CAST(content as XML) FROM Catalog WHERE Name = 'foo'

    Thursday, June 27, 2019 8:33 PM
  • The following tools are available for accomplishing this type of task if the ReportServer  and ReportServerTempDB databases are intact:

    1. Download and use Reporting Services Migration Tool from Microsoft Downloads site

    2. Download and use ReportSync from https://code.google.com/archive/p/reportsync/

    ReportSync especially can solve this problem.


    Tuesday, August 27, 2019 3:24 PM