SSRS 2008 R2 Repeat One-Time Subscription

1
votes

I am wanting to create a one-time SSRS shared schedule that can be altered once run, to execute again at a future point in time. The reason I am having to set-up a subscription in this way is because I need to email a set of reports based on a bespoke accounting calendar; we don't have enterprise so data-driven is not an option.

I have tested this method in the ReportServer.dbo.Subscription and Schedule tables, but have so far not managed to get it to work. I have updated the NextRunTime and StartTime columns of the Schedule table to the time I require the reports to be sent, and this appears to update on the Report Server Shared Schedules page (Next Run) but the email does not send.

Is there another table on the ReportServer database that I need to update, or is this simply not possible?

Thanks

Andy

2
reporting-servicesreportingsubscriptions
Set it up as reoccurring. Since you can't query for the next runtime you will need to have it run the first time at a specific time then after every 5 years in the future. You can then update the reoccurrence from five years to some other date to have it fire again. - Ross Bush
Hi LRB, Thanks for the response - I have tried this but with no success. Do you know whether there is some kind of system table overriding the information in the dbo.Schedule table? - ABryan
No but you should defiantly see it in the log file if it failed. There is a column on one of the subscription pages that indicates status. What does the status indicate after the subscription should have fired but did not. - Ross Bush

2 Answers

0
votes

I suggest taking a look at the article below. It details how to set up data driven subscriptions on SQL Server Standard Edition, and goes through manually activating subscriptions via SQL Server Agent.

http://www.sqlservercentral.com/articles/Development/datadrivensubscriptions/2432/

I've used if for a couple of clients and it generally works pretty well. The only thing I've since altered is increasing the timer to 10 seconds between running each data driven report.

Regards,

Jason

0
votes

In reading up on this, messing abound in the tables is not supported by Microsoft. USE AT YOUR OWN RISK!

I attempted the very same thing. When you change a subscription on the front end, the ReportServer.dbo.Schedule.StartDate column changes with each edit. I tried just altering this value, but adding a value here made no difference. There was no email and when I went back to my subscription in the SSRS webpage, the original datetime value was there. So that datetime must be stored elsewhere.

I did a SQL Trace and found these Sproc's were being called. I copied out the lines that looked promising. By changing the time value @active_start_time=160400 in the last one and running the entire batch, I could get an emailed report at the desired time. But I am not content with this; because when I go back and view My Subscriptions webpage, the original datetime is still listed. 

exec msdb.dbo.sp_delete_job @job_name=N'D478A346-ED05-422D-A73E-023080AD56DD'

exec msdb.dbo.sp_add_job @job_name=N'D478A346-ED05-422D-A73E-023080AD56DD',@description=N'This job is owned by a report server process. Modifying this job could result in database incompatibilities. Use Report Manager or Management Studio to update this job.',@category_id=101

exec msdb.dbo.sp_add_jobserver @job_name=N'D478A346-ED05-422D-A73E-023080AD56DD',@server_name =N'(LOCAL)'

exec msdb.dbo.sp_add_jobstep @job_name=N'D478A346-ED05-422D-A73E-023080AD56DD',@step_name=N'D478A346-ED05-422D-A73E-023080AD56DD_step_1',@command=N'exec [ReportServer$instanceDB].dbo.AddEvent @EventType=''TimedSubscription'', @EventData=''db7d7b08-3eee-4bb8-b354-b58c653b8ab6'''

exec msdb.dbo.sp_add_jobschedule @job_name=N'D478A346-ED05-422D-A73E-023080AD56DD',@name=N'Schedule_1',@freq_type=1,@active_start_date=20180405,@active_start_time=160400,@freq_subday_type=1