How to disable/enable multiple sql jobs in efficient way
Guys,
I was thinking to perform some multiple update with sql job, which to set them disabled at specific time and again set it again enable, here is my query, does anyone here have better than a query below to do update?
USE MSDB;
GO
UPDATE J
SET J.Enabled = 0
FROM MSDB.dbo.sysjobs J
INNER JOIN MSDB.dbo.syscategories C
ON J.category_id = C.category_id
WHERE C.[Name] in
('JOB1','JOB2','JOB5','JOB
)
GO
I was thinking to perform some multiple update with sql job, which to set them disabled at specific time and again set it again enable, here is my query, does anyone here have better than a query below to do update?
USE MSDB;
GO
UPDATE J
SET J.Enabled = 0
FROM MSDB.dbo.sysjobs J
INNER JOIN MSDB.dbo.syscategories C
ON J.category_id = C.category_id
WHERE C.[Name] in
('JOB1','JOB2','JOB5','JOB
)
GO
Update - In your query you are using
WHERE C.[Name] in ('JOB1','JOB2','JOB5','JOB
this c.Name is not the jobname it is the category name.
Hope it helps.
WHERE C.[Name] in ('JOB1','JOB2','JOB5','JOB
this c.Name is not the jobname it is the category name.
Hope it helps.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
If you need list of jobs and then decide...
Sample Output...
Exec MSDB.dbo.sp_update_job @job_name = N'syspolicy_purge_history'
SELECT 'Exec MSDB.dbo.sp_update_job @job_name = N''' + a.Name + ''', @Enabled = 0'
FROM msdb..sysjobs a
JOIN msdb..syscategories b
ON a.category_id = b.category_id
WHERE a.[Enabled] = 1 --AND a.name IN ( 'a' , 'b' ) Sample Output...
Exec MSDB.dbo.sp_update_job @job_name = N'syspolicy_purge_history'
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Hi
Your suggestion 41841193 to disabled / enable the job via sp_update_job sound promising and much safer. But how about if I want to run this in the sql jobs instead of me manually firing it up in ssms query ? My situation is to disabled teh job at specific time then again enable the job, this going to happen at each week midnight.
Your suggestion 41841193 to disabled / enable the job via sp_update_job sound promising and much safer. But how about if I want to run this in the sql jobs instead of me manually firing it up in ssms query ? My situation is to disabled teh job at specific time then again enable the job, this going to happen at each week midnight.
You need to get the job id based on the job name. All the other system stored procedures work on the job Id. So as long as you know the job name, you should be all set to embed this in a custom query/set of queries.
ASKER
Hi,
Could someone help me to embded the query below, upon generating the exec syntax the next step for the script is to run the procedure.
SELECT 'Exec MSDB.dbo.sp_update_job @job_name = N''' + a.Name + ''', @Enabled = 0'
FROM msdb..sysjobs a
JOIN msdb..syscategories b
ON a.category_id = b.category_id
WHERE a.[Enabled] = 1 --AND a.name IN ( 'a' , 'b'
Could someone help me to embded the query below, upon generating the exec syntax the next step for the script is to run the procedure.
SELECT 'Exec MSDB.dbo.sp_update_job @job_name = N''' + a.Name + ''', @Enabled = 0'
FROM msdb..sysjobs a
JOIN msdb..syscategories b
ON a.category_id = b.category_id
WHERE a.[Enabled] = 1 --AND a.name IN ( 'a' , 'b'
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Open in new window
The MSDB.dbo.syscategories table will be used if we want to disable Jobs By Job Category.