你目前正处于脱机状态,正在等待 Internet 重新连接

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

Extended support for SQL Server 2005 ends on April 12, 2016

If you are still running SQL Server 2005 after April 12, 2016, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

注意:这篇文章是由无人工介入的微软自动的机器翻译软件翻译完成。微软很高兴能同时提供给您由人工翻译的和由机器翻译的文章, 以使您能使用您的语言访问所有的知识库文章。然而由机器翻译的文章并不总是完美的。它可能存在词汇,语法或文法的问题,就像是一个外国人在说中文时总是可能犯这样的错误。虽然我们经常升级机器翻译软件以提高翻译质量,但是我们不保证机器翻译的正确度,也不对由于内容的误译或者客户对它的错误使用所引起的任何直接的, 或间接的可能的问题负责。

点击这里察看该文章的英文版: 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    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 存储过程。默认状态下,您将更新所有作业,如果您要运行此存储的过程不带任何参数。如果您要更新只有特定的作业,则必须指定以下三个参数中至少一个非空值:
  • @ 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) 的网站:

Warning: This article has been translated automatically

属性

文章 ID:915845 - 上次审阅时间:05/24/2006 16:37:19 - 修订版本: 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
反馈