![\"\"](\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2017\/10\/01_folder_structure.png\")
<\/a><\/p>\n
\nSi te gusta, aqu\u00ed est\u00e1 el Procedimiento almacenado, para que puedas copiar pegar y que empiece la diversi\u00f3n.<\/p>\n
<\/p>\n
\r\nUSE [master]\r\nGO\r\nIF OBJECT_ID('dbo.sqlg_renameJobLogFiles') IS NULL EXECUTE sp_executesql N'CREATE PROCEDURE dbo.sqlg_renameJobLogFiles AS RETURN';\r\nGO\r\nSET ANSI_NULLS ON\r\nGO\r\nSET QUOTED_IDENTIFIER ON\r\nGO\r\n-- =============================================\r\n-- Author: Raul Gonzalez @SQLDoubleG\r\n-- Create date: 17\/09\/2013\r\n-- Description: Rename Log Files for the specified job and place them into a folder \r\n-- with the following pattern\r\n-- [Drive:\\Current Path]\\Jobs\\yyyy\\mm\\Step-N-StepName_yyyymmdd[_hhmiss].log\r\n--\r\n-- Usage:\r\n-- Include the following call as the last step of a job\r\n--\r\n-- USE [master]\r\n-- GO\r\n-- -- tokens can only be used within job steps\r\n-- DECLARE @job_id uniqueidentifier = CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID)))\r\n-- DECLARE @includeTime bit = 1\r\n-- DECLARE @debugging bit = 0\r\n \r\n-- EXECUTE [dbo].[sqlg_renameJobLogFiles] \r\n-- @job_id = @job_id\r\n-- , @includeTime = @includeTime\r\n-- , @debugging = @debugging\r\n-- GO\r\n--\r\n-- Asumptions: Each step outputs into a different file\r\n--\r\n-- Change Log: 30\/03\/2016 RAG - Added parameter @includeTime to add _hhmmss to the output filename.\r\n--\r\n-- Copyright: (C) 2017 Raul Gonzalez (@SQLDoubleG https:\/\/sqldoubleg.live-website.com)\r\n--\r\n-- THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF \r\n-- ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED \r\n-- TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND\/OR FITNESS FOR A\r\n-- PARTICULAR PURPOSE.\r\n--\r\n-- THE AUTHOR SHALL NOT BE LIABLE TO YOU OR ANY THIRD PARTY FOR ANY INDIRECT, \r\n-- SPECIAL, INCIDENTAL, PUNITIVE, COVER, OR CONSEQUENTIAL DAMAGES OF ANY KIND\r\n--\r\n-- YOU MAY ALTER THIS CODE FOR YOUR OWN *NON-COMMERCIAL* PURPOSES. YOU MAY\r\n-- REPUBLISH ALTERED CODE AS LONG AS YOU INCLUDE THIS COPYRIGHT AND GIVE DUE CREDIT. \r\n--\r\n-- =============================================\r\nALTER PROCEDURE [dbo].[sqlg_renameJobLogFiles] \r\n @job_id UNIQUEIDENTIFIER\r\n , @includeTime BIT = 0\r\n , @debugging BIT = 0\r\nAS\r\nBEGIN\r\n \r\n SET NOCOUNT ON\r\n \r\n DECLARE @outputFiles TABLE (ID INT IDENTITY\r\n , job_name SYSNAME\r\n , step_no INT\r\n , step_name SYSNAME\r\n , output_file_name NVARCHAR(512) NOT NULL\r\n , only_file_name\tNVARCHAR(512) NOT NULL)\r\n \r\n DECLARE @numFiles INT\r\n , @countFiles INT = 1\r\n , @job_name SYSNAME\r\n , @output_file_name NVARCHAR(512)\r\n , @new_path NVARCHAR(512)\r\n , @new_file_name NVARCHAR(512)\r\n , @year VARCHAR(4) = DATEPART(YEAR,GETDATE())\r\n , @month VARCHAR(2) = RIGHT('00' + CONVERT(VARCHAR,DATEPART(MONTH,GETDATE())), 2)\r\n , @day VARCHAR(2) = RIGHT('00' + CONVERT(VARCHAR,DATEPART(DAY,GETDATE())), 2)\r\n , @time VARCHAR(6) = REPLACE(CONVERT(VARCHAR,GETDATE(), 108), ':', '')\r\n , @dirCmd NVARCHAR(2000)\r\n , @mvCmd NVARCHAR(2000)\r\n \r\n INSERT INTO @outputFiles\r\n SELECT TOP 100 PERCENT\r\n j.name\r\n , js.step_id\r\n , js.step_name\r\n , output_file_name \r\n \t\t\t, RIGHT( output_file_name, ( CASE WHEN CHARINDEX( CHAR(92), REVERSE(output_file_name) ) > 0 \r\n\t\t\t\t\t\t\t\t\t\t\tTHEN CHARINDEX( CHAR(92), REVERSE(output_file_name) ) - 1 \r\n\t\t\t\t\t\t\t\t\t\t\tELSE LEN(output_file_name) \r\n\t\t\t\t\t\t\t\t\t\tEND ) ) AS only_file_name\r\n\r\n FROM msdb.dbo.sysjobs AS j\r\n INNER JOIN msdb.dbo.sysjobsteps AS js\r\n ON js.job_id = j.job_id\r\n WHERE j.job_id = @job_id\r\n AND output_file_name IS NOT NULL\r\n ORDER BY js.step_id\r\n \r\n SET @numFiles = @@ROWCOUNT\r\n \r\n WHILE @countFiles <= @numFiles BEGIN\r\n \r\n SELECT @output_file_name = output_file_name\r\n , @new_file_name = 'Step-' + CONVERT(VARCHAR,step_no) + '-' + step_name\r\n , @new_path = REPLACE(output_file_name, only_file_name, '')\r\n + 'Jobs' + CHAR(92) + @year + CHAR(92) + @month + CHAR(92) + job_name + CHAR(92)\r\n , @job_name = job_name\r\n FROM @outputFiles\r\n WHERE ID = @countFiles\r\n \r\n -- Create folder if does not exist\r\n SET @dirCmd = 'EXEC master..xp_cmdshell ''if not exist "' + @new_path + '". md "' + @new_path + '".'''\r\n SET @mvCmd = 'EXEC master..xp_cmdshell ''move "' + @output_file_name + '" "'\r\n + @new_path + @new_file_name + '_' + @year + @month + @day\r\n + CASE WHEN @includeTime = 1 THEN '_' + @time ELSE '' END + '.log"'''\r\n \r\n PRINT @dirCmd\r\n PRINT @mvCmd\r\n \r\n IF ISNULL(@debugging, 0) = 0 BEGIN \r\n EXEC sp_executesql @dirCmd\r\n EXEC sp_executesql @mvCmd\r\n END\r\n \r\n SET @countFiles += 1\r\n END\r\nEND\r\nGO\r\n\r\n<\/pre>\n
\nLo \u00faltimo es agregar una llamada a este procedimiento almacenado en sus diferentes trabajos como paso final; la llamada se encuentra en la secci\u00f3n del encabezado, pero ser\u00eda una cosa as\u00ed.<\/p>\n
\r\n\r\nUSE [master]\r\nGO\r\n-- tokens can only be used within job steps\r\nDECLARE @job_id uniqueidentifier = CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID)))\r\nDECLARE @includeTime bit = 1\r\nDECLARE @debugging bit = 0\r\n\r\nEXECUTE [dbo].[sqlg_renameJobLogFiles] \r\n\t@job_id\t\t\t= @job_id\r\n\t, @includeTime\t= @includeTime\r\n\t, @debugging\t= @debugging\r\nGO\r\n<\/pre>\n
\nEl gotcha!<\/strong><\/p>\n\u00a1Casi me olvido! Todo esto vino por una raz\u00f3n, el uso de tokens. S\u00ed, no hay mucha chicha usando tokens, puedes consultar libros online para ver cuales est\u00e1n disponibles, yo solo uso JOBID en mi \u00faltimo paso de trabajo.<\/p>\n
La historia es que tengo scripts para crear trabajos para nuevos servidores y trato de usar SQLCMD para hacerlo m\u00e1s versatil, as\u00ed no tengo que molestarme en conectarme al servidor correcto, el script lo har\u00e1 por m\u00ed.<\/p>\n
Pero al crear un trabajo, me salt\u00e9 este error.<\/p>\n
\r\n\/*\r\nA fatal scripting error occurred.\r\nVariable JOBID is not defined.\r\n*\/\r\n<\/pre>\nEste error es bastante molesto, porque no pod\u00eda entender por qu\u00e9, pero parece que a SQLCMD no le gustan mucho los tokens, ya que usan la misma estructura que las variables en SQLCMD $(variable).<\/p>\n
No puedo encontrar ninguna referencia en BOL, pero solo en caso de que os surja el mismo error, simplemente ejecutar ese script localmente, \u00bfo quiz\u00e1s PowerShell funcionar\u00eda? Ya me dir\u00e9is si lo podeis hacer.<\/p>\n
\n\u00a1Gracias por leer!
\n<\/p>","protected":false},"excerpt":{"rendered":"
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\u00f3n, quiero contarte sobre un error…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,19],"tags":[81],"_links":{"self":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/454"}],"collection":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/comments?post=454"}],"version-history":[{"count":0,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/454\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/media?parent=454"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/categories?post=454"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/tags?post=454"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"\"](\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2017\/10\/02_sample_job.png\")
<\/a><\/p>\n