Bug #:426808(SQLBUDT)

症状

安装 Microsoft SQL Server 2005 Service Pack 1 (SP1)后,你会遇到以下行为:

  • 当作业包含使用令牌的作业步骤时,SQL Server 代理作业失败。

  • 您收到以下错误消息:

    作业步骤包含一个或多个令牌。 对于 SQL Server 2005 Service Pack 1 或更高版本,必须先使用宏更新带有令牌的所有作业步骤,然后才能运行作业。

注意 SQL Server 2005 的内部版本2046或更高版本会出现此问题。

原因

在 SQL Server 2005 SP1 中,SQL Server 代理作业步骤令牌语法已更改。 现在,你必须包含一个转义宏和作业步骤中使用的所有令牌。 如果不包含转义宏,这些作业步骤将失败。 下表列出了转义宏。

转义宏

说明

$(ESCAPE_SQUOTE(TokenName))

此宏对令牌替换字符串中的撇号(')进行转义。 宏将用两个撇号替换一个撇号。

$(ESCAPE_DQUOTE(TokenName))

此宏将在标记替换字符串中转义引号(")。 宏会将一个引号替换为两个引号。

$(ESCAPE_RBRACKET(TokenName))

此宏在标记替换字符串中转义右方括号(])。 宏将一个右括号替换为两个右括号。

$ (ESCAPE_NONE (TokenName))

宏将替换标记,而不会转义字符串中的任何字符。 提供此宏是为了支持仅期望受信任用户获得令牌替换字符串的环境下向后兼容性。

例如,作业步骤可能包含使用 a-DBN 令牌的以下 transact-sql 语句:

CREATE DATABASE [$(A-DBN)]

在此示例中,必须将令牌语法更新为以下语法:

CREATE DATABASE [$(ESCAPE_RBRACKET(A-DBN))]

此更改不同于以前的 SQL Server 2005 行为,其中不需要转义宏。

解决方案

若要解决此问题,请将所有使用令牌的特定作业更新为新的令牌语法。 若要执行此操作,请使用 sp_AddEscapeNoneToJobStepTokens 的存储过程。 你可以使用以下 Transact-sql 脚本创建此存储过程。注意 请确保你安装的 SQL Server 2005 SP1 版本是内部版本2046或更高版本。 此外,您必须是 sysadmin 固定服务器角色的成员才能运行脚本。

      -- 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    returnENDuse msdbgoif exists (select * from sys.objects where name = N'fn_PrefaceTokensWithEscapeNone' and type = 'FN')    drop function fn_PrefaceTokensWithEscapeNonego-- This function manipulates @commands so that all bare tokens-- are prefaced with ESCAPE_NONE.create function fn_PrefaceTokensWithEscapeNone(@commands nvarchar(max)) RETURNS nvarchar(max)ASBEGIN    if (@commands IS NULL)    BEGIN        return @commands    END    -- In order to let this script run under SQLCMD mode, we define    -- the special "$(" 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 @commandsENDgoif exists (select * from sys.objects where name = N'sp_AddEscapeNoneToJobStepTokens' and type = 'P')    drop procedure sp_AddEscapeNoneToJobStepTokensgo-- 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_idgo

运行脚本后,将创建 sp_AddEscapeNoneToJobStepTokens 存储过程。 默认情况下,如果运行此存储过程时未使用任何参数,则将更新所有作业。 如果只想更新特定作业,必须至少为以下三个参数之一指定非 null 值:

  • @job_name

  • @job_id

  • @owner_name

例如,你可以使用以下语法:

  • 更新所有作业:

    EXEC sp_AddEscapeNoneToJobStepTokens
  • 通过指定作业名称来更新作业:

    EXEC sp_AddEscapeNoneToJobStepTokens 'MyJob'
  • 更新由同一所有者拥有的作业:

    EXEC sp_AddEscapeNoneToJobStepTokens null,null,'JobOwner'

此脚本将 ESCAPE_NONE 宏添加到包含令牌的所有作业步骤。 运行此脚本后,建议您尽快查看使用令牌的作业步骤。 然后,将 ESCAPE_NONE 宏替换为适用于作业步骤上下文的其他转义宏之一。注意 如果你正在使用主服务器(MSX)和目标服务器(TSX)环境,则必须在 MSX 和 TSX 上运行此脚本,以确保已正确更新 TSX 上的主作业。有关如何更新作业以使用新语法以及如何使用转义宏在 SQL Server 代理作业步骤中启用令牌替换的详细信息,请参阅 SQL server 2005 联机丛书(4月2006)或更高版本的 SQL Server 2005 联机丛书中的 "使用作业步骤中的令牌" 主题。

状态

Microsoft 已确认这是在“适用范围”部分中列出的 Microsoft 产品存在的问题。

参考

有关如何在作业步骤中使用令牌的详细信息,请访问以下 Microsoft 开发人员网络(MSDN)网站:

http://msdn2.microsoft.com/en-us/library/ms175575.aspx

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

How satisfied are you with the translation quality?
What affected your experience?

Thank you for your feedback!

×