1

I have a report that generates PDF's using a data driven subscription process.

When the job is started, it shows a success status shortly afterwards, however I believe that this is really asynchronous and therefore cannot be used as an indication of the jobs status. Is there a reliable indicator that can be used?

Ideally this would be something that would allow me to automate a check in a stored procedure.

1

Using the ReportServer database, I have a script that appears to work.

Querying the same table as my data driven query

select count(distinct param)
from MyDataDrivenSourceTable

and compare the result of that to the below

    SELECT count(*)
  FROM [ReportServer].[dbo].[Schedule]
  INNER JOIN [ReportServer].[dbo].ReportSchedule ON ReportSchedule.ScheduleID = Schedule.ScheduleID
  INNER JOIN [ReportServer].[dbo].[ExecutionLogStorage] ON ExecutionLogStorage.ReportID = ReportSchedule.ReportID
  WHERE Schedule.ScheduleID = '********INSERT JOB NAME GUID HERE********'
  AND TimeStart >= schedule.LastRunTime

Then I should be able to compare the results to know when it has completed.

I'm relying on an internal table to SSRS which could change at any time, so I appreciate this method is totally unreliable, but it seems better than polling to see which files have appeared on disk.

This makes some massive assumptions:

  • The source table is not updated in-between the start of the SSRS subscription and when the check is performed.
  • The last run time is updated at the start of the execution.
  • I have no idea what would happen if the job was called again half way through the run.
  • This doesn't always appear to work, I think writing to the file share in asyncronous. I have seen examples where the job continues despite not having written the file to disk. Next step is to write a cursor that checks for each file individually – Neil P May 24 '16 at 8:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.