Uso de tokens en SQL Agent Jobs para renombrar los archivos de salida

En esta publicacion, quiero contaros acerca de una caracteristica bastante util que se puede usar dentro de los trabajos del Agente SQL en SQL Server En esta publicación, quiero contarte sobre un error que me acaba de ocurrir recientemente. Puede que sea un poco tonto, pero aún así puede hacerte perder un rato tratando de descubrir por qué.

Antecedentes

SQL Server viene con su propio planificador de tareas para todas las versiones de pago (lo siento, usuarios Express) que es el Agente SQL. Obviamente, esto es muy útil para llevar a cabo las diferentes tareas de mantenimiento que normalmente realizamos en nuestras bases de datos, como copias de seguridad, mantenimiento de índices y estadísticas, comprobaciones de coherencia y otras que pueden aplicarse a vuestro entorno.

Dentro de un paso de trabajo de SQL Agent, podemos usar algo llamado Tokens, y como podemos ver en BOL.

El uso de tokens cuando escribe los pasos de su trabajo le da la misma flexibilidad que proporcionan las variables cuando escribe programas de software. Después de insertar un token en un script de paso de trabajo, el Agente SQL Server reemplaza el token en tiempo de ejecución, antes de que el subsistema Transact-SQL ejecute el paso de trabajo.

Entonces, de una manera simplificada, son un tipo de variables de entorno que se utilizarán en un paso de trabajo.

No tengo muchos casos de uso para esto, pero al menos tengo uno 🙂

 
Caso de uso

Por lo general, es una buena idea escribir la salida de los trabajos de su Agente SQL en un archivo, para poder investigar si ocurre algún problema.

Pero cuando definimos el archivo de salida, necesitamos elegir entre Anexar el resultado al mismo archivo una y otra vez, o sobrescribirlo, pero tanto una cosa como la otra frustra el propósito en mi humilde opinión.

Por otro lado, si te olvidas de rotar los archivos, pueden crecer bastante y luego encontrar cualquier error puede convertirse en una pesadilla. Si lo sobreescribimos, y no analizamos antes de que ocurra, el error se pierde.

Entonces, hace algún tiempo, escribí un procedimiento almacenado que rota los archivos por mí y los ordena para que sea fácil encontrar una fecha en particular.

 
El truco

El siguiente procedimiento almacenado hará el truco, busca los archivos de salida para cada uno de los pasos, cambia el nombre y los mueve a una subcarpeta para se puedan encontrar fácilmente.

Obviamente, la estructura de las carpetas es como a mi me gusta ./Jobs/aaaa/mm/jobname/, pero puedes modificarla para adaptarla a tus necesidades, igual prefieres ./Jobs/jobname/aaaa/mm, así que eso depende de ti.

Si lo dejas como está, y tienes un trabajo como este

 
Entonces obtendrás algo como esto.

 
Si te gusta, aquí está el Procedimiento almacenado, para que puedas copiar pegar y que empiece la diversión.

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://sqldoubleg.live-website.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

 
Lo último es agregar una llamada a este procedimiento almacenado en sus diferentes trabajos como paso final; la llamada se encuentra en la sección del encabezado, pero sería una cosa así.

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

 
El gotcha!

¡Casi me olvido! Todo esto vino por una razón, el uso de tokens. Sí, no hay mucha chicha usando tokens, puedes consultar libros online para ver cuales están disponibles, yo solo uso JOBID en mi último paso de trabajo.

La historia es que tengo scripts para crear trabajos para nuevos servidores y trato de usar SQLCMD para hacerlo más versatil, así no tengo que molestarme en conectarme al servidor correcto, el script lo hará por mí.

Pero al crear un trabajo, me salté este error.

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

Este error es bastante molesto, porque no podía entender por qué, pero parece que a SQLCMD no le gustan mucho los tokens, ya que usan la misma estructura que las variables en SQLCMD $(variable).

No puedo encontrar ninguna referencia en BOL, pero solo en caso de que os surja el mismo error, simplemente ejecutar ese script localmente, ¿o quizás PowerShell funcionaría? Ya me diréis si lo podeis hacer.

 
¡Gracias por leer!