Microsoft KB Archive/915845

= SQL Server Agent jobs fail when the jobs contain job steps that use tokens after you install SQL Server 2005 Service Pack 1 =

Article ID: 915845

Article Last Modified on 5/24/2006

-

APPLIES TO


 * Microsoft SQL Server 2005 Standard Edition
 * Microsoft SQL Server 2005 Workgroup Edition
 * Microsoft SQL Server 2005 Enterprise Edition

-



Bug #: 426808 (SQLBUDT)



SYMPTOMS
After you install Microsoft SQL Server 2005 Service Pack 1 (SP1), you experience the following behavior:  SQL Server Agent jobs fail when the jobs contain job steps that use tokens. You receive the following error message:

The job step contains one or more tokens. For SQL Server 2005 Service Pack 1 or later versions, all job steps with tokens must be updated with a macro before the job can run.



Note This problem occurs with build 2046 or later versions of SQL Server 2005.



CAUSE
In SQL Server 2005 SP1, the SQL Server Agent job step token syntax has changed. Now, you must include an escape macro with all tokens that are used in job steps. If you do not include an escape macro, those job steps will fail. The following table lists the escape macros.

For example, a job step may contain the following Transact-SQL statement that uses the A-DBN token: CREATE DATABASE [$(A-DBN)] In this example, you must update the token syntax to the following syntax: CREATE DATABASE [$(ESCAPE_RBRACKET(A-DBN))] This change differs from previous SQL Server 2005 behavior, where escape macros were not required.



RESOLUTION
To resolve this problem, update either all or only specific jobs that use tokens to the new token syntax. To do this, use the sp_AddEscapeNoneToJobStepTokens stored procedure. You can create this stored procedure by using the following Transact-SQL script.

Note Make sure that the SQL Server 2005 SP1 build that you install is build 2046 or a later build. Additionally, you must be a member of the sysadmin fixed server role to run the script. -- This script is used to automatically edit SQL Agent job steps so that -- unescaped tokens are prefaced with the ESCAPE_NONE syntax that was added in -- SQL Server 2005 SP1.

if (@@microsoftversion < 0x90007FE) BEGIN RAISERROR('This script should only be run on at least SQL Server 2005 SP1.', 20, 127) WITH LOG return END

use msdb go

if exists (select * from sys.objects where name = N'fn_PrefaceTokensWithEscapeNone' and type = 'FN') drop function fn_PrefaceTokensWithEscapeNone go

-- This function manipulates @commands so that all bare tokens -- are prefaced with ESCAPE_NONE. create function fn_PrefaceTokensWithEscapeNone(@commands nvarchar(max)) RETURNS nvarchar(max) AS BEGIN if (@commands IS NULL) BEGIN return @commands END

