About This Blog

.net & SQL Samples, programming tips and tricks, performance tips, guidelines, and best practices

Wednesday, 7 August 2013

SSRS – Download all RDL files from Report Server in one go.

Introduction

People working on SSRS are well aware that “Report Manager” does not support downloading all the report files (.rdl files) at one go out-of-box. And I am sure that many of you might have come across this requirement some day or other. Hence, today I will share a simple handy script which would help you to download all the required report files at once.

Implementation

SSRS uses SQL Server to store it’s details as a backend and the Catalog table is used to store the report file in binary form. The below script simply pulls the report definition from the Catalog table & uses BCP utility to export the same at a pre-defined path as a .rdl file.

To use the BCP utility from TSQL, we need to execute  “xp_cmdshell” command; it is disabled by default. So, first you need to execute the below script to enable it -

-- Allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
 
-- Update the currently configured value for advanced options.
RECONFIGURE
GO
 
-- Enable xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
GO
 
-- Update the currently configured value for xp_cmdshell
RECONFIGURE
GO
 
-- Disallow further advanced options to be changed.
EXEC sp_configure 'show advanced options', 0
GO
 
-- Update the currently configured value for advanced options.
RECONFIGURE
GO

Once successfully executed, the below script with the required changes could be executed to download the files -



--Replace NULL with keywords of the ReportManager's Report Path, 
--if reports from any specific path are to be downloaded
DECLARE @FilterReportPath AS VARCHAR(500) = NULL 
 
--Replace NULL with the keyword matching the Report File Name,
--if any specific reports are to be downloaded
DECLARE @FilterReportName AS VARCHAR(500) = NULL
 
--Replace this path with the Server Location where you want the
--reports to be downloaded..
DECLARE @OutputPath AS VARCHAR(500) = 'D:\Reports\Download\'
 
--Used to prepare the dynamic query
DECLARE @TSQL AS NVARCHAR(MAX)
 
