May 13, 2022 at 6:24 pm
I've not done a lot with disabling jobs with script, I need to read the job names from a text a disable them or enable them on all servers within the environment? Is there any good examples for doing this?
May 13, 2022 at 8:22 pm
EXEC msdb.dbo.sp_update_job @job_name='<your_job_name>', @enabled = 0 /* to disable, or 1 to enable */
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 13, 2022 at 8:51 pm
I see that one but need to put the names of jobs in a text file, cause that will enable and disable all jobs, some jobs I don't want to disable, so instead of hard coding them I want to put them in a text file so if a new job is added it reads from the text file, and similar if it is removed.
May 14, 2022 at 7:28 am
Use DBATools and the Set-DbaAgentJob function.
You can easily read in the text file as an array of jobs to pass to the function to enable of disable en-mass
$JobsToChange = Get-Content c:\temp\joblist.txt
Set-DbaAgentJob -SqlInstance <Servername> -Job $JobsToChange -Disable
May 16, 2022 at 9:53 am
FWIW, whenever we disable a job, we leave a trace of it by renaming the job ! (we add a prefix e.g. 'DBA-MaintenanceWindow-'
Doing so, we know who/what did disable the job, and we prevent "sp_start_job" execution in case an application might try to start it directly (which we normally do NOT allow).
As a second note: Be careful when you are using alerts to start jobs!
If you fire an alert for a disabled job, SQLAgent will keep on trying to launch the job ( 6 times/sec ) and will write to SQLAGENT.OUT whenever it attempts to do so, until the disk is full !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 16, 2022 at 4:39 pm
Cant use those PS features, not allowed to install on servers
May 16, 2022 at 5:14 pm
I see that one but need to put the names of jobs in a text file, cause that will enable and disable all jobs, some jobs I don't want to disable, so instead of hard coding them I want to put them in a text file so if a new job is added it reads from the text file, and similar if it is removed.
Put the job names in a SQL table. Read the table and run dynamic SQL with each record from the table.
You could have a 2nd column for Enable/Disable, so your script will perform either function.
May 16, 2022 at 10:02 pm
I wish I could use a table but can't create tables on the servers, I have to read the Job name from a text file, which the jobs are the same across an environment, app server, web server and db server so I have to (disable/enable) those jobs across all those server normally I would just create a package but that option isn't available. I may not be explaining properly hope there's not confusion. I need to use 2016 SSMS to create a manually executable job, that reads a text file, and enables or disables a job name within the text file, there could be 1, 10, 20 ,30 entries in the text file across an environment, that could have 4 server for the environment or 30..
May 17, 2022 at 1:53 pm
Maybe you can BCP from a TXT file into a Temp table to start the process ?
Otherwise, with your limitations, maybe copy-paste saved scripts into SSMS. How often does this need to run ?
May 17, 2022 at 3:41 pm
It is manual, so when deployments are done, I would run it to disable them, then once deployment is done, I would run it to re-enable those disabled jobs in the text file.
May 20, 2022 at 4:36 pm
Here's what I have come up with, but I get an error.
Msg 14294, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 25 [Batch Start Line 2]
Supply either @job_id or @job_name to identify the job. Is there a better way of doing this? and why would I be getting this error, I am supplying the job_name
USE MSDB;
GO
DECLARE @job_name nvarchar(max)
DECLARE @enable int
create table #JobsToDisableEnable
(
name nvarchar(max),
enable int
)
DECLARE @Query nvarchar(max)
SET @Query ='BULK INSERT #JobsToDisableEnable FROM "G:\SQL_Jobs\DisableEnableJobs\joblist.txt"
WITH(
ROWTERMINATOR = ''\n'',
FIRSTROW = 2
)'
EXEC (@Query)
DECLARE job_cursor CURSOR READ_ONLY FOR
SELECT @job_name,[enable]
FROM #JobsToDisableEnable
OPEN job_cursor
FETCH NEXT FROM job_cursor INTO @job_name,@enable
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC msdb.dbo.sp_update_job @job_name = @job_name, @enabled = 1
FETCH NEXT FROM job_cursor INTO @job_name, @enable
END
CLOSE job_cursor
DEALLOCATE job_cursor
May 20, 2022 at 5:32 pm
The DECLARE for the cursor is incorrect: you need to remove the @ from before "@job_name":
DECLARE job_cursor CURSOR READ_ONLY FOR
SELECT job_name,[enable] --<<--
FROM #JobsToDisableEnable
Also, in the EXEC, you should be using the value from the table not hard-coding 1:
EXEC msdb.dbo.sp_update_job @job_name = @job_name, @enabled = @enable --<<--
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 20, 2022 at 6:40 pm
I noticed that it is using the value from sp_update_job, how would i get it to use the values in the FROM #JobsToDisableEnable? Like its doing enabling all and disabling all, where I just want it to enable or disable on entries within the text file.
May 20, 2022 at 7:41 pm
You have the general structure for that, you just had a typo in the SELECT for the cursor. You had:
DECLARE job_cursor CURSOR READ_ONLY FOR
SELECT @job_name, [enable]
FROM #JobsToDisableEnable
@job_name will contain NULL by default, so you were SELECTing NULL, and using that in the EXEC statement for the job, which is why you got the error about "Supply either @job_id or @job_name to identify the job".
If you correct the cursor to SELECT from the data you read in, you should be OK.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 20, 2022 at 9:01 pm
Here is what I changed it to, but still getting that error.
USE MSDB;
GO
DECLARE @job_name nvarchar(max)
DECLARE @enable int
create table #JobsToDisableEnable
(
job_name nvarchar(max),
enable int
)
DECLARE @Query varchar(1000)
SET @Query ='BULK INSERT #JobsToDisableEnable FROM "G:\SQL_Jobs\DisableEnableJobs\joblist.txt"
WITH(
ROWTERMINATOR = ''\n'',
FIRSTROW = 0
)'
EXEC (@Query)
DECLARE job_cursor CURSOR READ_ONLY FOR
SELECT @job_name,[enable]
FROM #JobsToDisableEnable
OPEN job_cursor
FETCH NEXT FROM job_cursor INTO @job_name,@enable
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC msdb.dbo.sp_update_job @job_name = @job_name, @enabled = @enable
FETCH NEXT FROM job_cursor INTO @job_name, @enable
END
CLOSE job_cursor
DEALLOCATE job_cursor
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply