November 16, 2012 at 3:49 am
How can we subscribe the SSRS report from a Stored Procedure
November 16, 2012 at 7:00 am
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
November 16, 2012 at 8:47 am
Thanks for the reply Daniel.:-)
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.
November 16, 2012 at 1:34 pm
It sounds like you are trying to create a data driven subscription. Do you have the enterprise version of SQL Server?
November 19, 2012 at 12:11 am
Mine is Standard Edition SQL 2008 R2
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply