How to get from JobID back to SubscriptionID

Hi!
I am hoping someone can explain how I can determine the subscription
associated with a currently running job (report). If a report is misbehaving,
we would like to determine the subscription that initiated the report
running. We can cancel the job to stop the report from running this time, but
we would like to be able to prevent it from running again.
I believe that this would mean tieing the jobid back to a subscriptionid,
but I have not been able to determine a way either programatically or via a
direct query of the RS tables to determine this.
Any help would be greatly appreciated.
Thanks in advance,
BobHello,
This is what I do:
--This will help you identify the OID of the report
SELECT ItemID, Path
FROM Catalog
--This will show all subscriptions for the report
SELECT *
FROM Subscriptions
WHERE Report_OID = '[ItemID from previous query]'
--This will tell you the job id
SELECT *
FROM ReportSchedule
WHERE SubscriptionID = '[SubscriptionID from the previous query]'
You can put all together, but I prefer to run them one by one because I have
a lot of reports and also several subscriptions per report.
Hope this helps.
Ricardo.
"bobhug" wrote:
> Hi!
> I am hoping someone can explain how I can determine the subscription
> associated with a currently running job (report). If a report is misbehaving,
> we would like to determine the subscription that initiated the report
> running. We can cancel the job to stop the report from running this time, but
> we would like to be able to prevent it from running again.
> I believe that this would mean tieing the jobid back to a subscriptionid,
> but I have not been able to determine a way either programatically or via a
> direct query of the RS tables to determine this.
> Any help would be greatly appreciated.
> Thanks in advance,
> Bob|||Ricardo,
Thanks for the response.
I do not see how this gets me to JobID however.
SELECT *
FROM ReportSchedule
WHERE SubscriptionID = '[SubscriptionID from the previous query]'
This would give me scheduleid and reportid, but I still do not know how to
get to jobid.
Perhaps I am just missing something.
Thanks,
Bob
"Ricardo Sampei" wrote:
> Hello,
> This is what I do:
> --This will help you identify the OID of the report
> SELECT ItemID, Path
> FROM Catalog
> --This will show all subscriptions for the report
> SELECT *
> FROM Subscriptions
> WHERE Report_OID = '[ItemID from previous query]'
> --This will tell you the job id
> SELECT *
> FROM ReportSchedule
> WHERE SubscriptionID = '[SubscriptionID from the previous query]'
> You can put all together, but I prefer to run them one by one because I have
> a lot of reports and also several subscriptions per report.
> Hope this helps.
> Ricardo.
> "bobhug" wrote:
> > Hi!
> > I am hoping someone can explain how I can determine the subscription
> > associated with a currently running job (report). If a report is misbehaving,
> > we would like to determine the subscription that initiated the report
> > running. We can cancel the job to stop the report from running this time, but
> > we would like to be able to prevent it from running again.
> > I believe that this would mean tieing the jobid back to a subscriptionid,
> > but I have not been able to determine a way either programatically or via a
> > direct query of the RS tables to determine this.
> > Any help would be greatly appreciated.
> >
> > Thanks in advance,
> > Bob|||Hello Bob,
Probably you have already figured out thi one, but anyway, here is the
answer: What you get in ScheduleID from ReportSchedule is the job name in SQL
Server Agent.
Ricardo.
"bobhug" wrote:
> Ricardo,
> Thanks for the response.
> I do not see how this gets me to JobID however.
> SELECT *
> FROM ReportSchedule
> WHERE SubscriptionID = '[SubscriptionID from the previous query]'
> This would give me scheduleid and reportid, but I still do not know how to
> get to jobid.
> Perhaps I am just missing something.
> Thanks,
> Bob
> "Ricardo Sampei" wrote:
> > Hello,
> >
> > This is what I do:
> > --This will help you identify the OID of the report
> > SELECT ItemID, Path
> > FROM Catalog
> >
> > --This will show all subscriptions for the report
> > SELECT *
> > FROM Subscriptions
> > WHERE Report_OID = '[ItemID from previous query]'
> >
> > --This will tell you the job id
> > SELECT *
> > FROM ReportSchedule
> > WHERE SubscriptionID = '[SubscriptionID from the previous query]'
> >
> > You can put all together, but I prefer to run them one by one because I have
> > a lot of reports and also several subscriptions per report.
> >
> > Hope this helps.
> >
> > Ricardo.
> >
> > "bobhug" wrote:
> >
> > > Hi!
> > > I am hoping someone can explain how I can determine the subscription
> > > associated with a currently running job (report). If a report is misbehaving,
> > > we would like to determine the subscription that initiated the report
> > > running. We can cancel the job to stop the report from running this time, but
> > > we would like to be able to prevent it from running again.
> > > I believe that this would mean tieing the jobid back to a subscriptionid,
> > > but I have not been able to determine a way either programatically or via a
> > > direct query of the RS tables to determine this.
> > > Any help would be greatly appreciated.
> > >
> > > Thanks in advance,
> > > Bob|||Ricardo,
Hi!
Now I understand the confusion.
The JobID I was referring to is the one as found in the RunningJobs table
and the Job class: http://tinyurl.com/9zyb2 , which is different than what
you are talking about, the name of the SQL Server agent job.
Thanks for responding,
Bob
"Ricardo Sampei" wrote:
> Hello Bob,
> Probably you have already figured out thi one, but anyway, here is the
> answer: What you get in ScheduleID from ReportSchedule is the job name in SQL
> Server Agent.
> Ricardo.
> "bobhug" wrote:
> > Ricardo,
> >
> > Thanks for the response.
> > I do not see how this gets me to JobID however.
> > SELECT *
> > FROM ReportSchedule
> > WHERE SubscriptionID = '[SubscriptionID from the previous query]'
> > This would give me scheduleid and reportid, but I still do not know how to
> > get to jobid.
> >
> > Perhaps I am just missing something.
> > Thanks,
> > Bob
> >
> > "Ricardo Sampei" wrote:
> >
> > > Hello,
> > >
> > > This is what I do:
> > > --This will help you identify the OID of the report
> > > SELECT ItemID, Path
> > > FROM Catalog
> > >
> > > --This will show all subscriptions for the report
> > > SELECT *
> > > FROM Subscriptions
> > > WHERE Report_OID = '[ItemID from previous query]'
> > >
> > > --This will tell you the job id
> > > SELECT *
> > > FROM ReportSchedule
> > > WHERE SubscriptionID = '[SubscriptionID from the previous query]'
> > >
> > > You can put all together, but I prefer to run them one by one because I have
> > > a lot of reports and also several subscriptions per report.
> > >
> > > Hope this helps.
> > >
> > > Ricardo.
> > >
> > > "bobhug" wrote:
> > >
> > > > Hi!
> > > > I am hoping someone can explain how I can determine the subscription
> > > > associated with a currently running job (report). If a report is misbehaving,
> > > > we would like to determine the subscription that initiated the report
> > > > running. We can cancel the job to stop the report from running this time, but
> > > > we would like to be able to prevent it from running again.
> > > > I believe that this would mean tieing the jobid back to a subscriptionid,
> > > > but I have not been able to determine a way either programatically or via a
> > > > direct query of the RS tables to determine this.
> > > > Any help would be greatly appreciated.
> > > >
> > > > Thanks in advance,
> > > > Bob