quote:

---

Originally posted by Rhahn

Does any one know why the timers get all screwed up when you disable a job and more importantly does any one know of a Quick way to reinsulated all the timers without manually going through each one and resetting the start date?

---

DECLARE @JOBNAME varchar(150)
SET @JOBNAME = 'INSERT JOB NAME HERE'

UPDATE msdb..sysjobschedules 
SET Enabled = 1 --, active_start_date = convert(varchar(8), getdate(), 112), active_start_time = left(replace(convert(varchar(8), dateadd(n, 1, getdate()), 8), ':', ''), 4) + '00', next_run_date = convert(varchar(8), getdate(), 112), next_run_time = left(replace(convert(varchar(8), dateadd(n, 1, getdate()), 8), ':', ''), 4) + '00'
FROM msdb..sysjobs j
	LEFT OUTER JOIN msdb..sysjobschedules s ON (s.job_id = j.job_id)
WHERE j.Name = @JOBNAME

DECLARE @job_id UNIQUEIDENTIFIER,
	@schedule_id int

SELECT @job_id = j.Job_ID, @schedule_id = S.Schedule_ID 
FROM msdb..sysjobs j (NoLock) 
	LEFT OUTER JOIN msdb..sysjobschedules s (NoLock) ON (s.job_id = j.job_id) 
WHERE j.Name = @JOBNAME

-- Update the job's version/last-modified information  
UPDATE msdb.dbo.sysjobs  
SET 	version_number = version_number + 1,  
	date_modified = GETDATE()  
WHERE (job_id = @job_id)  
  
-- Notify SQLServerAgent of the change, but only if we know the job has been cached  
IF (EXISTS (	SELECT s.*  
		FROM msdb.dbo.sysjobservers s (NoLock)
			INNER JOIN msdb.dbo.sysjobs j (NoLock) on (j.job_id = s.job_id)
		WHERE server_id = 0 and j.name = @JOBNAME)		)  
BEGIN  
	EXEC master.dbo.xp_sqlagent_notify 'S', @job_id, @schedule_id, null, 'U', 'INSERT NT ACCOUNT HERE', 1, @@trancount 
END  
GO

SET NOCOUNT ON

CREATE TABLE #Job_Names
(	
	Job_Name SYSNAME NOT NULL
)

INSERT INTO #Job_Names
SELECT name
FROM msdb.dbo.sysjobs
ORDER BY name

DECLARE @job_name SYSNAME
DECLARE @job_id UNIQUEIDENTIFIER

DECLARE disable_jobs CURSOR FOR	
SELECT Job_Name
FROM #Job_Names

SET @job_id = NULL

OPEN disable_jobs
FETCH NEXT FROM disable_jobs INTO @job_name

WHILE @@FETCH_STATUS = 0
BEGIN
	EXEC msdb.dbo.sp_verify_job_identifiers '@job_name', '@job_id', @job_name OUTPUT, @job_id OUTPUT

	EXEC msdb.dbo.sp_update_job @job_id, @enabled = 0
	
	SET @job_id = NULL

	FETCH NEXT FROM disable_jobs INTO @job_name
END

CLOSE disable_jobs
DEALLOCATE disable_jobs

DROP TABLE #Job_Names

quote:

---

Originally posted by Rhahn

So it’s a bad idea to modify systables, check. Using EM is way to slow and my fingers get soar from repetitive clicking. How would one do this:
Update sysjobs
Set enabled = 0
Where category_id = 3
Using system_sp’s?

---

quote:

---

Originally posted by Rhahn

You'll need to run the system proc: xp_sqlagent_notify

The SQL Server Agent keeps the job data cached. You can't just update system tables and do nothing else and still think that things will work correctly.

---

quote:

---

Originally posted by tkizer

Here is a sample of how to disable all jobs. The code is from my toolbox and looking at it now, it can be simplified a bit, but it should give you an idea of how to do things.

SET NOCOUNT ON

CREATE TABLE #Job_Names
(	
	Job_Name SYSNAME NOT NULL
)

INSERT INTO #Job_Names
SELECT name
FROM msdb.dbo.sysjobs
ORDER BY name

DECLARE @job_name SYSNAME
DECLARE @job_id UNIQUEIDENTIFIER

DECLARE disable_jobs CURSOR FOR	
SELECT Job_Name
FROM #Job_Names

SET @job_id = NULL

OPEN disable_jobs
FETCH NEXT FROM disable_jobs INTO @job_name

WHILE @@FETCH_STATUS = 0
BEGIN
	EXEC msdb.dbo.sp_verify_job_identifiers '@job_name', '@job_id', @job_name OUTPUT, @job_id OUTPUT

	EXEC msdb.dbo.sp_update_job @job_id, @enabled = 0
	
	SET @job_id = NULL

	FETCH NEXT FROM disable_jobs INTO @job_name
END

CLOSE disable_jobs
DEALLOCATE disable_jobs

DROP TABLE #Job_Names

There is a category_name parameter to sp_update_job, so perhaps that can be used to satisfy your request. Or you can modify the select into the temp table in my code to grab the jobs that you want and then use the rest of the code.

Tara Kizer
aka tduggan

---

quote:

---

Originally posted by Rhahn

Thank you both, I've adapted the script to fix my start date problem. but
EXEC msdb.dbo.sp_update_jobschedule @job_id, @active_start_date = @nextDate

is throwing
Server: Msg 201, Level 16, State 4, Procedure sp_update_jobschedule, Line 0
Procedure 'sp_update_jobschedule' expects parameter '@name', which was not supplied.

I understand it want the name of the schedule but why, can't it work like your scipt:
EXEC msdb.dbo.sp_update_job @job_id, @enabled = 0
where you only update the info you need updated?

---

quote:

---

Originally posted by Rhahn

Thanks,
Just out of curiosity why wouldn’t restarting sql server agent clear its cash… that seems a lot easer than messing around with these scripts.. I’m pretty new to this admin stuff and I’m starting to see that the strait forward way to do something never wroks.

---

quote:

---

Originally posted by Rhahn

So I ran the script with sp_update_jobschedule and I've got the same problem as befor. I Don't understand why SQL is having such a hard time looking at its own DataBase and figuring out to start a job.

---

quote:

---

Originally posted by Rhahn

I never could get the xp to work with the syntax in your script, and I couldn't find any thing on the web regarding it.

---

UPDATE msdb..sysjobschedules 
SET Enabled = 1 --, active_start_date = convert(varchar(8), getdate(), 112), active_start_time = left(replace(convert(varchar(8), dateadd(n, 1, getdate()), 8), ':', ''), 4) + '00', next_run_date = convert(varchar(8), getdate(), 112), next_run_time = left(replace(convert(varchar(8), dateadd(n, 1, getdate()), 8), ':', ''), 4) + '00'
FROM msdb..sysjobs j
	LEFT OUTER JOIN msdb..sysjobschedules s ON (s.job_id = j.job_id)
WHERE j.Name = @JOBNAME