none
sqlserver JOB脚本错误如何解决 RRS feed

  • 问题

  • 我做了一个job的脚本,job的主要任务就是按顺序执行包,但是每次部署时,都要改脚本的所有者,这样很麻烦,所以我给所以者做了一个变量,但是失败了,@owner_login_name=@JobSERVER+'\Administrator',报“'+' 附近有语法错误。”,这个脚本中有很多地方用到机器名称(JobSERVER),比如'/DTS "\MSDB\TBR_DMS_Customer" /SERVER "KJ-0DDE99AB3896" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E'中的“KJ-0DDE99AB3896”也是机器名称,直接换成@JobSERVER报同样的错误,请问如何解决

    USE [msdb]
    GO
    /****** 对象:  Job [TBRDMSBI_ByDay]    脚本日期: 01/20/2010 11:34:15 ******/
    BEGIN TRANSACTION
    
    DECLARE @JobSERVER varchar(100)
    SELECT @JobSERVER='KJ-0DDE99AB3896'
    DECLARE @ReturnCode INT
    SELECT @ReturnCode = 0
    /****** 对象:  JobCategory [[Uncategorized (Local)]]]    脚本日期: 01/20/2010 11:34:15 ******/
    IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
    BEGIN
    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    
    END
    
    DECLARE @jobId BINARY(16)
    EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'TBRDMSBI_ByDay', 
    		@enabled=1, 
    		@notify_level_eventlog=0, 
    		@notify_level_email=0, 
    		@notify_level_netsend=0, 
    		@notify_level_page=0, 
    		@delete_level=0, 
    		@description=N'No description available.', 
    		@category_name=N'[Uncategorized (Local)]', 
    		@owner_login_name=@JobSERVER+'\Administrator', @job_id = @jobId OUTPUT
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    /****** 对象:  Step [Customer]    脚本日期: 01/20/2010 11:34:16 ******/
    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Customer', 
    		@step_id=1, 
    		@cmdexec_success_code=0, 
    		@on_success_action=3, 
    		@on_success_step_id=0, 
    		@on_fail_action=2, 
    		@on_fail_step_id=0, 
    		@retry_attempts=0, 
    		@retry_interval=0, 
    		@os_run_priority=0, @subsystem=N'SSIS', 
    		@command=N'/DTS "\MSDB\TBR_DMS_Customer" /SERVER "KJ-0DDE99AB3896" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E', 
    		@database_name=N'master', 
    		@flags=0
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    /****** 对象:  Step [Retailer]    脚本日期: 01/20/2010 11:34:16 ******/
    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Retailer', 
    		@step_id=2, 
    		@cmdexec_success_code=0, 
    		@on_success_action=3, 
    		@on_success_step_id=0, 
    		@on_fail_action=2, 
    		@on_fail_step_id=0, 
    		@retry_attempts=0, 
    		@retry_interval=0, 
    		@os_run_priority=0, @subsystem=N'SSIS', 
    		@command=N'/DTS "\MSDB\TBR_DMS_Retalier" /SERVER "KJ-0DDE99AB3896" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E', 
    		@database_name=N'master', 
    		@flags=0
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    /****** 对象:  Step [Product]    脚本日期: 01/20/2010 11:34:16 ******/
    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Product', 
    		@step_id=3, 
    		@cmdexec_success_code=0, 
    		@on_success_action=3, 
    		@on_success_step_id=0, 
    		@on_fail_action=2, 
    		@on_fail_step_id=0, 
    		@retry_attempts=0, 
    		@retry_interval=0, 
    		@os_run_priority=0, @subsystem=N'SSIS', 
    		@command=N'/DTS "\MSDB\TBR_DMS_Product" /SERVER "KJ-0DDE99AB3896" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E', 
    		@database_name=N'master', 
    		@flags=0
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    /****** 对象:  Step [DealerPSI]    脚本日期: 01/20/2010 11:34:16 ******/
    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DealerPSI', 
    		@step_id=4, 
    		@cmdexec_success_code=0, 
    		@on_success_action=3, 
    		@on_success_step_id=0, 
    		@on_fail_action=2, 
    		@on_fail_step_id=0, 
    		@retry_attempts=0, 
    		@retry_interval=0, 
    		@os_run_priority=0, @subsystem=N'SSIS', 
    		@command=N'/DTS "\MSDB\TBR_DMS_DealerPSI" /SERVER "KJ-0DDE99AB3896" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E', 
    		@database_name=N'master', 
    		@flags=0
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    /****** 对象:  Step [RetailerPSI]    脚本日期: 01/20/2010 11:34:16 ******/
    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'RetailerPSI', 
    		@step_id=5, 
    		@cmdexec_success_code=0, 
    		@on_success_action=3, 
    		@on_success_step_id=0, 
    		@on_fail_action=2, 
    		@on_fail_step_id=0, 
    		@retry_attempts=0, 
    		@retry_interval=0, 
    		@os_run_priority=0, @subsystem=N'SSIS', 
    		@command=N'/DTS "\MSDB\TBR_DMS_RetalierPSI" /SERVER "KJ-0DDE99AB3896" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E', 
    		@database_name=N'master', 
    		@flags=0
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    /****** 对象:  Step [DealerPurchaseTarget]    脚本日期: 01/20/2010 11:34:16 ******/
    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DealerPurchaseTarget', 
    		@step_id=6, 
    		@cmdexec_success_code=0, 
    		@on_success_action=3, 
    		@on_success_step_id=0, 
    		@on_fail_action=2, 
    		@on_fail_step_id=0, 
    		@retry_attempts=0, 
    		@retry_interval=0, 
    		@os_run_priority=0, @subsystem=N'SSIS', 
    		@command=N'/DTS "\MSDB\TBR_DMS_DealerPurchaseTargetRange" /SERVER "KJ-0DDE99AB3896" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E', 
    		@database_name=N'master', 
    		@flags=0
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    /****** 对象:  Step [DealerSalesTarget]    脚本日期: 01/20/2010 11:34:16 ******/
    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DealerSalesTarget', 
    		@step_id=7, 
    		@cmdexec_success_code=0, 
    		@on_success_action=3, 
    		@on_success_step_id=0, 
    		@on_fail_action=2, 
    		@on_fail_step_id=0, 
    		@retry_attempts=0, 
    		@retry_interval=0, 
    		@os_run_priority=0, @subsystem=N'SSIS', 
    		@command=N'/DTS "\MSDB\TBR_DMS_DealerSalesTarget" /SERVER "KJ-0DDE99AB3896" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E', 
    		@database_name=N'master', 
    		@flags=0
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    /****** 对象:  Step [RetailerSalesTarget]    脚本日期: 01/20/2010 11:34:17 ******/
    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'RetailerSalesTarget', 
    		@step_id=8, 
    		@cmdexec_success_code=0, 
    		@on_success_action=3, 
    		@on_success_step_id=0, 
    		@on_fail_action=2, 
    		@on_fail_step_id=0, 
    		@retry_attempts=0, 
    		@retry_interval=0, 
    		@os_run_priority=0, @subsystem=N'SSIS', 
    		@command=N'/DTS "\MSDB\TBR_DMS_RetailerSalesTarget" /SERVER "KJ-0DDE99AB3896" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E', 
    		@database_name=N'master', 
    		@flags=0
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    /****** 对象:  Step [OrderPlan]    脚本日期: 01/20/2010 11:34:17 ******/
    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'OrderPlan', 
    		@step_id=9, 
    		@cmdexec_success_code=0, 
    		@on_success_action=3, 
    		@on_success_step_id=0, 
    		@on_fail_action=2, 
    		@on_fail_step_id=0, 
    		@retry_attempts=0, 
    		@retry_interval=0, 
    		@os_run_priority=0, @subsystem=N'SSIS', 
    		@command=N'/DTS "\MSDB\TBR_DMS_DealerOrderPlan" /SERVER "KJ-0DDE99AB3896" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E', 
    		@database_name=N'master', 
    		@flags=0
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    /****** 对象:  Step [DealerOrder]    脚本日期: 01/20/2010 11:34:17 ******/
    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DealerOrder', 
    		@step_id=10, 
    		@cmdexec_success_code=0, 
    		@on_success_action=3, 
    		@on_success_step_id=0, 
    		@on_fail_action=2, 
    		@on_fail_step_id=0, 
    		@retry_attempts=0, 
    		@retry_interval=0, 
    		@os_run_priority=0, @subsystem=N'SSIS', 
    		@command=N'/DTS "\MSDB\TBR_DMS_DealerOrder" /SERVER "KJ-0DDE99AB3896" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E', 
    		@database_name=N'master', 
    		@flags=0
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    /****** 对象:  Step [BSCNOrder]    脚本日期: 01/20/2010 11:34:17 ******/
    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'BSCNOrder', 
    		@step_id=11, 
    		@cmdexec_success_code=0, 
    		@on_success_action=3, 
    		@on_success_step_id=0, 
    		@on_fail_action=2, 
    		@on_fail_step_id=0, 
    		@retry_attempts=0, 
    		@retry_interval=0, 
    		@os_run_priority=0, @subsystem=N'SSIS', 
    		@command=N'/DTS "\MSDB\TBR_DMS_BSCNOrder" /SERVER "KJ-0DDE99AB3896" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E', 
    		@database_name=N'master', 
    		@flags=0
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    /****** 对象:  Step [SAPOrder]    脚本日期: 01/20/2010 11:34:17 ******/
    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'SAPOrder', 
    		@step_id=12, 
    		@cmdexec_success_code=0, 
    		@on_success_action=3, 
    		@on_success_step_id=0, 
    		@on_fail_action=2, 
    		@on_fail_step_id=0, 
    		@retry_attempts=0, 
    		@retry_interval=0, 
    		@os_run_priority=0, @subsystem=N'SSIS', 
    		@command=N'/DTS "\MSDB\TBR_DMS_SAPOrder" /SERVER "KJ-0DDE99AB3896" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E', 
    		@database_name=N'master', 
    		@flags=0
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    /****** 对象:  Step [Delivery]    脚本日期: 01/20/2010 11:34:17 ******/
    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Delivery', 
    		@step_id=13, 
    		@cmdexec_success_code=0, 
    		@on_success_action=1, 
    		@on_success_step_id=0, 
    		@on_fail_action=2, 
    		@on_fail_step_id=0, 
    		@retry_attempts=0, 
    		@retry_interval=0, 
    		@os_run_priority=0, @subsystem=N'SSIS', 
    		@command=N'/DTS "\MSDB\TBR_DMS_Delivery" /SERVER "KJ-0DDE99AB3896" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E', 
    		@database_name=N'master', 
    		@flags=0
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'ByDay', 
    		@enabled=1, 
    		@freq_type=4, 
    		@freq_interval=1, 
    		@freq_subday_type=1, 
    		@freq_subday_interval=0, 
    		@freq_relative_interval=0, 
    		@freq_recurrence_factor=0, 
    		@active_start_date=20091215, 
    		@active_end_date=99991231, 
    		@active_start_time=113300, 
    		@active_end_time=235959
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    COMMIT TRANSACTION
    GOTO EndSave
    QuitWithRollback:
        IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
    EndSave:
    
    2010年1月22日 9:01

答案

  • 这样我知道行,但是底下的如何改“@command=N'/DTS "\MSDB\TBR_DMS_Customer" /SERVER "KJ-0DDE99AB3896" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E',
    ”,中SERVER "KJ-0DDE99AB3896“如何改,难道每个包的路径都要作为一个变量,我一共有28个包,每个包的名称不一样,是不是要28个变量,这样岂不是很麻烦


    一个变量, 在每次执行 sp_add_jobstep 之间赋值

    另外一种方法, 对于sp_add_jobstpe 而言, 基本上变化的只有 @step_id @step_name及 @command 参数, 那么你完全可以用游标来做的, 类似于这样:
    DECLARE
    	@server_name sysname
    SET @server_name = N'KJ-0DDE99AB3896';
    
    DECLARE CUR_STEP CURSOR LOCAL
    FOR
    SELECT
    	command = N'/DTS "'
    			+ package_name
    			+ N'" /SERVER "'
    			+ @server_name -- N'KJ-0DDE99AB3896'
    			+ N'" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E',
    	step_name = STUFF(package_name, 1, LEN(N'\MSDB\TBR_DMS_'), N'')
    FROM(    -- 列出所要执行的 package 名称(示例中的 step name 是根据 package name 取的, 要自定义的话也在下面列出来)
    	SELECT package_name = N'\MSDB\TBR_DMS_Delivery' UNION ALL
    	SELECT package_name = N'\MSDB\TBR_DMS_Delivery' UNION ALL
    	SELECT package_name = N'\MSDB\TBR_DMS_Delivery' UNION ALL
    	SELECT package_name = N'\MSDB\TBR_DMS_Delivery'
    )A
    ;
    OPEN CUR_STEP;
    DECLARE
    	@command nvarchar(4000),
    	@step_name sysname,
    	@step_id int
    ;
    SELECT
    	@step_id = 1
    ;
    FETCH CUR_STEP INTO @command, @step_name;
    WHILE @@FETCH_STATUS = 0
    BEGIN;
    	EXEC @ReturnCode = msdb.dbo.sp_add_jobstep
    			@job_id=@jobId,
    			@step_name=@step_name, 
    			@step_id=@step_id, 
    			@cmdexec_success_code=0, 
    			@on_success_action=1, 
    			@on_success_step_id=0, 
    			@on_fail_action=2, 
    			@on_fail_step_id=0, 
    			@retry_attempts=0, 
    			@retry_interval=0, 
    			@os_run_priority=0, @subsystem=N'SSIS', 
    			@command=@command, 
    			@database_name=N'master', 
    			@flags=0
    	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    	
    	FETCH CUR_STEP INTO @command, @step_name;
    	SET @step_id = @step_id + 1;
    END;
    CLOSE CUR_STEP;
    DEALLOCATE CUR_STEP;
    
    • 已标记为答案 zuocl 2010年1月25日 5:23
    2010年1月25日 4:40

