October 6, 2006 at 7:42 am
I would like to use SSIS to enable/disable jobs during holidays. Here's my plan...
1. Create a store procedure to query each server for a list of enabled jobs and records those jobs in a table called job_status on a single server. For Example: Server A would store list of enabled jobs on all servers.
1a. The stored procedure will then disable these jobs
2. Create a stored procedure to enable the jobs previously disabled per server
Job_Status Table would look like this:
Server_Name Job_Name AddDate
Server 1 Job 1 1/1/06
Server 2 Job 1 1/1/06
I am able to do this by creating 2 jobs and a table for each server. To be honest, I'm a little lazy and would rather create 1 SSIS package to accomplish this for each server. Is it possible to disable the jobs on Server B by querying the job_status table on Server A?
October 6, 2006 at 9:31 am
I believe you should be able to do this, but be aware that you will need to use msdb.dbo.sp_update_job to do it:
EXEC msdb.dbo.sp_update_job @job_name=N'test',
@enabled=0
If you try and update the sysjobs table enabled column to 0 the SQL agent will not pick this up untill it is restarted, msdb.dbo.sp_update_job refreshes the sql agent but does it via a dll so you cannot control it.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply