Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Scheduling the SSRS Report from SP Expand / Collapse
Author
Message
Posted Friday, November 16, 2012 3:49 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 04, 2013 1:05 AM
Points: 65, Visits: 98
How can we subscribe the SSRS report from a Stored Procedure
Post #1385587
Posted Friday, November 16, 2012 7:00 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:16 PM
Points: 2,812, Visits: 2,543
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
Posted Friday, November 16, 2012 8:47 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 04, 2013 1:05 AM
Points: 65, Visits: 98
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.
Post #1385731
Posted Friday, November 16, 2012 1:34 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:16 PM
Points: 2,812, Visits: 2,543
It sounds like you are trying to create a data driven subscription. Do you have the enterprise version of SQL Server?
Post #1385849
Posted Monday, November 19, 2012 12:11 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 04, 2013 1:05 AM
Points: 65, Visits: 98
Mine is Standard Edition SQL 2008 R2
Post #1386175
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse