Downloading SSRS report from command line

rs Utility is used to download SSRS report from command line. rs.exe is normally found under program folder.

It requires a script to download file. Lets make the name of the script download.rss. It is vb.net and it should be in .rss extension file.

Public Sub Main()
  Dim format as string = "EXCELOPENXML"
  Dim fileName as String = "E:\DownloadReport\RfsReport\Major Projects for CSOC Reporting - "+Date.Now.ToString("MM-dd-yy")+".xlsx"
  Dim reportPath as String = "/RFS Reports/Major Projects - Singleview CSOC"

  'Delete file if exists
  If System.IO.File.Exists( fileName ) = True Then
System.IO.File.Delete( fileName )
  End If

  ' Prepare Render arguments
  Dim historyID as string = Nothing
  Dim deviceInfo as string = Nothing
  Dim extension as string = Nothing
  Dim encoding as string
  Dim mimeType as string
  Dim warnings() AS Warning = Nothing
  Dim streamIDs() as string = Nothing
  Dim results() as Byte

  rs.LoadReport(reportPath, historyID)

  results = rs.Render(format,  deviceInfo, extension, _
   mimeType, encoding,  warnings, streamIDs)

  ' Open a file stream and write out the report
  Dim stream  As FileStream = File.OpenWrite(fileName)
  stream.Write(results, 0, results.Length)
  stream.Close()
End Sub

Now run the following command

c:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\rs.exe -i c:\download.rss -s http://[Report server name]/Reportserver -e Exec2005'

SSRS exporing as Excel 97-2003 (.xls) and Excel 2012 (.xlsx)

Format should be excel, for downloading as .xlsx file format.

http://tocgjtim1pv.bns.bns/Reportserver?/RFS%20Reports/Major+Projects+-+Singleview&rs:Format=excel


Format should be EXCELOPENXML, for downloading as .xlsx file format.

http://localhost/Reportserver?/RFS%20Reports/Major+Projects+-+Singleview&rs:Format=EXCELOPENXML

DELETE ALL SUBSCRIPTIONS IN REPORTING SERVICES

DELETE FROM ReportSchedule GO DELETE FROM Subscriptions GO DELETE FROM Schedule GO

List all SSRS data souce with connection string

;WITH XMLNAMESPACES  -- XML namespace def must be the first in with clause.
    (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource'
            ,'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'
     AS rd)
,SDS AS
    (SELECT SDS.name AS SharedDsName
           ,SDS.[Path]
           ,CONVERT(xml, CONVERT(varbinary(max), content)) AS DEF
     FROM dbo.[Catalog] AS SDS
     WHERE SDS.Type = 5)     -- 5 = Shared Datasource

SELECT CON.[Path]
      ,CON.SharedDsName
      ,CON.ConnString
FROM
    (SELECT SDS.[Path]
           ,SDS.SharedDsName
           ,DSN.value('ConnectString[1]', 'varchar(150)') AS ConnString
     FROM SDS
          CROSS APPLY
          SDS.DEF.nodes('/DataSourceDefinition') AS R(DSN)
     ) AS CON
-- Optional filter:
-- WHERE CON.ConnString LIKE '%Initial Catalog%=%TFS%'
ORDER BY CON.[Path]
        ,CON.SharedDsName;

List of failed SSRS subscription jobs

1. Get all the failed job related to SSRS subscription
SELECT * FROM dbo.Subscriptions WHERE LastStatus LIKE 'Failure%'

2. Get all the failed job related to email subscription

SELECT r.ScheduleID,s.LastStatus,s.Description,c.Name,c.Path,s.LastRunTime FROM dbo.Subscriptions s
INNER JOIN dbo.Catalog c ON s.Report_OID = c.ItemID
INNER JOIN ReportSchedule r ON c.ItemID = r.ReportID
WHERE s.LastStatus like 'Failure%'
and s.Description like '%e-mail%'
order by s.LastRunTime desc


3. Get all the failed job realted to file write subscription

SELECT r.ScheduleID,s.LastStatus,s.Description,c.Name,c.Path,s.LastRunTime FROM dbo.Subscriptions s
INNER JOIN dbo.Catalog c ON s.Report_OID = c.ItemID
INNER JOIN ReportSchedule r ON c.ItemID = r.ReportID
WHERE s.LastStatus like 'Failure%'
and s.Description like '%Save%'
order by s.LastRunTime desc


4. Check the execution log

select * from ExecutionLog
select * from ExecutionLog2
select * from ExecutionLog3


5. Check the log file

C:\Program Files\Microsoft SQL Server\MSRS11.JTIME\Reporting Services\LogFiles\ReportServerService__MM_DD_YYYY_hh_mm_ss.log

Running SSRS Subscription job

1. First we need get the ScheduleID from the following query by report name.

SELECT r.ScheduleID,s.LastStatus,s.LastRunTime  FROM dbo.Subscriptions s
INNER JOIN dbo.Catalog c ON s.Report_OID = c.ItemID
INNER JOIN ReportSchedule r ON c.ItemID = r.ReportID
WHERE c.name='ReportName'

example:

SELECT r.ScheduleID,s.LastStatus,s.LastRunTime  FROM dbo.Subscriptions s

INNER JOIN dbo.Catalog c ON s.Report_OID = c.ItemID

INNER JOIN ReportSchedule r ON c.ItemID = r.ReportID

WHERE c.name='HSMTaskReport'

2. Then get the job id by ScheduleID (it actually job name). 

select * from msdb.dbo.sysjobs where name='ScheduleID'

example:

select * from msdb.dbo.sysjobs where name='FBC6126C-C038-41DD-B40D-1DC5EE446B39'

3. Then run the job with job id.

exec msdb..sp_start_job @job_id='JOB_ID'

example:

exec msdb..sp_start_job @job_id='3E3A3645-53B6-4577-9D17-941D4B6BFCFD'

Download all .rdl from SSRS server

1. BCP utility should be enabled first.

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

2. Change the @OutputPath variable and execute it.

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

List of all SSRS reports name and their datasource

SELECT
    DS.Name AS DatasourceName,
    C.Name AS DependentItemName,
    C.Path AS DependentItemPath
FROM
    ReportServer.dbo.Catalog AS C
        INNER JOIN
    ReportServer.dbo.Users AS CU
        ON C.CreatedByID = CU.UserID
        INNER JOIN
    ReportServer.dbo.Users AS MU
        ON C.ModifiedByID = MU.UserID
        LEFT OUTER JOIN
    ReportServer.dbo.SecData AS SD
        ON C.PolicyID = SD.PolicyID AND SD.AuthType = 1
        INNER JOIN
    ReportServer.dbo.DataSource AS DS
        ON C.ItemID = DS.ItemID
WHERE
    DS.Name IS NOT NULL
ORDER BY
    DS.Name;

Disable all the SSRS Reporting services' Subscription Jobs

1. List all reporting service subscription jobs.

USE ReportServer
SELECT     Schedule.ScheduleID AS SQLAgent_Job_Name, Subscriptions.Description AS sub_desc, Subscriptions.DeliveryExtension AS sub_delExt,
                      [Catalog].Name AS ReportName, [Catalog].Path AS ReportPath,'exec msdb.dbo.sp_update_job @job_name = '''+CAST(Schedule.ScheduleID AS VARCHAR(max))+''',@enabled = 0' AS sqlcmd
FROM         ReportSchedule INNER JOIN
                      Schedule ON ReportSchedule.ScheduleID = Schedule.ScheduleID INNER JOIN
                      Subscriptions ON ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID INNER JOIN
                      [Catalog] ON ReportSchedule.ReportID = [Catalog].ItemID AND Subscriptions.Report_OID = [Catalog].ItemID



2. Disable each jobs.

exec msdb.dbo.sp_update_job @job_name = 'A83AE658-E203-457B-83D3-5715EA64A47A',@enabled = 0