-- In order to let this script run under SQLCMD mode, we define -- the special &quot;$(&quot; variable start string by concatenation so that   -- sqlcmd mode does not think that we are defining one of its variables.    declare @strVariableStart nchar(2)    select @strVariableStart = N'$' + N'('

declare @idxTokenStart int select @idxTokenStart = CHARINDEX(@strVariableStart, @commands) while (@idxTokenStart != 0 and @idxTokenStart is not null) BEGIN declare @idxCloseParen int select @idxCloseParen = CHARINDEX(N')', SUBSTRING(@commands, @idxTokenStart, LEN(@commands)))       -- Error checking. If there is no close parenthesis, return.        if (0 = @idxCloseParen)        BEGIN            return @commands        END

-- Deduce the token variable. declare @tokenLen int select @tokenLen = @idxCloseParen - LEN(@strVariableStart) - 1 declare @token nvarchar(max) select @token = SUBSTRING(@commands, @idxTokenStart + LEN(@strVariableStart), @tokenLen)

-- Verify if @token contains a mis-matched number of open and -- close parens. This behavior could happen if invalid syntax is       -- in a comment block. If so, skip to the next token. declare @idx int declare @cOpenParens int declare @cCloseParens int

select @cOpenParens = 0 select @idx = CHARINDEX(N'(', @token);       while (@idx != 0)        BEGIN            select @cOpenParens = @cOpenParens + 1            select @idx = CHARINDEX(N'(', @token, @idx + 1); END

select @cCloseParens = 0 select @idx = CHARINDEX(N')', @token);       while (@idx != 0)        BEGIN            select @cCloseParens = @cCloseParens + 1            select @idx = CHARINDEX(N')', @token, @idx + 1); END

-- Special case for the WMI token. if (N'WMI(' = SUBSTRING(@token, 1, LEN(N'WMI('))) BEGIN select @cOpenParens = @cOpenParens - 1 END

if ((@cOpenParens = @cCloseParens) and           (N'ESCAPE_NONE(' != SUBSTRING(@token, 1, LEN(N'ESCAPE_NONE('))) and            (N'ESCAPE_SQUOTE(' != SUBSTRING(@token, 1, LEN(N'ESCAPE_SQUOTE('))) and            (N'ESCAPE_DQUOTE(' != SUBSTRING(@token, 1, LEN(N'ESCAPE_DQUOTE('))) and            (N'ESCAPE_RBRACKET(' != SUBSTRING(@token, 1, LEN(N'ESCAPE_RBRACKET(')))) BEGIN select @commands = STUFF(@commands, @idxTokenStart + LEN(@strVariableStart), @tokenLen, N'ESCAPE_NONE(' + @token + N')') END select @idxTokenStart = CHARINDEX(@strVariableStart, @commands, @idxTokenStart + 1) END

return @commands

END go

if exists (select * from sys.objects where name = N'sp_AddEscapeNoneToJobStepTokens' and type = 'P') drop procedure sp_AddEscapeNoneToJobStepTokens go

-- This procedure allows you to update jobs so that bare tokens -- are prefaced with ESCAPE_NONE. By default, all jobs are updated. -- You can optionally specify @job_name, @job_id, or @owner_name -- to limit the jobs that will be affected. CREATE PROCEDURE sp_AddEscapeNoneToJobStepTokens (   @job_name nvarchar(128) = null,    @job_id uniqueidentifier = null,    @owner_name nvarchar(256) = null ) AS

-- Find the jobs to update. These jobs must match all of the input -- criteria, unless all of the inputs are null. In this case, -- examine all jobs. The jobs must also be jobs created locally, -- such as sysjobs.originating_server_id = 0. These jobs should not be a job that we run -- because another server told us to. Furthermore, if the job -- is local but it is meant to be run on a target server, we send an -- update for the job. declare @jobsToUpdate TABLE (job_id uniqueidentifier not null) insert into @jobsToUpdate select job_id from sysjobs where originating_server_id = 0 -- local jobs and ((COALESCE(@job_name, sysjobs.name) = sysjobs.name) and          (COALESCE(@job_id, sysjobs.job_id) = sysjobs.job_id) and           (COALESCE(@owner_name, suser_sname(sysjobs.owner_sid)) = suser_sname(sysjobs.owner_sid))) -- Now find the job steps to update, creating the new command by using -- fn_PrefaceTokensWithEscapeNone. declare @jobStepsToUpdate TABLE (job_id uniqueidentifier not null,                                   step_id int not null,                                    command_old nvarchar(max) null,                                    command_new nvarchar(max) null,                                   output_file_old nvarchar(max) null,                                    output_file_new nvarchar(max) null) insert into @jobStepsToUpdate (job_id, step_id, command_old, command_new, output_file_old, output_file_new) select job_id, step_id, command, dbo.fn_PrefaceTokensWithEscapeNone(command), output_file_name, dbo.fn_PrefaceTokensWithEscapeNone(output_file_name) from sysjobsteps where sysjobsteps.job_id = (select job_id       from @jobsToUpdate        where job_id = sysjobsteps.job_id) -- Now we update the actual job step commands. We do this first before -- we push out the updated jobs to the target servers so the -- target servers actually get the updated version. declare @updated_job_id uniqueidentifier declare @updated_job_step_id int declare @updated_job_step_command nvarchar(max) declare @updated_job_step_output_file nvarchar(max) declare job_steps_cursor CURSOR FOR select job_id, step_id, command_new, output_file_new from @jobStepsToUpdate order by job_id, step_id OPEN job_steps_cursor FETCH NEXT from job_steps_cursor into @updated_job_id, @updated_job_step_id, @updated_job_step_command, @updated_job_step_output_file WHILE (@@FETCH_STATUS <> -1) BEGIN IF (@@FETCH_STATUS <> -2) BEGIN EXEC sp_update_jobstep @job_id = @updated_job_id, @step_id = @updated_job_step_id, @command = @updated_job_step_command, @output_file_name = @updated_job_step_output_file END FETCH NEXT from job_steps_cursor into @updated_job_id, @updated_job_step_id, @updated_job_step_command, @updated_job_step_output_file END CLOSE job_steps_cursor DEALLOCATE job_steps_cursor -- For multiserver jobs, call the sp_post_msx_operation stored procedure to update -- all the target servers. Note that the sp_post_msx_operation stored procedure is safe -- to call because it verifies whether the job is really a multiserver job. declare jobs_cursor CURSOR FOR select job_id from @jobsToUpdate OPEN jobs_cursor FETCH NEXT from jobs_cursor into @updated_job_id WHILE (@@FETCH_STATUS <> -1) BEGIN IF (@@FETCH_STATUS <> -2) BEGIN EXEC sp_post_msx_operation @operation = 'UPDATE', @job_id = @updated_job_id END FETCH NEXT from jobs_cursor into @updated_job_id END CLOSE jobs_cursor DEALLOCATE jobs_cursor

-- List the jobs that we ran on, including the previous command -- text. We list all of the job steps, even the ones that we did not -- update. Otherwise, a jumble of job steps from -- different jobs run together and the output is not -- useful. select N'Warning - Jobs Updated' = N'The following job steps and job output file names were analyzed and potentially updated to add the ESCAPE_NONE macro before any job tokens that were not already escaped. Please review the modified job steps and replace ESCAPE_NONE with the correct escape macro.' select suser_sname(jobs.owner_sid) as N'Job owner', jobs.name as N'Job name', jobs.job_id, jobStepsUpdated.step_id, N'Modified' = CASE WHEN jobStepsUpdated.command_new != jobStepsUpdated.command_old or jobStepsUpdated.output_file_new != jobStepsUpdated.output_file_old THEN 1 ELSE 0 END, N'Command' = jobStepsUpdated.command_new, N'Previous Command' = jobStepsUpdated.command_old, N'Output file' = jobStepsUpdated.output_file_new, N'Previous Output file' = jobStepsUpdated.output_file_old from sysjobs as jobs, @jobsToUpdate as jobsUpdated, @jobStepsToUpdate as jobStepsUpdated where jobsUpdated.job_id = jobs.job_id and jobsUpdated.job_id = jobStepsUpdated.job_id order by 'Job name', jobStepsUpdated.step_id go After you run the script, the sp_AddEscapeNoneToJobStepTokens stored procedure is created. By default, you will update all jobs if you run this stored procedure without any parameters. If you want to update only specific jobs, you must specify non-null values for at least one of the following three parameters:
 * @job_name
 * @job_id
 * @owner_name

For example, you may use the following syntax:   Update all jobs: EXEC sp_AddEscapeNoneToJobStepTokens   Update a job by specifying the job name: EXEC sp_AddEscapeNoneToJobStepTokens 'MyJob'   Update jobs that are owned by the same owner: EXEC sp_AddEscapeNoneToJobStepTokens null,null,'JobOwner' </li></ul>

This script adds the ESCAPE_NONE macro to all job steps that contain tokens. After you run this script, we recommend that you review your job steps that use tokens as soon as possible. Then, replace the ESCAPE_NONE macro with one of the other escape macros that is appropriate for the job step context.

Note If you are working in a master server (MSX) and target server (TSX) environment, you must run the this script on both the MSX and the TSX to make sure that master jobs on the TSX are correctly updated.

For more information about how to update jobs to use the new syntax and how to use escape macros to enable token replacement in SQL Server Agent job steps, see the &quot;Using Tokens in Job Steps&quot; topic in SQL Server 2005 Books Online (April 2006) or later versions of SQL Server 2005 Books Online.

<div class="status_section">

STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the &quot;Applies to&quot; section.

<div class="references_section">