0
votes

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!

When calling exec you must provide either a variable or a static value, you can't do any logic/manipulation etc. Just create a new variable and concat it before you call exec. Not sure what the point of your begin/end block is either. And for storing table names, use datatype sysname.Dale K
add DECLARE @step_id INT = 1 to your query.RF1991
@DaleK thanks for your reply. I'm thinking with a c# mind, because i'm not so into SQL scripting. The point of the begin/end block was to create a FOREACH statement. Like Foreach DBName in variable, CONCAT the name + _CleanLogs_job. Is it more clear now?Leo Ferreira
You could read the documentation to get clear on how T-SQL works since its quite different to C#. BEGIN/END doesn't create a loop unless you put a WHILE in front of it.Dale K