I have an server that has like 90 databases. In this server I have one job per database, that cleans some table logs. All jobs are named like DBName_CleanLogs_job
.
each Job have 5 steps, that are pretty smiliar, 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 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!
sysname
. – Dale KWHILE
in front of it. – Dale K