January 14, 2008 at 9:53 am
We are running MSRS 2005, over the weekend the server was down. Some of the scheduled subscriptions/jobs did not run thus the reports did not get emailed out. Does anyone know of a script/report/resource/website etc... which explains how to run the subscriptions, or kick-off the job list? I'm sure others have run across this issue. Going into each schedule or report and setting them up for a onetime execution or executing the report manually would be a little tedious.
Thanks,
Dave
January 15, 2008 at 5:32 am
Dave,
SQL 2k5 & SSRS 2k5 set up jobs in SSMS to relate to each individual timed subscription. The bad thing is they name the job with some sort of GUID that makes no sense. The good thing is, if you remember when you created the subscription, you should be able to find the job based on when it was created. And at that point, I advise you to rename the SSMS job.
Once you find the job, just manually kick it off. That should send out the reports for you without a hitch.
January 15, 2008 at 8:06 am
Unfortunately, some of the jobs where set up before my time here, so I can ID a handful of them. I was hoping there was some script or tool I could use.
Thanks, for the help!
Dave
January 15, 2008 at 9:57 am
The name of the jobs are the GUID of the schedule on the table ReportSchedule on ReportServer database.
You can try a query like this to identify the job needed to run:
Use ReportServer
go
Select ReportSchedule.ScheduleID, Catalog.Name, Subscriptions.Description, Subscriptions.LastStatus, Subscriptions.LastRunTime
From ReportSchedule join Subscriptions on ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID
join Catalog on Subscriptions.Report_OID = Catalog.ItemID
go
January 16, 2008 at 4:22 am
Good afternoon,
You can try to find the right job but when the reports that are scheduled are on the report portal. Now it depence on how you setup the subscriptions for the reports.
I prefer to use shared schedules for reports that need to be executed at the same timespan and sequence of days. So when you ahve done this you can just change the shared schedule and let it run after 5 min of the current time on the server. When there send don't forget to put the right timespan back in the shared schedule.
If you don't use a shared schedule you can choose to use the portal to let them run once and then put the right subscription back on but this may take a lot of time if there a lot of reports that need to be send. So then I advice you to try to find the right job in sql server and start them from there.
Niels Naglé
January 16, 2008 at 9:28 am
Thanks! I'll see what I can come up with, there are 140+ MSRS Scheduled jobs, at least I can look on this and look for the GUID in the job list.
June 4, 2008 at 7:47 am
Hi, I am facing the same issue, did you find a practical solution?
June 4, 2008 at 8:11 am
The steps above are the closest you can come to a practical solution.
You can also look in sysjobhistory to find out which jobs did NOT run during the time frame specified, pull out the GUIDs into a temp table and then run a WHILE loop with an sp_startjob command for each of the reports.
June 5, 2008 at 5:13 pm
I came up with report or query which was able to at least 'cross reference' the jobs. Although, the server hasn't gone down since, so I haven't had a chance to really use it. Let me dig it up and see If I can post the solution I was able to cobble together.
June 12, 2008 at 7:00 am
Here is the query I use (as a view):
SELECT TOP (100) PERCENT c.Name AS Report, msdb.dbo.sysjobs.name AS SQLAgentName, s.Description AS ToWho, MAX(s.LastRunTime) AS LastRun, s.LastStatus,
s.DeliveryExtension, c.Description AS HowOften, msdb.dbo.sysjobs.job_id, msdb.dbo.sysjobs.enabled
FROM dbo.Catalog AS c INNER JOIN
dbo.Subscriptions AS s ON s.Report_OID = c.ItemID INNER JOIN
dbo.ReportSchedule ON c.ItemID = dbo.ReportSchedule.ReportID AND s.SubscriptionID = dbo.ReportSchedule.SubscriptionID INNER JOIN
msdb.dbo.sysjobs ON CAST(dbo.ReportSchedule.ScheduleID AS varchar(100)) = CAST(msdb.dbo.sysjobs.name AS varchar(100))
GROUP BY c.Name, msdb.dbo.sysjobs.name, s.Description, s.LastStatus, s.DeliveryExtension, c.Description, msdb.dbo.sysjobs.job_id, msdb.dbo.sysjobs.enabled
ORDER BY Report
The "HowOften" field is the catalog description, which you would see when view the reports via http. I just made sure to put the schedule in there.
I belive we have notfications set up, so if a job fails, I can at least use this view to cross-reference the GUID and kick it off manually.
I suppose another option would be maybe to create an SSIS or Stored Proc that uses this view and check the laststatus field for "Failure" and then kicks off that job.
Thankfully, the server hasnt gone down too much since the orginal issue, so I havent really gone much farther on this that what I mentioned up top.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply