SSRS REPORTS SUBSCRIPTION SCHEDULES AND NAMES OF THE REPORTS

 The below query will gives you the SSRS subscription scheduled information from SQL Server end.

DECLARE @jobName VARCHAR(100)
SET @jobName='52B9C43E-748E-40B9-8FC9-A95369E2ED73'
SELECT jobs.date_created,JOBS.[NAME],jobschedule.next_run_date,
NextRunTime=stuff(stuff(right('00000' + cast(jobschedule.next_run_time as varchar),6),3,0,':'),6,0,':')
,schedules.[enabled] AS ScheduleEnable
from msdb.dbo.sysjobs jobs INNER JOIN msdb.dbo.sysjobschedules jobschedule
ON jobs.JOB_ID=jobschedule.JOB_ID
inner join msdb.dbo.sysschedules schedules
ON schedules.schedule_id=jobschedule.schedule_id
WHERE jobs.[name]=@jobName
The below query will tell you about which sql server agent job is running which report and its schedules
SELECT distinct
sj.[name] AS [Job Name],
rs.SubscriptionID,
c.[Name] AS [Report Name],
c.[Path]
FROM msdb..sysjobs AS sj
INNER JOIN ReportServer..ReportSchedule AS rs
ON sj.[name] = CAST(rs.ScheduleID AS NVARCHAR(128))
INNER JOIN ReportServer..Subscriptions AS su
ON rs.SubscriptionID = su.SubscriptionID
INNER JOIN ReportServer..[Catalog] c
ON su.Report_OID = c.ItemID

Comments

Popular posts from this blog

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

Pre login Handshake or Connection Timeout Period

Transparent Data Encryption(TDE) with Master Key and Certificate in SQL Server