Thursday 23 July 2009

Reading RDL Definitions directly from a ReportServer database

Just a quick post: I recently had to extract the report definitions (RDL) directly from a ReportServer database, as the ReprtServer installation was broken. This should be straightforward, but requires some work to convert the data to a readable format. This can be done with the following SQL query:

select convert(varchar(max), convert(varbinary(max), content))
from catalog
where content is not null

at

10 comments:

  1. Sarah Vessels4 August 2009 at 18:49

    Excellent, this is just what I needed!

    ReplyDelete
  2. Unknown19 November 2009 at 03:25

    your awesomeness is awesome!!!!!!!
    Thank you. Exactly what I required.

    ReplyDelete
  3. Zeox4 May 2011 at 15:24

    Works perferctly Thx

    ReplyDelete
  4. Neetu11 November 2011 at 11:42

    Hi, I have deployed the reports ussing SSRS. can i know where SSRS reports stores in report server. can i get a full path. so i can go forward. i know it will store in th catolog table. but i require full path of catalog.

    I am using rsscripter to automate the reports. but there i hv to give the outputpath. that has to be in SSRS reports path. where they r actually storing.

    Pls let me know the extact path. so i can go further.

    Thanks

    ReplyDelete
  5. netekim13 August 2012 at 12:59

    Many many thanks.

    ReplyDelete
  6. Chaim Stiefer7 March 2014 at 18:50

    var n = 1;
    for (i = 0; i < n; ++i)
    {
    Console.WriteLine("Thank you!");
    --i;
    }

    ReplyDelete
  7. Unknown4 June 2014 at 20:56

    I tried this, and it worked on a small RDL, but truncated on a larger file. Another issue: introduced additional/unreadable characters.

    Please help in fine tuning this code. Thanks very much.

    ReplyDelete
  8. Unknown23 January 2015 at 16:44

    My RDL is truncated at 980 lines.

    I am using SQL Server Management Studio 2014.

    Is there a workaround?

    ReplyDelete
  9. JP16 July 2015 at 17:07

    converting to XML will pull more characters into the MSSQL result (see Options, Query Results, SQL Server, Results to Grid)

    SELECT Name, convert (xml, convert (varbinary(max),[Content])) AS ReportRDL
    FROM [dbo].[Catalog] where TYPE =2

    ReplyDelete
  10. Anonymous4 March 2018 at 15:23

    To follow on from JP, the RDL will be shown by SSMS as an XML column. (It will be underlined.) Click the field and the RDL XML will be formatted.

    ReplyDelete