--Reset the OutputPath separator.
SET @OutputPath = REPLACE(@OutputPath,'\','/')
 
--Simple validation of OutputPath; this can be changed as per ones need.
IF LTRIM(RTRIM(ISNULL(@OutputPath,''))) = ''
BEGIN
  SELECT 'Invalid Output Path'
END
ELSE
BEGIN
   --Prepare the query for download.
   /*
   Please note the following points -
   1. The BCP command could be modified as per ones need. E.g. Providing UserName/Password, etc.
   2. Please update the SSRS Report Database name. Currently, it is set to default - [ReportServer]
   3. The BCP does not create missing Directories. So, additional logic could be implemented to handle that.
   4. SSRS stores the XML items (Report RDL and Data Source definitions) using the UTF-8 encoding. 
      It just so happens that UTF-8 Unicode strings do not NEED to have a BOM and in fact ideally would not have one. 
      However, you will see some report items in your SSRS that begin with a specific sequence of bytes (0xEFBBBF). 
      That sequence is the UTF-8 Byte Order Mark. It’s character representation is the following three characters, “”. 
      While it is supported, it can cause problems with the conversion to XML, so it is removed.
   */
   SET @TSQL = STUFF((SELECT
                      ';EXEC master..xp_cmdshell ''bcp " ' +
                      ' SELECT ' +
                      ' CONVERT(VARCHAR(MAX), ' +
                      '       CASE ' +
                      '         WHEN LEFT(C.Content,3) = 0xEFBBBF THEN STUFF(C.Content,1,3,'''''''') '+
                      '         ELSE C.Content '+
                      '       END) ' +
                      ' FROM ' +
                      ' [ReportServer].[dbo].[Catalog] CL ' +
                      ' CROSS APPLY (SELECT CONVERT(VARBINARY(MAX),CL.Content) Content) C ' +
                      ' WHERE ' +
                      ' CL.ItemID = ''''' + CONVERT(VARCHAR(MAX), CL.ItemID) + ''''' " queryout "' + @OutputPath + '' + CL.Name + '.rdl" ' + '-T -c -x'''
                    FROM
                      [ReportServer].[dbo].[Catalog] CL
                    WHERE
                      CL.[Type] = 2 --Report
                      AND '/' + CL.[Path] + '/' LIKE COALESCE('%/%' + @FilterReportPath + '%/%', '/' + CL.[Path] + '/')
                      AND CL.Name LIKE COALESCE('%' + @FilterReportName + '%', CL.Name)
                    FOR XML PATH('')), 1,1,'')
  
  --SELECT @TSQL
  
  --Execute the Dynamic Query
  EXEC SP_EXECUTESQL @TSQL
END

Conclusion


Hope, this helps & save a lot of your valuable time.


Happy Reporting!

30 comments:

  1. Your are AWESOME!!!! Saved me a ton of work.

    ReplyDelete
  2. Is the output path pointing to the physical report server machine?

    ReplyDelete
  3. Hello Atrapp,

    The download path will be of the DB Server; i.e. where the DB exists.

    ReplyDelete
  4. What value needs to be provided against NULL at DECLARE @FilterReportPath AS VARCHAR(500) = NULL

    ReplyDelete
    Replies
    1. @Prashant, you can provide the keywords matching the specific folder from where you want to download the reports.

      For example, your report server might have many other reports as well properly organized in some folder hierarchy and when you want to download rdl files of any specific folder, you just need to specify the complete name of the folder or the keywords matching the folder name and you are done.

      Delete
  5. You are genius!!!

    Thank you!

    jha

    ReplyDelete
  6. Hi there,
    Do we have any option to extract Linked reports from one server ? Its very important for me to extract report shortcuts from one server and deploy into another server. Appreciate your quick response on this matter.

    Regards
    Balaji Manickamchetty

    ReplyDelete
  7. @Balaji,
    I am sorry to say but I have not tested this for Linked reports.
    The above TSQL will just save the XML from the Tables to your physical disk.

    One will have to check how Linked Reports are saved inside the Tables & then develop the logic accordingly.

    ReplyDelete
  8. Can you give an example of:
    value:
    @FilterReportPath
    @FilterReportName


    ReplyDelete
    Replies
    1. --if reports from path having "\purchase\" in it are to be downloaded
      DECLARE @FilterReportPath AS VARCHAR(500) = '\purchase\'

      --if reports having word "sales" are to be downloaded
      DECLARE @FilterReportName AS VARCHAR(500) = 'sales'

      Hope, this helps!

      Delete
  9. Hi there,
    But when I do this result is NULL.

    How can I know where the physical path: @FilterReportPath?

    ReplyDelete
    Replies
    1. You can get the path of the report from Report Manager. Just browse to the required report and you can get the path.

      Delete
  10. Wow , excellent, thank you so much !

    ReplyDelete
  11. Hi there,
    This worked perfectly. As a follow up, do you know if deleting a report would simply require deleting rows from this table?

    Thanks!

    ReplyDelete
    Replies
    1. Hey, good to know that this worked for you too.

      As far as deleting goes, I haven't tried the way you have shared but yes will surely revert back after testing it thoroughly.

      Thanks!

      Delete
  12. Hi, Thank you for the code. Worked 100%. Anyone reading this, please create the output folder path first.

    ReplyDelete
  13. I am getting errors for SSRS 2005, gave the following values for report path and report name, both are valid,

    Msg 139, Level 15, State 1, Line 0
    Cannot assign a default value to a local variable.
    Msg 139, Level 15, State 1, Line 0
    Cannot assign a default value to a local variable.
    Msg 139, Level 15, State 1, Line 0
    Cannot assign a default value to a local variable.
    Msg 137, Level 15, State 2, Line 17
    Must declare the scalar variable "@OutputPath".
    Msg 137, Level 15, State 2, Line 20
    Must declare the scalar variable "@OutputPath".
    Msg 156, Level 15, State 1, Line 24
    Incorrect syntax near the keyword 'ELSE'.
    Msg 137, Level 15, State 2, Line 50
    Must declare the scalar variable "@OutputPath".

    ReplyDelete
    Replies
    1. Hello,

      Thanks for using this script.

      Please try separating the DECLARATION & INITIAL VALUE ASSIGNMENT as under -

      DECLARE @FilterReportPath AS VARCHAR(500)
      SET @FilterReportPath = NULL

      Hope, this helps!

      Delete
  14. Thank you very much Vinay, after the changes it worked fine, it saved me a lot of time, i download around 500+ reports.
    Following are the changes i did
    DECLARE @FilterReportPath AS VARCHAR(500)
    SET @FilterReportPath = NULL

    DECLARE @FilterReportName AS VARCHAR(500)
    SET @FilterReportName = NULL

    DECLARE @OutputPath AS VARCHAR(500)
    SET @OUTPUTPATH = 'S:\Old_Reports\Download\'

    ReplyDelete
    Replies
    1. Hey, Good to know this has helped you!

      Happy Reporting...!!

      Delete
  15. Thanks a lot. Really Helpful !

    ReplyDelete
  16. I received this error as below !! Please help me !!

    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near 's'.
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near 'B194C246'.
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near '1E'.
    Msg 132, Level 15, State 1, Line 1
    The label 'D' has already been declared. Label names must be unique within a query batch or stored procedure.
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near '0'.
    Msg 132, Level 15, State 1, Line 1
    The label 'D' has already been declared. Label names must be unique within a query batch or stored procedure.
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near '89'.
    Msg 132, Level 15, State 1, Line 1
    The label 'D' has already been declared. Label names must be unique within a query batch or stored procedure.
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near '52042E3'.
    Msg 132, Level 15, State 1, Line 1
    The label 'D' has already been declared. Label names must be unique within a query batch or stored procedure.
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near '10871'.
    Msg 132, Level 15, State 1, Line 1
    The label 'D' has already been declared. Label names must be unique within a query batch or stored procedure.
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near '408144'.
    Msg 132, Level 15, State 1, Line 1
    The label 'D' has already been declared. Label names must be unique within a query batch or stored procedure.

    ReplyDelete
    Replies
    1. Hello,
      It seems you are not executing the given TSQL in the way it should be.

      Could you please share your code which you are executing either here or in private mail?

      I can certainly help you once I have a look at the TSQL you are executing.

      Thanks!

      Delete
  17. Great work. Thank you for this awesome script.
    Is it a chance to repeat ReportServer directory sctructure?
    For example: i have report in "/stuff/", and script automatically create %server_path%/stuff folder and put report in it.

    ReplyDelete
    Replies
    1. Hello Artem,

      Absolutely! By making use of two undocumented extended stored procedures (master.sys.xp_dirtree and master.sys.xp_create_subdir) you can create folders from within your T-SQL code.

      Hope, this helps!

      Delete
  18. HI Vinay, Can I use this script for SQL 2008 SP3.

    ReplyDelete
  19. Thank you for sharing, this post saved me countless hours of work!

    ReplyDelete
  20. hi got below error while running the code
    output
    SQLState = 08001, NativeError = 2
    Error = [Microsoft][SQL Server Native Client 11.0]Named Pipes Provider: Could not open a connection to SQL Server [2].
    SQLState = 08001, NativeError = 2
    Error = [Microsoft][SQL Server Native Client 11.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is config
    ured to allow remote connections. For more information see SQL Server Books Online.
    SQLState = S1T00, NativeError = 0
    Error = [Microsoft][SQL Server Native Client 11.0]Login timeout expired
    NULL


    pls help

    ReplyDelete
    Replies
    1. There seems to be a connection problem with your SQL Server. Please fix that first & then execute the script.

      Thanks!

      Delete