The msdb database contains a stored procedure that allows modifications to job steps in a wide variety of ways, including the ability to modify only the output properties.
The following code will output the commands required to modify all job steps that have no "output file" specified, such that job step output will be written into C:\temp\job_name_here.txt. Output will be overwritten each time the job step runs.
USE msdb;
DECLARE @OutputPath nvarchar(260);
DECLARE @cmd nvarchar(max);
SET @OutputPath = N'C:\temp';
DECLARE @msg nvarchar(1000);
SET @msg = N'
/*
flags:
0 - overwrite output file
2 - append to output file
4 - Write T-SQL output to step history in MSDB
8 - Write log to table (overwrite existing)
16 - Write log to table (append)
*/
';
PRINT @msg;
DECLARE cur CURSOR LOCAL FORWARD_ONLY LOCAL
FOR
SELECT N'EXEC msdb.dbo.sp_update_jobstep @job_id = N''{' + CONVERT(varchar(50), (sj.job_id)) + N'}''
, @step_id = ' + CONVERT(nvarchar(30), sjs.step_id) + '
, @output_file_name = N''' + @OutputPath + N'\' + sj.name + N'_Step_' + CONVERT(nvarchar(10), sjs.step_id) + N'.txt''
, @flags = 0;
'
FROM dbo.sysjobs sj
INNER JOIN dbo.sysjobsteps sjs ON sj.job_id = sjs.job_id
WHERE sjs.output_file_name IS NULL OR sjs.output_file_name = ''
ORDER BY sj.name
, sjs.step_id;
OPEN cur;
FETCH NEXT FROM cur INTO @cmd;
WHILE @@FETCH_STATUS = 0
BEGIN
IF LEN(@cmd)>4000
BEGIN
SET @msg = 'Output of the next command is truncated.';
RAISERROR (@msg, 14, 1);
END
PRINT @cmd;
--EXEC sys.sp_executesql @cmd; --uncomment this line to actually run the command
FETCH NEXT FROM cur INTO @cmd;
END
CLOSE cur;
DEALLOCATE cur;
You could run this against multiple servers by creating a local server group in SSMS using the "Registered Servers" functionality, adding the desired servers to that group, then executing a query against the group.
