August 1, 2011 at 9:51 am
Has anyone got a TSQL script to move reports from one shared schedule to another without changing the parameters, destination, user etc.?
My least favourite user has created 10 reports, each with at least half a dozen subreports and a dozen parameters and has scheduled them to run about 10 times each with different parameters - all on the same schedule on a mediocre server!
I need to split them up between existing shared schedules without going into each one individually and re-entering passwords etc. so a bit of SQL would be ideal.
I see the schedule ID in the ReportSchedule table - can I just change that?
August 1, 2011 at 9:57 am
One test I'd try is to go to the sql agent and change the schedule there. The only problem is that as soon as the user goes back to the schedule UI that will get dropped and recreated.
I'm really warry about changing the system tables of MS and I wouldn't recommend that route even if I have no alternatives to offer.
What I've done in the past for a user is to select all of it's subscriptions and refire them all at once if for some reason the server was offline during the night. Maybe you could adapt this to fire them only 1 at a time when the server has some breathing time...
DECLARE @Exec AS VARCHAR(MAX)
SELECT @Exec = COALESCE(@Exec + 'EXEC msdb.dbo.sp_start_job @job_name = ''' + CAST(Scd.ScheduleID AS VARCHAR(MAX)) + ''';', 'EXEC msdb.dbo.sp_start_job @job_name = ''' + CAST(Scd.ScheduleID AS VARCHAR(MAX)) + ''';') FROM dbo.Schedule Scd
INNER JOIN dbo.Users U
ON Scd.CreatedById = U.UserID
WHERE U.UserName = 'that darn user'
EXEC (@Exec)
Put that in a loop with waitfor and schedule the job to run whenever you know the server is less busy.
You could create another script that disable all the jobs for that users based on the name of the schedule you should be able to track them that way. If you disable the schedule they won't run, but you'll be able to fire them whenever you want.
August 1, 2011 at 10:08 am
It's not the agent job I want to change, there's a hundred subscriptions on the one agent job and none on others so I need to even it out. Re-running failures, even with a script, is a pain as it needs a person to do it.
Getting the user from hell to change his report subscriptions is even less of an option - my ears are aching quite enough already:angry:
August 1, 2011 at 10:12 am
P Jones (8/1/2011)
It's not the agent job I want to change, there's a hundred subscriptions on the one agent job and none on others so I need to even it out. Re-running failures, even with a script, is a pain as it needs a person to do it.Getting the user from hell to change his report subscriptions is even less of an option - my ears are aching quite enough already:angry:
Those are unfortunaltely all the options I know of.
1 might accidentally find all his subscriptions deleted but a problem with the server that we can't fix... but that's sneaky way to go (make sure you have your boss on board for this one).
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply