SQL Server job step output file using job names and tokens

I recently wanted to put output files on all job steps running on all my sql servers. I wanted the output filenames to be unique and I found that you can use tokens to achieve this. However it does not let you use the job name, as I wanted an easy to read filename (not having to look up a guid everytime I am searching for an output file) I came up with the following SQL that will add an output file to every job step in the sysjobsteps tables.

I used a couple of references to get to this (see end of article), and the script runs daily to ensure added jobs also get the outputfile treatment.

The output filename generated by this will be

SQLAGENT_JOB_<jobname>_StepID-<stepID>_<date>_<time>.txt

the actual outputfile string that will appear in the step output file will look something like this (the $() are the SQL tokens that will be replaced with values when the output file is created by SQL Server)

<filelocation>\SQLAGENT_JOB_Daily_Backup_StepID-$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt.

• edit: After running this for a day, it became apparent that I was going to have a mass of files for jobs that run multiple times a day. To alleviate this I have now change the output file to just include the date and not time. Just means I have 1 file for each job step for each day. Please note the difference between the above and the code below. The main code example has also been updated.
• <filelocation>\SQLAGENT_JOB_Daily_Backup_<jobname>_StepID-$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(STRTDT)).txt.

• edit: Ok further edit, I did not notice that the token $(ESCAPE_SQUOTE(STEPID)) was actually inserting only ID value 1 on all steps. I am not sure of the reason as the other tokens are working fine. Any way I redesigned the query to use the <filelocation>\SQLAGENT_JOB_Daily_Backup_<jobname>_StepID-<stepID>_$(ESCAPE_SQUOTE(STRTDT)).txt.
  I include both scripts. I have an inkling that this is something to do with the fact the job started at step 1 and thats the what the stepid holds (the starting step), but thats just a feeling.

Script 1 – with stepID token

DECLARE @output_file_name varchar (2048)
DECLARE @output_file_name_prefix varchar (512)
DECLARE @output_file_name_postfix varchar (512)
DECLARE @job_id varchar(512)
DECLARE @step_id varchar(10)
DECLARE @job_name varchar(255)
DECLARE JobList CURSOR FOR
  SELECT job_id, step_id FROM msdb.dbo.sysjobsteps
OPEN JobList
FETCH NEXT FROM JobList INTO @job_id, @step_id
SET @output_file_name_prefix = '<filelocation>\SQLAGENT_JOB_'
SET @output_file_name_postfix = '_StepID-$(ESCAPE_SQUOTE(STEPID)_$(ESCAPE_SQUOTE(STRTDT)).txt'
WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @job_name = name FROM msdb.dbo.sysjobs WHERE job_id = @job_id
  SET @output_file_name = @output_file_name_prefix + REPLACE(@job_name, ' ', '_') + @output_file_name_postfix
  EXECUTE msdb.dbo.sp_update_jobstep
    @job_id           = @job_id,
    @step_id          = @step_id,
    @output_file_name = @output_file_name,            
    @flags            = 2 -- append to output file 
  FETCH NEXT FROM JobList INTO @job_id, @step_id
END
CLOSE JobList
DEALLOCATE JobList

Script 2 – with stepID token replaced

DECLARE @output_file_name varchar (2048)
DECLARE @output_file_name varchar (2048)
DECLARE @output_file_name_prefix varchar (512)
DECLARE @output_file_name_midfix varchar (512)
DECLARE @output_file_name_postfix varchar (512)
DECLARE @job_id varchar(512)
DECLARE @step_id varchar(10)
DECLARE @job_name varchar(255)
DECLARE JobList CURSOR FOR
  SELECT job_id, step_id FROM msdb.dbo.sysjobsteps
OPEN JobList
FETCH NEXT FROM JobList INTO @job_id, @step_id
SET @output_file_name_prefix = '<file location>\SQLAGENT_JOB_'
SET @output_file_name_midfix = '_StepID-'
SET @output_file_name_postfix = '_$(ESCAPE_SQUOTE(STRTDT)).txt'
WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @job_name = name FROM msdb.dbo.sysjobs WHERE job_id = @job_id
  
  SET @output_file_name = @output_file_name_prefix + REPLACE(@job_name, ' ', '_') + @output_file_name_midfix + @step_id + @output_file_name_postfix
  EXECUTE msdb.dbo.sp_update_jobstep            
        @job_id           = @job_id,            
        @step_id          = @step_id,            
        @output_file_name = @output_file_name,            
        @flags            = 2 -- append to output file 
  FETCH NEXT FROM JobList INTO @job_id, @step_id
END
CLOSE JobList
DEALLOCATE JobList

For information the jobs and jobssteps are held in the MSDB system database in SQL Server in the msdb.dbo.sysjobs and msdb.dbo.sysjobsteps tables.References

As a quick aside to this, in the job I scheduled to run the above script I was included a second step to clear up the output files I created. The command below will delete files over 30 days old. Note: forfiles command is only native on Windows 2003+ server. The SQL job step runs a type Operating system (CmdExec).

forfiles /P "<file location>" /D -30 /C "cmd /c DEL /F /Q @path"

http://ask.sqlservercentral.com/questions/1148/saving-jobs-output-to-a-text-file


http://msdn.microsoft.com/en-us/library/ms175575.aspx


http://support.microsoft.com/kb/111401

SQL Server job step output file using job names and tokens