SSRS Report Server does not provides visual option to download all reports at single shot. Report Manager does not support downloading all the report files (.rdl files) at single shot.That is why we have to face crucial situation while migrate report server to higher version.
They provided solution for that. By the help of Bulk Copy (BCP) we can able to achieve it.
For that we have to write few lines of SQL code.
Today we will achieve the same by BCP option.
To do that first we have to know some basic about report database. When we configure Reporting Services configuration manager then we have to create report database. This report database contain whole information about reports. If we connect this report server database to SSMS, we can see various tables inside it.
Here one of the most important table is dbo.Catalog. This table contain report information and report XML body. Below picture showing few column of dbo.Catalog.
Type is the one column of dbo.Catalog Table. Here we can see some number, each number has a specific indication.
1 = Folder
2 = Report
3 = Resources
4 = Linked Report
5 = Data Source
6 = Report Model
7 = Report Part
8 = Shared
To get all the report we have to pass type as 2.
Below is the SQL scripts to download all report RDL files
DECLARE @ReportDetails TABLE
(
Report_No int,
ItemId uniqueidentifier,
Report_name nvarchar(425),
Report_Path nvarchar(425),
Folder_Path nvarchar(425)
)
DECLARE @intFlag INT=1
/*To start with 1 in while loop*/
DECLARE @MaxReportCount INT
Insert into @ReportDetails(Report_No,ItemId,Report_name,Report_Path,Folder_Path)
select ROW_NUMBER() over (order by ItemId) as Report_No,ItemId,Name,[Path],replace(SUBSTRING(PATH,1,LEN(PATH)-(LEN(Name)+1)),'/','\') as Folder_Path
from [dbo].[Catalog]
where [Type]=2 /*Load all the
reports (Type=2 indicates) from catalog table to table variable*/
select @MaxReportCount = MAX(Report_No) from @ReportDetails /*Get
max count to pass as highest value in while loop*/
WHILE (@intFlag <= @MaxReportCount)
/*Declare loop to generate rdl file of all
reports one by one*/
BEGIN
Declare @FolderContainer AS NVARCHAR(200)
declare @cmd as varchar(MAX) /*Hold xml file for
report*/
declare @Reportqueryout as varchar
(240) /*Hold
report output path*/
declare @DynamicQuery AS NVARCHAR(MAX) /*Hold xp_cmdshell
string for generating RDL
file*/
Select @FolderContainer='D:\ReportDetails'+Folder_Path from @ReportDetails where Report_No=@intFlag
Select @cmd = CONVERT(VARCHAR(MAX),
CASE
WHEN LEFT(C.Content,3) = 0xEFBBBF THEN STUFF(C.Content,1,3,'''')
ELSE C.Content END)
FROM
[ReportServerPDReport].[dbo].[Catalog] CL
CROSS APPLY (SELECT CONVERT(VARBINARY(MAX),CL.Content) Content) C WHERE
CL.ItemID =
(Select ItemId from @ReportDetails
where Report_No=@intFlag)
select * into ##Temp from (select @cmd temp) a /*store XML file to temp table*/
------------------------------------
EXEC master.dbo.xp_create_subdir @FolderContainer
------------------------------------
select @Reportqueryout='"'+@FolderContainer+'\'+Report_name+'.rdl"'
from @ReportDetails
where
Report_No=@intFlag /*Generate output path */
select @DynamicQuery='EXEC master..xp_cmdshell ''bcp " '
+ 'Select
Temp from ##Temp" QUERYOUT '
+ @Reportqueryout
+ '
-c -S Localhost\reportinstance -T''' /* Here
Localhost\reportinstance is the SQL
server name with instance*/
EXEC SP_EXECUTESQL @DynamicQuery
Drop table ##Temp
SET @intFlag = @intFlag + 1 /*By increasing this
go to or fetch next report*/
END
Steps to understand this
1. Open ReportServer database in SSMS (SQL Server management Studio).
2. Select all data from dbo.catalog table where type=2 (2 indicates reports only)
3. Create table variable named @ReportDetails to store few columns form dbo.catalog table.
4. Create 2 more variable named @intFlag and @MaxReportCount, next assign values.
5. Run while loop from 1 to @MaxReportCount, and convert content to XML file.
6. Push the XML file to ##Temp table.
7. Give path for export RDL file.
8. Create proper syntax of xp_cmdshell and store it into @DynamicQuery variable.
9. Now execute the @DynamicQuery variable.
In case of more than 500 reports in report server, we can use one more way to avoid System.OutOfMemoryException" exception
DECLARE @intFlag INT=1
/*To start with 1 in while loop*/
DECLARE @MaxReportCount INT
Select ROW_NUMBER() over (order by ItemId) as Report_No,ItemId,Name as Report_name,[Path] as Report_Path,replace(SUBSTRING(PATH,1,LEN(PATH)-(LEN(Name)+1)),'/','\') as Folder_Path
into #ReportDetails
from [dbo].[Catalog]
where [Type]=2 /*Load all the
reports (Type=2 indicates) from catalog table to table variable*/
select @MaxReportCount = MAX(Report_No) from #ReportDetails /*Get
max count to pass as highest value in while loop*/
Declare @FolderContainer AS NVARCHAR(200)
declare @cmd as varchar(MAX) /*Hold xml file for
report*/
declare @Reportqueryout as varchar
(240) /*Hold
report output path*/
declare @DynamicQuery AS NVARCHAR(MAX) /*Hold xp_cmdshell
string for generating RDL file*/
WHILE (@intFlag <= @MaxReportCount)
/*Declare loop to generate rdl file of all
reports one by one*/
BEGIN
Select @FolderContainer='D:\ReportDetails'+Folder_Path from #ReportDetails where Report_No=@intFlag
Select @cmd = CONVERT(VARCHAR(MAX),
CASE
WHEN LEFT(C.Content,3) = 0xEFBBBF THEN STUFF(C.Content,1,3,'''')
ELSE C.Content END)
FROM
[ReportServerPDReport].[dbo].[Catalog] CL
CROSS APPLY (SELECT CONVERT(VARBINARY(MAX),CL.Content) Content) C WHERE
CL.ItemID =
(Select ItemId from #ReportDetails
where Report_No=@intFlag)
select * into #Temp from (select @cmd temp) a /*store XML file to temp table*/
------------------------------------
EXEC master.dbo.xp_create_subdir @FolderContainer
------------------------------------
select @Reportqueryout= '"'+@FolderContainer+'\'+Report_name+'.rdl"'
from #ReportDetails
where
Report_No=@intFlag /*Generate output path */
select @DynamicQuery='EXEC master..xp_cmdshell ''bcp " '
+ 'Select
Temp from #Temp" QUERYOUT '
+ @Reportqueryout
+ '
-c -S Localhost\reportinstance -T''' /* Here
Localhost\reportinstance is the SQL
server name with instance*/
EXEC SP_EXECUTESQL @DynamicQuery
Drop table #Temp
set @FolderContainer=''
set @cmd=''
set @Reportqueryout=''
set @DynamicQuery=''
SET @intFlag = @intFlag + 1 /*By increasing this
go to or fetch next report*/
END