全部回复

  • job在不同的机器上执行时,都要把@owner_login_name改为部署机器的机器名,但是如果改成@owner_login_name=@JobSERVER+'\Administrator',报加号有错,还有下面的执行包的服务器(SERVER "KJ-0DDE99AB3896")也要改成@JobSERVER,如果直接改成@JobSERVER后,并没有获得@JobSERVER的值,而是字符串“@JobSERVER

    2010年1月22日 9:09
  • job在不同的机器上执行时,都要把@owner_login_name改为部署机器的机器名,但是如果改成@owner_login_name=@JobSERVER+'\Administrator',报加号有错,还有下面的执行包的服务器(SERVER "KJ-0DDE99AB3896")也要改成@JobSERVER,如果直接改成@JobSERVER后,并没有获得@JobSERVER的值,而是字符串“@JobSERVER


    DECLARE @JobSERVER  varchar(100)
    set @JobSERVER='KJ-0DDE99AB3896\Administrator'
    DECLARE @ReturnCode INT
    set @ReturnCode = 0
     

    DECLARE @jobId BINARY(16)
    EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'TBRDMSBI_ByDay',
      @enabled=1,
      @notify_level_eventlog=0,
      @notify_level_email=0,
      @notify_level_netsend=0,
      @notify_level_page=0,
      @delete_level=0,
      @description=N'No description available.',
      @category_name=N'[Uncategorized (Local)]',
      @owner_login_name = @JobSERVER, @job_id = @jobId OUTPUT


    More: blog.csdn.net/happyflystone
    2010年1月22日 14:52
  • 这样我知道行,但是底下的如何改“@command=N'/DTS "\MSDB\TBR_DMS_Customer" /SERVER "KJ-0DDE99AB3896" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E',
    ”,中SERVER "KJ-0DDE99AB3896“如何改,难道每个包的路径都要作为一个变量,我一共有28个包,每个包的名称不一样,是不是要28个变量,这样岂不是很麻烦

    2010年1月25日 3:28
  • You can use one variable and assign value to it 28 times.
    2010年1月25日 3:37
  • How Can I do this,Can you give me a example?
    2010年1月25日 3:51
  • One job with 28 steps or 28 jobs?
    2010年1月25日 3:57
  • one job with 28 steps!
    我是的目的使用一个job里面有28个step,每个step是运行一个包,通过job定时运行这些包(实现数据的导入)
    2010年1月25日 4:01
  • 这样我知道行,但是底下的如何改“@command=N'/DTS "\MSDB\TBR_DMS_Customer" /SERVER "KJ-0DDE99AB3896" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E',
    ”,中SERVER "KJ-0DDE99AB3896“如何改,难道每个包的路径都要作为一个变量,我一共有28个包,每个包的名称不一样,是不是要28个变量,这样岂不是很麻烦


    一个变量, 在每次执行 sp_add_jobstep 之间赋值

    另外一种方法, 对于sp_add_jobstpe 而言, 基本上变化的只有 @step_id @step_name及 @command 参数, 那么你完全可以用游标来做的, 类似于这样:
    DECLARE
    	@server_name sysname
    SET @server_name = N'KJ-0DDE99AB3896';
    
    DECLARE CUR_STEP CURSOR LOCAL
    FOR
    SELECT
    	command = N'/DTS "'
    			+ package_name
    			+ N'" /SERVER "'
    			+ @server_name -- N'KJ-0DDE99AB3896'
    			+ N'" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E',
    	step_name = STUFF(package_name, 1, LEN(N'\MSDB\TBR_DMS_'), N'')
    FROM(    -- 列出所要执行的 package 名称(示例中的 step name 是根据 package name 取的, 要自定义的话也在下面列出来)
    	SELECT package_name = N'\MSDB\TBR_DMS_Delivery' UNION ALL
    	SELECT package_name = N'\MSDB\TBR_DMS_Delivery' UNION ALL
    	SELECT package_name = N'\MSDB\TBR_DMS_Delivery' UNION ALL
    	SELECT package_name = N'\MSDB\TBR_DMS_Delivery'
    )A
    ;
    OPEN CUR_STEP;
    DECLARE
    	@command nvarchar(4000),
    	@step_name sysname,
    	@step_id int
    ;
    SELECT
    	@step_id = 1
    ;
    FETCH CUR_STEP INTO @command, @step_name;
    WHILE @@FETCH_STATUS = 0
    BEGIN;
    	EXEC @ReturnCode = msdb.dbo.sp_add_jobstep
    			@job_id=@jobId,
    			@step_name=@step_name, 
    			@step_id=@step_id, 
    			@cmdexec_success_code=0, 
    			@on_success_action=1, 
    			@on_success_step_id=0, 
    			@on_fail_action=2, 
    			@on_fail_step_id=0, 
    			@retry_attempts=0, 
    			@retry_interval=0, 
    			@os_run_priority=0, @subsystem=N'SSIS', 
    			@command=@command, 
    			@database_name=N'master', 
    			@flags=0
    	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    	
    	FETCH CUR_STEP INTO @command, @step_name;
    	SET @step_id = @step_id + 1;
    END;
    CLOSE CUR_STEP;
    DEALLOCATE CUR_STEP;
    
    • 已标记为答案 zuocl 2010年1月25日 5:23
    2010年1月25日 4:40