To the MSSQLTips.com community, 

As an update to this tip, our team has observed some issues with SQL Server 2000 SP4 with respect to determining the 'Next Run Date' without restarting the SQL Server Agent Service.  Please note this potential issue and test accordingly.

Thank you,
The MSSQLTips.com Team

I tried using this method (changing sysjobs.enabled from 1 to 0) for disabling a group of jobs which contained specific text using the like predicate (SQL 2K5 SP4 Enterprise x64). However, the jobs still ran. In SSMS under Jobs they show disabled (little red arrow on job), on job activity monitor they show disabled and on job properties they show disabled. The schedule is still enabled but I tested this idea by right clicking on each job and selecting disable [one at a time...ugh] and it only seems to disable the job not the schedule.

Is the only way to disable a job using TSQL by using sp_update_job? I don't like this concept because I cannot disable a group of jobs at one a time easily.

 Thank you!

Hi,

I have tried to use the mentionned method to enable/disable a job using T-SQL, and I was facing the same behaviour as sgr mentionned here before (under SQL2000 server): although the status was correctly updated in hte job list, the job was actually acting keeping its previous status (set manually).

I found the solution (at least for me it's working) :
instead of updating the sysjobs table :

UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE [Name] = 'My_Job_Name_xyz';

you should rather use the stored procedure "sp_update_job" :

EXEC msdb.dbo.sp_update_job @job_name = 'My_Job_Name_xyz',
@enabled = 0

Hope it helps...

Laurent.

hi,

i have faced the same problem and found that updating the system table sysjobs doesnt work.it the jobs run.

even though i disabled the corresponding shedule from sysshedule the job still runs. then i restarted the Agent.

and then job was actually disabled.

but this is not the case with sp_update_job it disables the job without restarting the agent.

Thanks

Great Job

Sql jobs are not working under sql server agent in sql server 2008

I got below error like these

Job_Name=Km_Report

Date                      1/2/2013 11:45:00 PM

Log                         Job History (KM_Report)

Step ID                 0

Server                   CATOM-GMAPDVW69

Job Name                            KM_Report

Step Name                         (Job outcome)

Duration                              00:00:39

Sql Severity                        0

Sql Message ID                 0

Operator Emailed                           

Operator Net sent                          

Operator Paged                               

Retries Attempted                          0

Message

The job failed.  The Job was invoked by Schedule 13 (KM_Report).  The last step to run was step 1 (KM_Report).

2^nd

Date                      1/2/2013 11:45:01 PM

Log                         Job History (KM_Report)

Step ID                 1

Server                   CATOM-GMAPDVW69

Job Name                            KM_Report

Step Name                         KM_Report

Duration                              00:00:38

Sql Severity                        0

Sql Message ID                 0

Operator Emailed                           

Operator Net sent                          

Operator Paged                               

Retries Attempted                          0

Message

Executed as user: CATOM-GMAPDVW69\SYSTEM. 016     Source:       Description: Failed to decrypt protected XML node "SQLPassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  11:45:01 PM  Finished: 11:45:39 PM  Elapsed:  38.25 seconds.  The package execution failed.  The step failed.

hanumanthareddy,

I think this is the portion of the error message you need to focus on from your DTEXEC command:

Failed to decrypt protected XML node "SQLPassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. 

HTH.

Thank you,
Jeremy Kadlec
Community Co-Leader

I'm happier using sp_update_job, rather than updating sysjobs directly, mainly to get around the caching issue. Just stick the query logic from above in a cursor first, eg, for "Disable Jobs By Job Category":

DECLARE @SelectedJobId uniqueidentifier

DECLARE Job_Cursor CURSOR FOR
SELECT Job_Id FROM MSDB.dbo.sysjobs J
INNER JOIN MSDB.dbo.syscategories C
ON J.category_id = C.category_id
WHERE C.Name = 'Log Shipping'

OPEN Job_Cursor
FETCH NEXT FROM Job_Cursor
INTO @SelectedJobId

WHILE @@FETCH_STATUS = 0
BEGIN
  EXEC dbo.sp_update_job @job_id = @SelectedJobId
                        ,@enabled = 0
  FETCH NEXT FROM Job_Cursor
  INTO @SelectedJobId
END
CLOSE Job_Cursor
DEALLOCATE Job_Cursor