'Update one step of every SQL Server job
I have a server that has around 90 databases. On this server I have one job per database that cleans some table logs. All jobs are named as DBName_CleanLogs_job. Each job has 5 steps, that are all pretty similar, some of them are identical.
What I need to do is to update all jobs with a script, to override a given step in each job, for example, the step named "delete logs".
I managed to get all the databases names, but I'm not able to include them in the query to CONCAT the DBName with the job name.
Here is an example of what I tried to do.
USE master
GO
Declare @variable table (name nvarchar(128));
DECLARE @cnt INT = 0;
declare @name nvarchar(128);
INSERT INTO @variable (name)
SELECT name FROM sys.databases
WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb')
SELECT * from @variable --check the output
BEGIN
SELECT name = @name
FROM @variable
EXEC msdb.dbo.sp_update_jobstep
@job_name = SELECT CONCAT (@name , '_CleanLogs_job'),
@step_id = 1,
@step_name = N'Delete Table Logs',
@subsystem = N'TSQL',
@command = N'My SQL Command';
END
The error message in this block of code is that the SP is expecting a stepId which was not given.
Thank you in advance for your help!
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
