March 8, 2010 at 10:16 am
Hello,
I am using SQL Server 2005. We are using SSRS and have about 30 or reports in Report Manager. Actually some of the reports were done in SQL Server 2000 and ported over to the new Server a year ago. Our admin group is building a SQL Server 2008 Report server soon.
But for now, our director has a monitoring tool that sends out emails and let's us know when there is a problem with a server. I have no idea what tool he is using that is privileged information.
But we get in an email that says the maintenance plan and job id. This is a guid. When I see the report server in the email, I know it was a report that failed. Something happened when it tried to generate a subscription and the job failed.
I go to the job history for the guid in the email and try to troubleshoot it. But I wish I knew what report this job is associated with. Many times, I have no idea.
Question
======
Is there a way to know which report had a problem if you review the job history and it shows a failure of one of the jobs (which is associated with a subscription report)?
That would be great to know and help in troubleshooting.
Thanks,
Tony
Things will work out. Get back up, change some parameters and recode.
March 9, 2010 at 11:36 am
I may be reading this incorrectly... Can you confirm the GUID that you get is that the GUID for the Subscription or something else? If it's the subscription you can join back to the catalog to find out the report associated with that subscription.
SELECT c.*
FROM [dbo].[Subscriptions] s
INNER JOIN [dbo].[Catalog] c
ON s.[Report_OID] = c.[ItemID]
Disclaimer: MS recommends against querying the reportserver database directly as things may change in future hotfixes/service packs etc...
Does the GUID represent the actual job that failed? If so you can check the sysjobs table, or use sp_help_JobActivity and sp_help_JobHistory to find what you are looking for.
-Luke.
March 9, 2010 at 12:16 pm
Luke,
The GUID is the job name. The job is automatically created when you create a subscription. How do I know which job got created for which report subscription?
Thanks,
Tony.
Things will work out. Get back up, change some parameters and recode.
March 9, 2010 at 12:34 pm
Job Name or Job ID? If it's the JobName, that is the same as the ScheduleID from the Reportserver database which you can use to join to the catalog to get the name of the report as I stated in my above query.
If it's JobID, then run sp_help_JobHistory '[insert your GUID JobId here]' and it will return the job history for that jobID. The second column from the results of that procedure is the JobName aka the scheduleID. You can also use that procedure to find out a bit more information about the job and perhaps why it failed...
-Luke.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply