Scheduling the SSRS Report from SP

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Reporting Services » Reporting Services » Scheduling the SSRS Report from SP

Scheduling the SSRS Report from SP

Rate Topic

Display Mode

Topic Options

Author

Message

Ajay Surendran

Posted Friday, November 16, 2012 3:49 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 1:29 AM
Points: 47, Visits: 87

How can we subscribe the SSRS report from a Stored Procedure

Post #1385587

Daniel Bowlin

Posted Friday, November 16, 2012 7:00 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 8:49 AM
Points: 2,672, Visits: 2,417

Not sure exactly what you mean, but assuming you want a subscription to fire off as the result of a stored procedure, or other command. You need to kinow the JobID of the report. Once you know that you run

@ReportJobID CHAR(36)
SET @ReportJobID = Your SQL Agent Job here.
EXEC msdb.dbo.sp_start_job @job_name = @ReportJobID

I use the query below to generate a lot of useful information about report subscriptions, in your case you want to pick out the value in the JobID column to feed to the statement above

SELECT cat.[Name] AS RptName
,U.UserName
,cat.[Path]
,res.ScheduleID AS JobID
,sub.SubscriptionID AS SubscriptionID
,sub.LastRuntime
,sub.LastStatus
,LEFT(CAST(sch.next_run_date AS CHAR(8)) , 4) + '-'
+ SUBSTRING(CAST(sch.next_run_date AS CHAR(8)) , 5 , 2) + '-'
+ RIGHT(CAST(sch.next_run_date AS CHAR(8)) , 2) + ' '
+ CASE WHEN LEN(CAST(sch.next_run_time AS VARCHAR(6))) = 5
THEN '0' + LEFT(CAST(sch.next_run_time AS VARCHAR(6)) , 1)
ELSE LEFT(CAST(sch.next_run_time AS VARCHAR(6)) , 2)
END + ':'
+ CASE WHEN LEN(CAST(sch.next_run_time AS VARCHAR(6))) = 5
THEN SUBSTRING(CAST(sch.next_run_time AS VARCHAR(6)) , 2 , 2)
ELSE SUBSTRING(CAST(sch.next_run_time AS VARCHAR(6)) , 3 , 2)
END + ':00.000' AS NextRunTime
,CASE WHEN job.[enabled] = 1 THEN 'Enabled'
ELSE 'Disabled'
END AS JobStatus
,sub.ModifiedDate
,sub.[Description]
,sub.EventType
,sub.[Parameters]
,sub.DeliveryExtension
,sub.Version
,substring(SUBSTRING(sub.ExtensionSettings,DATALENGTH('<ParameterValues><ParameterValue><Name>TO</Name><Value>')+1,DATALENGTH(sub.ExtensionSettings)),1,CHARINDEX('</value>',SUBSTRING(sub.ExtensionSettings,DATALENGTH('<ParameterValues><ParameterValue><Name>TO</Name><Value>')+1,DATALENGTH(sub.ExtensionSettings)))-1) AS Recipients
FROM
ALBSSRS01.dbo.Catalog AS cat
INNER JOIN ALBSSRS01.dbo.Subscriptions AS sub
ON cat.ItemID = sub.Report_OID
INNER JOIN ALBSSRS01.dbo.ReportSchedule AS res
ON cat.ItemID = res.ReportID
AND sub.SubscriptionID = res.SubscriptionID
INNER JOIN msdb.dbo.sysjobs AS job
ON CAST(res.ScheduleID AS VARCHAR(36)) = job.[name]
INNER JOIN msdb.dbo.sysjobschedules AS sch
ON job.job_id = sch.job_id
INNER JOIN ALBSSRS01.dbo.Users U
ON U.UserID = sub.OwnerID
ORDER BY sub.LastStatus

Post #1385681

Ajay Surendran

Posted Friday, November 16, 2012 8:47 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 1:29 AM
Points: 47, Visits: 87

Thanks for the reply Daniel. Smile

I want create a subscription from the stored procedure.
Assuming the Stored procedure will have the inputs for schedules and export format etc.
When I run procedure i want the report to subscribe as we create a new subscription from report manager.

Post #1385731

Daniel Bowlin

Posted Friday, November 16, 2012 1:34 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 8:49 AM
Points: 2,672, Visits: 2,417

It sounds like you are trying to create a data driven subscription. Do you have the enterprise version of SQL Server?

Post #1385849

Ajay Surendran

Posted Monday, November 19, 2012 12:11 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 1:29 AM
Points: 47, Visits: 87

Mine is Standard Edition SQL 2008 R2

Post #1386175

« Prev Topic | Next Topic »

Permissions