<div>
Try..<br />
<br />

<pre><code class="code-1 nolinks" id="code-20-41840462-1"><span>USE MSDB</span>
<span>GO</span>
<span></span>
<span>UPDATE m </span>
<span>SET m.Enabled = 0</span>
<span>FROM MSDB.dbo.sysjobs m</span>
<span>WHERE name IN ( 'a' , 'b' )</span>
<span></span>
<span>GO</span>
</code></pre>
<br />
<b>The MSDB.dbo.syscategories table will be used if we want to disable Jobs By Job Category. </b>
</div>


<div>
Update - In your query you are using <br />
<br />
<b>WHERE C.[Name] in ('JOB1','JOB2','JOB5','JOB<wbr />20'</b><br />
<br />
<b>this c.Name is not the jobname it is the category name.</b><br />
<br />
Hope it helps.
</div>


<div>
If you need list of jobs and then decide...<br />
<br />

<pre><code class="code-1 nolinks" id="code-20-41840506-1"><span>SELECT 'Exec MSDB.dbo.sp_update_job @job_name = N''' + a.Name + ''', @Enabled = 0'</span>
<span>  FROM msdb..sysjobs a </span>
<span>  JOIN msdb..syscategories b</span>
<span>    ON a.category_id = b.category_id</span>
<span> WHERE a.[Enabled] = 1 --AND a.name IN ( 'a' , 'b' ) </span>
</code></pre>
<br />
Sample Output...<br />
<br />
Exec MSDB.dbo.sp_update_job @job_name = N'syspolicy_purge_history'<wbr />, @Enabled = 0
</div>


<div>
Hi <br />
<br />
Your suggestion &nbsp;41841193 &nbsp;to disabled / enable the job via sp_update_job sound promising and much safer. But how about &nbsp;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.
</div>


<div>
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.
</div>


<div>
Hi,<br />
Could someone help me to embded the query below, upon generating the exec syntax &nbsp;the next step for the script is to run the procedure.<br />
<br />
SELECT 'Exec MSDB.dbo.sp_update_job @job_name = N''' + a.Name + ''', @Enabled = 0'<br />
&nbsp; FROM msdb..sysjobs a <br />
&nbsp; JOIN msdb..syscategories b<br />
&nbsp; &nbsp; ON a.category_id = b.category_id<br />
&nbsp;WHERE a.[Enabled] = 1 --AND a.name IN ( 'a' , 'b'
</div>


<div>
<div class="content wysiwyg-content">
Guys,<br />
<br />
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?<br />
<br />
<br />
USE MSDB;<br />
GO<br />
UPDATE J<br />
SET J.Enabled = 0<br />
FROM MSDB.dbo.sysjobs J<br />
INNER JOIN MSDB.dbo.syscategories C<br />
ON J.category_id = C.category_id<br />
WHERE C.[Name] in <br />
('JOB1','JOB2','JOB5','JOB<wbr />20'<br />
)<br />
GO
</div>
</div>


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','JOB20'
)
GO

How to disable/enable multiple sql jobs in efficient way

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.