I want to do an upgrade on a busy SQL 2000 server that has lots of jobs kicking off at different times. But first I need to wait until there are no jobs running before I shut down the SQL Agent.
My plan was to make a copy of sysjobs, then I did
UPDATE sysjobs SET Enabled = 0
To my surprise the jobs kept executing. When I queried the table, sure enough the Enabled column (smallint) was all zeros. I'm thinking the Agent must have a cache somewhere which needs to be refreshed, but I don't know how to do that.
Any ideas on how to disable my jobs via TSQL?
It might be easier to use:
exec msdb..sp_update_job @job_name = 'Job Name', @enabled = 0
The way I learned some of the s/p's was to right click on the job and choose All tasks...Generate SQL script. That shows many s/p's used to create a job on another server.