Check the finish time of data driven subscription using Sql

How can I check using sql that a data driven subscription has run and the time it had finished?

Thanks

Elias

Hello Elias,

Run this from the server with the Reporting Services database:

select c.name, el.TimeStart, el.TimeEnd, el.TimeDataRetrieval, el.TimeProcessing, el.TimeRendering
from ReportServer.dbo.executionlog el
innerjoin ReportServer.dbo.catalog c on el.reportid = c.itemid
where c.name='ReportName'
orderby timestart desc

Hope this helps.

Jarret

|||

Jarret,

Thanks, that is helpfull. But the question is not at the report level but at the subsctiption level. What I mean is, I would like to pass in a Subscription Description and find out if this subscription finished running.

Elias

|||

Try this:

select c.name, el.TimeStart, el.TimeEnd, el.TimeDataRetrieval, el.TimeProcessing, el.TimeRendering, s.Description
from ReportServer.dbo.executionlog el
innerjoin ReportServer.dbo.catalog c on el.reportid = c.itemid
innerjoin ReportServer.dbo.subscriptions s on c.itemid = s.report_oid
where s.description ='SubscriptionDescription'
orderby el.timestart desc

If there is an entry in the executionlog table, that means it completed.

Jarret

|||

Jarret,

Thanks.

A couple of questions:

1) It seems that the only way to know if this was a success is to parse the ReportServer.dbo.subscriptions.LastStatus for "Done" & "0 Errors"?

2) Is there any way to bring back only those rows in ReportServer.dbo.executionlog that were created by running the the subscription which relates to the ReportServer.dbo.subscriptions.LastRunTime. (is there some Id on the subscription that the executionlog gets tagged with)

Elias

|||

There is a 'status' field on ExecutionLog that says 'rsSuccess' if the job was successful.

Unfortunately, the times don't match and there is no ID on the executionlog to correspond to the subscription. I believe the times are different because when the report is done running, it writes the executionlog, but the email/file delivery has to take place before the subscription is completed. The ExecutionLog.TimeEnd should be fairly close to the Subscriptions.LastRunTime, but not exact. If you run your report every hour, then you can check to see if the two times are within, say, 10 minutes of each other.

Jarret