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!

97 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
    2. Hi,
      I had the same problem.
      Because command which is produced by script has been broken by two (in my case) things:
      1. command itself was too long, so I split it by extracting subfolders
      2. code was broken by apostrophe ' in the name of the report (remove all from reports name or add one more in the command and command should be ok)

      I came to these conclusions analyzing code using 'select @TSQL' rather than execute procedure.

      I hope this explanation will help someone.

      PF

      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
  21. Hi Vinay,

    This script is awesome... I have got out my reports but is there anyway it can include other resources? I have images that have been uploaded is it possible to get these out as well?

    Thanks
    Allan

    ReplyDelete
  22. Hello Allan,

    Good to know that the script has help you too.

    For images, I will have to look into this & only then could revert. But yes, there should be a way.

    Thanks!

    ReplyDelete
  23. awesome script, what a life saver.

    ReplyDelete
  24. Hi Script failing with below error can you please help me ou

    SQLState = S1000, NativeError = 0
    Error = [Microsoft][SQL Server Native Client 11.0]Unable to open BCP host data-file
    NULL

    ReplyDelete
    Replies
    1. Hello,

      Please ensure that the path/filename is correct (check your typing / spelling)
      Also, make sure that bcp.exe can access the file/path from it's context.

      Hope, this helps!

      Delete
  25. i got the the error

    Error = [Microsoft][ODBC Driver 11 for SQL Server]Unable to open BCP host data-file

    ReplyDelete
    Replies
    1. i got this error, if the mentioned path is not correct! Also, as mentioned above, use '/' in the path.

      Delete
  26. Hello Dilip,

    Could you please ensure that the user under which the BCP utility is running, is having appropriate permission at the file location in referred in the query.

    ReplyDelete
  27. Hi Vinay,
    Thanks a ton for this script! It worked like magic for my distributed reports to various folders!

    ReplyDelete
  28. Run the script as is but no rdl files appeared in the folder that I had created :(

    ReplyDelete
    Replies
    1. Hello Darren,

      Did you encounter any error or exception?

      Delete
  29. Hi Vinay,

    Thanks for the nice script.but unfortunately I am facing below errors. Could you please help me out?

    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

    Thanks
    Atul

    ReplyDelete
    Replies
    1. Hello Atul,

      This is a common error which we get when the connection to the SQL Server could not be made.

      So, please check you connection to the SQL Server & ensure you get through it. Once successfully connected, you will be able to run the script.

      Please feel free to let me know in case you face any other issues.

      Delete
    2. Thanks Vinay.
      I got the issue, Actually I was missing -S [Servername\Instancename]

      Again Thank you so much for prompt response and nice script.


      Thanks
      Atul

      Delete
    3. Hello ,
      got the issue, Actually I was missing -S [Servername\Instancename]

      Where did u add this ?

      Delete
    4. got same issue, where did you add/?

      Delete
  30. I got the solution for my problem actaully I was missing the -S Servername\Instance. Now after adding Server details. it Works for me.
    Thanks you so much for the nice script.

    Thanks
    Atul

    ReplyDelete
  31. The query ran successfully with a bunch of output message saying "(7 row(s) affected)". However, no report was downloaded to my out put path


    DECLARE @OutputPath AS VARCHAR(500);
    SET @OutputPath = 'C:\Reports\';

    Could you please help? Thank you.

    ReplyDelete
  32. Hello San,

    If you can share some more information on this, it will help me in getting onto the root cause of the issue you are facing.

    Hence, if possible please share some more information.

    Thanks!

    ReplyDelete
  33. I tried it as well, created the folder locally where SSMS is running:
    DECLARE @OutputPath AS VARCHAR(500) = 'C:\Tools\'

    Got this message (with 1 row effected)since I tried testing on a single report first:
    Command(s) completed successfully.

    but nothing showed up in local location or on Server location that is hosting the reportsever db. Any additional thoughts on this issue?

    ReplyDelete
    Replies
    1. Figured out the issue, mentioning it here as well incase it maybe helpful for others. I was running in SSMS on my local box and I kept getting the following error:

      SQLState = S1000, NativeError = 0
      Error = [Microsoft][SQL Server Native Client 11.0]Unable to open BCP host data-file
      NULL

      Then I logged onto SSMS in the server that is hosting reporting service, then it worked out. So folks seeing this issue and have their report paths correct, try running the query directly in the server hosting the reportserver db.

      And thanks a bunch Vinay for this query. If you already have something that can also go remove the reports from report server that would be a fantastic post as well.

      In the words of Vinay, Happy Reporting!!!

      Delete
    2. Good to see that you have figured out a solution for the issue you were facing & yes, thanks a lot for the suggestion for writing a blog on removing the rdl files @ a go.

      stay connected...I will be writing on it soon!

      Delete
    3. Any chance you got around to this, Vinay? Sorry if being impatient but could use the help.

      Delete
    4. Are you awaiting for the TSQL to delete the rdl files?

      Delete
    5. Just the sql to bulk remove reports from the report server, is it as simple as deleting rows from the catalog table? as well as from subscriptions, schedule tables for the associated subscriptions etc?

      Delete
    6. Yes, it is to be deleted from the Catalog table & DataSource table, that is it!

      For subscriptions, if they are configured only then they need to be addressed.

      Delete
    7. Thanks Vinay. I'll give that a shot and report back, in case others are also interested.

      Delete
    8. Hello,

      Just wanted to share an update that finally I got a chance to write about deleting the rdl files. Here is the link you can follow & share your comments - http://www.inkeysolutions.com/blogs/ssrs-delete-specific-rdl-files-from-report-server-in-one-go/

      Thanks!

      Delete
  34. Hi vinay ,
    I am using below script ,when I run script executes without any error and says 'Command(s) completed successfully.'. but when I go and check folder there are no files down loaded.

    --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)
    set @FilterReportPath = 'http://ssrs.cmltd.net.au/Reports/Pages/Report.aspx?ItemPath=%2fBI+Support%2fAHCStoreList'

    --Replace NULL with the keyword matching the Report File Name,
    --if any specific reports are to be downloaded
    DECLARE @FilterReportName AS VARCHAR(500)
    set @FilterReportName = NULL

    --Replace this path with the Server Location where you want the
    --reports to be downloaded..
    DECLARE @OutputPath AS VARCHAR(500) = 'D:\LocalApp\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 ' +
    ' [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
    [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


    ReplyDelete
  35. Hello Srinivas,

    Try setting the report path as -
    set @FilterReportPath = '/BI Support/AHCStoreList/'

    Hope, this helps!

    ReplyDelete
  36. Error
    Msg 208, Level 16, State 1, Line 38
    Invalid object name 'ReportServer.dbo.Catalog'.

    ReplyDelete
  37. Hello Anant,

    Please change "ReportServer" to the actual name of your SSRS Reporting Database. It seems, your SSRS configuration is not using the default report server db name.

    Hope, this helps!

    ReplyDelete
  38. Hi, Could you help me to solve this? I always get an error - "Must declare the scalar variable "@OutputPath"." I already declared @OutputPath, Could you please check the script for me? Appreciate 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

    --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)
    set @FilterReportPath = NULL

    --Replace NULL with the keyword matching the Report File Name,
    --if any specific reports are to be downloaded
    DECLARE @FilterReportName AS VARCHAR(500)
    set @FilterReportName = NULL

    --Replace this path with the Server Location where you want the
    --reports to be downloaded..
    DECLARE @OutputPath AS VARCHAR(500)
    set @OutputPath = 'C:\Report Download'

    --Used to prepare the dynamic query
    DECLARE @TSQL AS NVARCHAR(MAX)

    --Reset the OutputPath separator.
    SET @OutputPath = REPLACE(@OutputPath,'\','/')

    select @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
    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

    ReplyDelete
    Replies
    1. Hello,

      I could successfully execute the above TSQL with no error related to @OutputPath.

      However, I noticed that you need to change the value being set for this variable as under -
      --Replace this path with the Server Location where you want the
      --reports to be downloaded..
      DECLARE @OutputPath AS VARCHAR(500)
      set @OutputPath = 'C:\Report Download\'

      NOTICE the ending "\"

      Rest all seems okay to me.

      Please try again with the suggested change & update your findings.

      Thanks!

      Delete
    2. Thank you very much.
      I typed the code instead of copy&past, and it's working. Confused about it. Anyway, it's working.
      Appreciate it!!

      Delete
    3. Hi vinay god,
      Could you help me to solve this?
      My SSRS report name is Chinese name,
      it must use N'' with SQL command on SQL Server (I don't know how to do, or have the other solutions.)
      So, I have tried successfully for English name.
      I guess i must adjust BCP command. Please help me, thank you.

      Delete
  39. God, can you help me?
    My report name is Chinese name,
    How could I do?

    ReplyDelete
    Replies
    1. Hello,

      Please try changing the variables to NVARCHAR as shown below -

      --Replace NULL with keywords of the ReportManager's Report Path,
      --if reports from any specific path are to be downloaded
      DECLARE @FilterReportPath AS NVARCHAR(500) = NULL

      --Replace NULL with the keyword matching the Report File Name,
      --if any specific reports are to be downloaded
      DECLARE @FilterReportName AS NVARCHAR(500) = NULL

      --Replace this path with the Server Location where you want the
      --reports to be downloaded..
      DECLARE @OutputPath AS NVARCHAR(500) = 'D:\Reports\Download\'

      Hope, this helps!

      Please do leave a comment on your findings.

      Thanks!

      Delete
  40. Excellent time saver!

    ReplyDelete
  41. how long does the download take? I have +- 500 files

    ReplyDelete
    Replies
    1. Hello,

      Honestly speaking I have never focused on the timing/performance. However, in general the download is pretty quick and possibly that's the reason why I never thought about performance for this activity.

      I would suggest just give it a try & then share your findings.

      Thanks!

      Delete
  42. Hello vinay, love the script, works great. However, I have sub folders and when I use @FilterReprotPath='Reports' I get all reports even those that are in sub folders like 'Reports/More Reports' The issue is I may have a report with the same name in the sub folder. Can I get it to only look at a specific folder and not the sub folders?
    Thank you,
    Justin

    ReplyDelete
    Replies
    1. Hello Justin,

      Good to know that you have found this script helpful. Actually, the primary focus of this script is to download all the rdl files starting from the root folder - recursively.

      However, I do understand your concern. And to handle this, you just need to make a small change in the TSQL. Please change the following statement

      AND '/' + CL.[Path] + '/' LIKE COALESCE('%/%' + @FilterReportPath + '%/%', '/' + CL.[Path] + '/')

      to

      AND '/' + CL.[Path] + '/' = COALESCE('/' + @FilterReportPath + '/', '/' + CL.[Path] + '/')

      Hope, this helps!

      P.S. I have not tested this. Please feel free to let me know if this does not work for you.

      Delete
  43. Thank you very much. I'll test ASAP and report back.

    ReplyDelete
  44. Hi vinay,

    I appreciate the effort but this did not seem to work. I get Command(s) completed successfully but no results in folder.

    Thank you,
    Justin

    ReplyDelete
    Replies
    1. Justin,

      Can you please share your T-SQL for the above statement if possible? I strongly feel that it would be just an extra '/' character in the path which is creating the issue.

      Actually, the 'Path' in the Catalog table stores the path with a '/' character prefixed & no trailing '/'. If we just take care of this in the T-SQl, it will work as you will need to use the '=' operator instead of 'LIKE'.

      Hope, this helps!

      Delete
  45. Hi vinay,

    Thanks again for your help but I found a powershell tool that does the trick.
    https://github.com/Microsoft/ReportingServicesTools

    Take care,
    Justin

    ReplyDelete
  46. is it possible to download all the reports with folder structure?

    ReplyDelete
  47. Hi Vinay,

    Thanks for the nice script.but unfortunately I am facing below errors. Could you please help me out?

    Msg 207, Level 16, State 1, Line 54
    Invalid column name 'Type'.
    Msg 207, Level 16, State 1, Line 55
    Invalid column name 'Path'.
    Msg 207, Level 16, State 1, Line 55
    Invalid column name 'Path'.
    Msg 207, Level 16, State 1, Line 56
    Invalid column name 'Name'.
    Msg 207, Level 16, State 1, Line 56
    Invalid column name 'Name'.
    Msg 207, Level 16, State 1, Line 50
    Invalid column name 'ItemID'.
    Msg 207, Level 16, State 1, Line 50
    Invalid column name 'Name'.


    Thanks
    Daniel

    ReplyDelete
    Replies
    1. Hello Daniel,

      I guess the [ReportServer].[dbo].[Catalog] table of the SQL Server version which you are using is having a different structure than the one this script is expecting.

      The error message says that it could not find the columns - Type, Path, Name & ItemId in the table - [ReportServer].[dbo].[Catalog]

      I hope, this will give you a good direction to fix the error.

      And yes, thanks a lot for using this script.

      Delete
    2. Hi Vinay,

      It was a good direction and I was able to fix the problem.

      Now, that I have the RDL file, I will modify it and I will have to put it back in the database.

      Is there a script for something like this, or can you help me with a few words to upload the RDL file into the database?

      Thank you a lot.
      Daniel

      Delete
    3. Hello Daniel,

      Deploying Rdl files in bulk was never a problem. You can use tools like SSDT to create a report project and then deploy them in one go.

      Delete
  48. This script is amazing thanks! I'm having one issue though. For me it only works when I put a specific report name via
    DECLARE @FilterReportName AS VARCHAR(500) = 'test'. If I leave it null it just throws me various errors.

    Could you please help? Thanks.

    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near 's'.
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near '705'.
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near '78598004'.
    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 168, Level 15, State 1, Line 1
    The floating point value '4E910162' is out of the range of computer representation (8 bytes).
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near '4E910162'.
    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 '93'.
    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 '696'.
    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 'DC1D8B00'.

    ReplyDelete
    Replies
    1. Hello, please scroll up & have a look at the comment dated 25th March 2015. I guess both the issues are same & check on the replies to it. I hope, it will solve your issues. If not, please do write back.

      Delete
  49. Hi Admin Vinay,
    I command runs successfully even create folders subfolders but .rdl files not downloading please help me out of it

    ReplyDelete
    Replies
    1. Hello Farrukh, thanks a lot for using this script but I am sorry to hear that it did not work for you. Can you please share more details on this. Say, any error message if you are getting. I will be in a much better position to help you with more details in hand.

      Delete


    2. DECLARE @FilterReportPath AS VARCHAR(500) = ''
      DECLARE @FilterReportName AS VARCHAR(500) =''
      DECLARE @OutputPath AS VARCHAR(500) = 'C:\Reports\Download';
      SELECT Path, @OutputPath + REPLACE(REPLACE(REVERSE(SUBSTRING(REVERSE(Path), CHARINDEX('/',REVERSE(Path)), LEN(Path) - CHARINDEX('/',REVERSE(Path)) + 1)), '/','\'), '\\\\','\\') FolderPath FROM [SERVER].[ReportServer].[dbo].Catalog WITH(NOLOCK)
      DECLARE @TSQL AS NVARCHAR(MAX);
      DECLARE @DataPath NVARCHAR(500);
      DECLARE @DirTree TABLE
      (
      subdirectory NVARCHAR(255) ,
      depth INT
      );

      select @OutputPath = REPLACE(@OutputPath, '/', '\');

      IF LTRIM(RTRIM(ISNULL(@OutputPath, ''))) = ''
      BEGIN
      SELECT 'Invalid Output Path';
      END;
      ELSE
      BEGIN
      DECLARE @itemID UNIQUEIDENTIFIER = NULL;
      DECLARE @currentFolderPath NVARCHAR(2000);
      IF ( OBJECT_ID('tempdb..#catalog') IS NOT NULL )
      DROP TABLE #catalog;
      SELECT * ,
      0 AS Processed
      INTO #catalog
      FROM [SERVER].[ReportServer].[dbo].[Catalog] CL
      WHERE CL.[Type] = 2 --Report-- = 5 -- DataSource
      AND '/' + CL.[Path] + '/' LIKE COALESCE('%/%'+ @FilterReportPath+ '%/%','/' + CL.[Path] + '/')
      AND CL.Name LIKE COALESCE('%' + @FilterReportName + '%',CL.Name);
      WHILE EXISTS ( SELECT 1FROM #catalogWHERE Processed = 0 )
      BEGIN SELECT TOP 1 @itemID = ItemID ,@currentFolderPath = @OutputPath
      + REPLACE(REPLACE(REVERSE(SUBSTRING(REVERSE(Path),
      CHARINDEX('/',
      REVERSE(Path)),
      LEN(Path)
      - CHARINDEX('/',
      REVERSE(Path))
      + 1)), '/', '\'),
      '\\\\', '\\')
      FROM #catalog
      WHERE Processed = 0;

      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 '
      + ' [SERVER].[ReportServer].[dbo].[Catalog] CL '
      + ' CROSS APPLY (SELECT CONVERT(VARBINARY(MAX),CL.Content) Content) C '
      + ' WHERE ' + ' CL.ItemID = '''''
      + CONVERT(VARCHAR(MAX), CL.ItemID)
      + ''''' " queryout "'
      + @currentFolderPath + ''
      + CL.Name + '.rdl" '
      + '-T -c -x'''
      FROM #catalog CL
      WHERE CL.ItemID = @itemID
      FOR
      XML PATH('')
      ), 1, 1, '');


      DELETE FROM @DirTree;
      INSERT INTO @DirTree
      ( subdirectory ,
      depth
      )

      EXEC master.sys.xp_dirtree @OutputPath;

      SELECT @currentFolderPath;
      IF NOT EXISTS ( SELECT 1
      FROM @DirTree
      WHERE subdirectory = @currentFolderPath )
      EXEC master.dbo.xp_create_subdir @currentFolderPath;

      -- SELECT @TSQL;

      --Execute the Dynamic Query
      EXEC sp_executesql @TSQL;

      UPDATE #catalog
      SET Processed = 1
      WHERE ItemID = @itemID;

      END;
      END;

      Delete
  50. Hi Vinay,
    The command successfully executed but output is null and in folder no .rdl found

    ReplyDelete
    Replies
    1. However, I noticed that you need to change the value being set for this variable as under -
      --Replace this path with the Server Location where you want the
      --reports to be downloaded..
      DECLARE @OutputPath AS VARCHAR(500)
      set @OutputPath = 'C:\Reports\Download\'

      NOTICE the ending "\"

      Rest all seems okay to me.

      Please try again with the suggested change & update your findings.

      Thanks!

      Delete
    2. HI Expert Vinay,
      I changed the above as mentioned also given the full control shared to the folder Reports but no luck.
      DECLARE @OutputPath AS VARCHAR(500)
      set @OutputPath = 'C:\Reports\Download\'

      Delete
    3. Oops!

      If this is the case, the only option we have is to debug the script in your environment and check results are you getting at each stage.

      Please do the needful & share the update.

      Delete
    4. Hi Expert Vinay can you please connect via teams or anydesk portable to resolve a issue if you have a time my whatsapp 00923333551019

      Delete
    5. Hello Farrukh, I am sorry to say but supporting via screen sharing will not be possible for me. At the same time I am sure that if you can debug the script by yourself, you will certainly pick the issue and then I can help you to sort it out.

      Delete
  51. Vinay - thank you for the code - when i run this code i get a 0kb file that is empty and the output states: NULL / Starting copy... / NULL / 0 rows copied. / Network packet size (bytes): 4096 / Clock Time (ms.) Total :1 / NULL
    any ideas for me? Thanks, Brad

    ReplyDelete
  52. Hello Brad,

    Good to know that you found this code useful but seems like it's not working for you.

    The case which you have explained generally happens when incorrect path is set (this is the only case I have found so far). So, I would request you to please double check if you have the right path set for the variables.

    Hope, it works for you.

    ReplyDelete