May 14, 2011 at 2:52 pm
We are using SSRS 2008R2 Enterprise.
We have created a data driven subscripton using the null delivery provider. This subscription is not an email list, just a way to preload the varous parameters for the report. We are trying to pre load the report cache based on the data source to improve the rendering and processing time for the report. The source data may be updated more then once per day and some unspecified time (due to our ETL process). Therefore scheduling a snapshot update is not practical, as for example the data may be changed at 7am and at 8:15 am. Is there a way to create a trigger for the snapshot to run without using the built in scheduling for the report cache.
Can any one help?
May 14, 2011 at 3:54 pm
Make the last step of your ETL a call to execute the SQL Server Agent job that triggers the update. All report subscriptions create an Agent Job on the server.
May 18, 2011 at 5:15 pm
Thanks Daniel, that did the trick, but it seems that you must keep the original schedule as well.
Seems that now the subscrption works for the main report but the detail section stays in the 'Pending' status in the subscripton status window.
Ever seen that before?
We are using ssrs 2008r2
June 30, 2011 at 2:27 pm
I have several subscriptions on R2 that seem to get stuck in the Pending status - but the reports have been created. This is one of the weaknesses of SSRS. You might want to check the log file directly to see if the subscription is actually being kicked off or not and if it gets to completion.
MWise
June 30, 2011 at 2:34 pm
Mhlewis (6/30/2011)
I have several subscriptions on R2 that seem to get stuck in the Pending status - but the reports have been created. This is one of the weaknesses of SSRS. You might want to check the log file directly to see if the subscription is actually being kicked off or not and if it gets to completion.MWise
I think you might want to start a new thread for this. Taking over an existing (old) thread is a bad practice. Sorry.
CEWII
June 30, 2011 at 4:12 pm
I figured this one out on my own. How do we 'kill' the thread? Admin?
July 1, 2011 at 8:04 am
I wasn't post-jacking. I was sharing my experience and advice directly in response to the original poster (MaricopaJoe) concerning his issues with a data driven subscription. Granted his circumstances changed slightly after the first go-round (he got the subscription partially running, but was having issues with the status), but it was still his thread and issue.
More importantly he says that he fixed it - so MaricopaJoe tell us what you did. Happy endings are always nice.
Thanks,
MWise
July 2, 2011 at 6:59 pm
We decided not to use a data driven subscription as the goal was to only purge and update the report cache when our ETL process completed.
We accomplished this by finding the jobs in SSMS that run the purge and cache jobs.
We rescheduled them to run far in future (deleting the jobs, or disabling the jobs does not work as Reporting Services will recreate the jobs) Since the scheduler in reporting services only changes at a month at a time, it was easier to do this in the back end.
We then created a job that executes the stored procs that purge and refresh the cache (by examining the contents of the above two jobs).
Now when our ETL process runs, the cache is purged and refreshed. Even though the schedule inside SSRS says 'next run' is 12/31/2099.
Monitoring the jobs inside SSRS shows the completed jobs and last execution.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply