Using tokens in SQL Agent Jobs and renaming output files

In this post, I want to tell you about some useful feature that can be used within SQL Agent jobs in SQL Server This post I want to tell you about an error that just happened to me recently. It’s very silly, but still can make you waste some time trying to find out why.

Background

SQL Server comes with its own tasks scheduler for all paid versions (sorry, Express users) which is the SQL Agent. Obviously this comes very handy to do the different maintenance we normally undertake on our databases like backups, index and stats maintenance, consistency checks and other that can apply to your environment.

Inside a SQL Agent job step, we can use something called Tokens, and as BOL states

Using tokens when you write your job steps gives you the same flexibility that variables provide when you write software programs. After you insert a token in a job step script, SQL Server Agent replaces the token at run time, before the job step is executed by the Transact-SQL subsystem.

So in a simplified way, they are a sort of environment variables to be used within a job step.

I don’t have many use cases for this, but at least I have one 🙂

 
Use case

It’s usually a good idea to write the output of your SQL Agent jobs to a file, so you can investigate should any issue occur.

But when you define the output file, you need to choose between Appending the output to the same file over and over, or to overwrite it, but that defeats the purpose IMHO.

On the other hand, if you forget to roll over the files, they can grow quite large and then finding any error can become a nightmare.

So some time ago, I wrote a stored procedure that rolls the files for me and place them sorted so it’s easy to find any particular date.

 
The hack

The following stored procedure will do the trick for you, it will look at the output files for each of the steps and rename and place them within a subfolder so you can find them easily.

Obviously the structure of folders is how I like it ./Jobs/yyyy/mm/jobname/, but feel free to tweak it to suit your needs, maybe you prefer ./Jobs/jobname/yyyy/mm so that’s up to you.

If you leave it like it is, and you have a job like this

 
Then you will get something like this.

 
If you liked what you’ve seen, then here is the Stored Procedure, so you can copy paste and start having fun.

USE [master]
GO
IF OBJECT_ID('dbo.sqlg_renameJobLogFiles') IS NULL EXECUTE sp_executesql N'CREATE PROCEDURE dbo.sqlg_renameJobLogFiles AS RETURN';
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Raul Gonzalez @SQLDoubleG
-- Create date: 17/09/2013
-- Description: Rename Log Files for the specified job and place them into a folder 
--              with the following pattern
--                  [Drive:\Current Path]\Jobs\yyyy\mm\Step-N-StepName_yyyymmdd[_hhmiss].log
--
-- Usage:
--              Include the following call as the last step of a job
--
--              USE [master]
--              GO
--              -- tokens can only be used within job steps
--              DECLARE @job_id uniqueidentifier = CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID)))
--              DECLARE @includeTime bit = 1
--              DECLARE @debugging bit = 0
 
--              EXECUTE [dbo].[sqlg_renameJobLogFiles] 
--                  @job_id         = @job_id
--                  , @includeTime  = @includeTime
--                  , @debugging    = @debugging
--              GO
--
-- Asumptions:  Each step outputs into a different file
--
-- Change Log:  30/03/2016  RAG - Added parameter @includeTime to add _hhmmss to the output filename.
--
-- Copyright:   (C) 2017 Raul Gonzalez (@SQLDoubleG https://www.sqldoubleg.com)
--
--              THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
--              ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
--              TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
--              PARTICULAR PURPOSE.
--
--              THE AUTHOR SHALL NOT BE LIABLE TO YOU OR ANY THIRD PARTY FOR ANY INDIRECT, 
--              SPECIAL, INCIDENTAL, PUNITIVE, COVER, OR CONSEQUENTIAL DAMAGES OF ANY KIND
--
--              YOU MAY ALTER THIS CODE FOR YOUR OWN *NON-COMMERCIAL* PURPOSES. YOU MAY
--              REPUBLISH ALTERED CODE AS LONG AS YOU INCLUDE THIS COPYRIGHT AND GIVE DUE CREDIT. 
--
-- =============================================
ALTER PROCEDURE [dbo].[sqlg_renameJobLogFiles] 
    @job_id         UNIQUEIDENTIFIER
    , @includeTime  BIT = 0
    , @debugging    BIT = 0
AS
BEGIN
 
    SET NOCOUNT ON
 
    DECLARE @outputFiles TABLE (ID                  INT IDENTITY
                                , job_name          SYSNAME
                                , step_no           INT
                                , step_name         SYSNAME
                                , output_file_name  NVARCHAR(512) NOT NULL
                                , only_file_name	NVARCHAR(512) NOT NULL)
 
    DECLARE @numFiles           INT
            , @countFiles       INT = 1
            , @job_name         SYSNAME
            , @output_file_name NVARCHAR(512)
            , @new_path         NVARCHAR(512)
            , @new_file_name    NVARCHAR(512)
            , @year             VARCHAR(4) = DATEPART(YEAR,GETDATE())
            , @month            VARCHAR(2) = RIGHT('00' + CONVERT(VARCHAR,DATEPART(MONTH,GETDATE())), 2)
            , @day              VARCHAR(2) = RIGHT('00' + CONVERT(VARCHAR,DATEPART(DAY,GETDATE())), 2)
            , @time             VARCHAR(6) = REPLACE(CONVERT(VARCHAR,GETDATE(), 108), ':', '')
            , @dirCmd           NVARCHAR(2000)
            , @mvCmd            NVARCHAR(2000)
 
    INSERT INTO @outputFiles
    SELECT TOP 100 PERCENT
            j.name
            , js.step_id
            , js.step_name
            , output_file_name 
  			, RIGHT( output_file_name, ( CASE WHEN CHARINDEX( CHAR(92), REVERSE(output_file_name) ) > 0 
											THEN CHARINDEX( CHAR(92), REVERSE(output_file_name) ) - 1 
											ELSE LEN(output_file_name) 
										END ) ) AS only_file_name

        FROM msdb.dbo.sysjobs AS j
            INNER JOIN msdb.dbo.sysjobsteps AS js
                ON js.job_id = j.job_id
        WHERE j.job_id = @job_id
            AND output_file_name IS NOT NULL
        ORDER BY js.step_id
 
    SET @numFiles = @@ROWCOUNT
 
    WHILE @countFiles <= @numFiles BEGIN
     
        SELECT @output_file_name = output_file_name
                , @new_file_name = 'Step-' + CONVERT(VARCHAR,step_no) + '-' + step_name
                , @new_path = REPLACE(output_file_name, only_file_name, '')
                                    + 'Jobs' + CHAR(92) + @year + CHAR(92) + @month + CHAR(92) + job_name + CHAR(92)
                , @job_name = job_name
            FROM @outputFiles
            WHERE ID = @countFiles
 
        -- Create folder if does not exist
        SET @dirCmd = 'EXEC master..xp_cmdshell ''if not exist "' + @new_path + '". md "' + @new_path + '".'''
        SET @mvCmd  = 'EXEC master..xp_cmdshell ''move "' + @output_file_name + '" "'
                            + @new_path + @new_file_name + '_' +  @year + @month + @day
                            + CASE WHEN @includeTime = 1 THEN '_' + @time ELSE '' END + '.log"'''
     
        PRINT @dirCmd
        PRINT @mvCmd
 
        IF ISNULL(@debugging, 0) = 0 BEGIN 
            EXEC sp_executesql @dirCmd
            EXEC sp_executesql @mvCmd
        END
 
        SET @countFiles += 1
    END
END
GO

 
The final thing is to add a call to this stored procedure within your different jobs as the final step, the call is in the header section but it’d be like this

USE [master]
GO
-- tokens can only be used within job steps
DECLARE @job_id uniqueidentifier = CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID)))
DECLARE @includeTime bit = 1
DECLARE @debugging bit = 0

EXECUTE [dbo].[sqlg_renameJobLogFiles] 
	@job_id			= @job_id
	, @includeTime	= @includeTime
	, @debugging	= @debugging
GO

 
The gotcha!

Almost forgot! All this came for a reason, the use of tokens. Yes, there is no much juice using tokens, you can check books online to see what is available, I just use JOBID in my final job step.

The thing is that I have scripts to create jobs for new servers and I try to use SQLCMD to make it easier so I don’t have to bother connecting to the right server, the script will do it for me.

But when creating a job I come to this error.

/*
A fatal scripting error occurred.
Variable JOBID is not defined.
*/

And that was very annoying, because I couldn’t understand why, but seems like SQLCMD does not like tokens very much.

I can’t find any reference in BOL, but just in case you come to the same error, just run that bit locally, or maybe powershell would work? I let you try that.

Thanks for reading!