SQL Server 代理作业失败时该作业包含安装 SQL Server 2005 Service Pack 1 后,请使用标记的作业步骤

文章翻译 文章翻译
文章编号: 915845 - 查看本文应用于的产品
错误 #: 426808 (SQLBUDT)
展开全部 | 关闭全部

症状

在安装 Microsoft SQL Server 2005 Service Pack 1 (SP1) 后,您会遇到以下行为:
  • 该作业包含使用标记的作业步骤时,将会失败 SQL Server 代理作业。
  • 您会收到以下错误消息:
    作业步骤中包含一个或多个标记。SQL Server 2005 Service Pack 1 或更高版本,与标记的所有作业步骤必须宏的都更新,才能运行该作业。
注意与生成 2046年或更高版本的 SQL Server 2005 会出现此问题。

原因

在 SQL Server 2005 SP1 中,SQL Server 代理作业步骤标记语法已更改。现在,您必须在作业步骤中包括与使用的所有标记已转义的宏。如果您不能包括一个转义宏,这些作业步骤将会失败。下表列出了转义宏。
收起该表格展开该表格
转义宏说明
$ (ESCAPE_SQUOTE (TokenName))此宏转义撇号 (') 标记替换字符串中。该宏使用两个撇号替换一个撇号。
$ (ESCAPE_DQUOTE (TokenName))该宏对标记替换字符串中的引号 (") 进行转义。该宏将一个引号替换为两个引号引起来。
$ (ESCAPE_RBRACKET (TokenName))该宏对标记替换字符串中的右方括号 (]) 进行转义。该宏将一个右方括号替换为两个的右括号。
$ (ESCAPE_NONE (TokenName))该宏替换标记,而不转义该字符串中的任何字符。为了支持向后兼容性标记替换字符串需要的地方只从受信任的用户的环境中提供此宏。
例如对于作业步骤可能包含下面的 TRANSACT-SQL 语句 A DBN 标记的: 在此示例中的
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
    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 "$(" 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

在运行该脚本后,创建 sp_AddEscapeNoneToJobStepTokens 存储过程。默认状态下,您将更新所有作业,如果您要运行此存储的过程不带任何参数。如果您要更新只有特定的作业,则必须指定以下三个参数中至少一个非空值:
  • @ 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 丛书联机 (2006 年四月) 或更高版本的 SQL Server 2005 丛书联机。

状态

Microsoft 已经确认这是在"适用于"一节中列出的 Microsoft 产品中的问题。

参考

有关如何使用作业步骤中的标记的详细信息请访问下面的 Microsoft 开发人员网络 (MSDN) 的网站:
http://msdn2.microsoft.com/en-us/library/ms175575.aspx

属性

文章编号: 915845 - 最后修改: 2006年5月24日 - 修订: 2.1
这篇文章中的信息适用于:
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL 2005 Server Workgroup
  • Microsoft SQL 2005 Server Enterprise
关键字:?
kbmt kbsql2005presp1fix kbprb kbexpertiseadvanced kbtshoot KB915845 KbMtzh
机器翻译
注意:这篇文章是由无人工介入的微软自动的机器翻译软件翻译完成。微软很高兴能同时提供给您由人工翻译的和由机器翻译的文章, 以使您能使用您的语言访问所有的知识库文章。然而由机器翻译的文章并不总是完美的。它可能存在词汇,语法或文法的问题,就像是一个外国人在说中文时总是可能犯这样的错误。虽然我们经常升级机器翻译软件以提高翻译质量,但是我们不保证机器翻译的正确度,也不对由于内容的误译或者客户对它的错误使用所引起的任何直接的, 或间接的可能的问题负责。
点击这里察看该文章的英文版: 915845
Microsoft和/或其各供应商对于为任何目的而在本服务器上发布的文件及有关图形所含信息的适用性,不作任何声明。 所有该等文件及有关图形均"依样"提供,而不带任何性质的保证。Microsoft和/或其各供应商特此声明,对所有与该等信息有关的保证和条件不负任何责任,该等保证和条件包括关于适销性、符合特定用途、所有权和非侵权的所有默示保证和条件。在任何情况下,在由于使用或运行本服务器上的信息所引起的或与该等使用或运行有关的诉讼中,Microsoft和/或其各供应商就因丧失使用、数据或利润所导致的任何特别的、间接的、衍生性的损害或任何因使用而丧失所导致的之损害、数据或利润不负任何责任。

提供反馈

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com