If you create a new SQL Agent job step with tokens in output file name using the stored procedure sp_add_jobstep or want to add a filename with tokens to an existing job step using the stored procedure sp_update_jobstep you will get a error like
A fatal scripting error occurred.
Variable SQLLOGDIR is not defined.
In this case I was trying to use the token SQLLOGDIR, but it does not matter which token.
Many users of Ola Hallengren (LinkedIn) Maintenance Solution (link) had similar errors. Actually Ola is aware of the challenge - he just forgot to promote his solution ;-)
When you use the stored procedure sp_add_jobstep you with that call the internal stored procedure sp_add_jobstep_internal. You can see the definition of both procedures in the system database msdb with SQL Server Management Studio (SSMS).
The root-challenge is that somewhere along the path from calling sp_add_jobstep to setting the output file name on the job step the content of the parameter is interpreted. But at that point the execution is outside SQL Agent, and the tokens are not available.
The trick - that Ola forgot to promote - is to build a string with each token element seperated.
DECLARE @_token_log_directory NVARCHAR(MAX) = N'$' + N'(ESCAPE_SQUOTE(SQLLOGDIR))';
DECLARE @_token_job_name NVARCHAR(MAX) = N'$' + N'(ESCAPE_SQUOTE(JOBNAME))';
DECLARE @_token_date NVARCHAR(MAX) = N'$' + N'(ESCAPE_SQUOTE(DATE))';
DECLARE @_token_time NVARCHAR(MAX) = N'$' + N'(ESCAPE_SQUOTE(TIME))';
DECLARE @_output_file_name NVARCHAR(MAX) = @_token_log_directory + N'\' + @_token_job_name + N'_' + @_token_date + N'_' + @_token_time + N'.txt';
This string can then be used as parameter value to the parameter @output_file_name in the stored procedures to a SQL Agent job step.
EXECUTE msdb.dbo.sp_update_jobstep @job_name=N'IndexOptimize - Db1.Schema1.Tbl1',
@step_id=1,
@output_file_name= @_output_file_name;
If you dig into Olas code to install the maintenance solution and search for the string „@TokenLogDirectory“ you will see that Ola build this string element by element. With careful seperation of $-sign and token names.
Posts
