添加链接 注册    登录
link管理
链接快照平台
  • 输入网页链接，自动生成快照
  • 标签化管理网页链接
相关文章推荐
独立的小狗  ·  Catch the IEX if You ...·  2 月前    · 
痛苦的双杠  ·  python读取和保存json文件_pyth ...·  2 月前    · 
绅士的毛衣  ·  基于JavaScript ...·  2 月前    · 
兴奋的草稿本  ·  ASP.NET Core ...·  8 月前    · 
力能扛鼎的饼干  ·  在宁全国人大代表开展年中专题调研_ ...·  1 年前    · 
爱旅游的盒饭  ·  江苏省人民政府 政府办公室(厅)文件 ...·  1 年前    · 
安静的弓箭  ·  2018世界街球大奖赛基里连科现身并点赞_国 ...·  1 年前    · 
刚失恋的啤酒  ·  swift5弃用方法'withUnsafeB ...·  1 年前    · 
link管理  ›  sqlserver JOB脚本错误如何解决
脚本
https://qa.social.msdn.microsoft.com/Forums/office/zh-CN/7f68ceb2-81f2-4dcc-965f-b36c18a720d1/sqlserver-job33050264123816935823229
玩足球的企鹅
2 年前

我做了一个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]
/****** 对象:  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
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:

这样我知道行，但是底下的如何改“@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
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'
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;
CLOSE CUR_STEP;
DEALLOCATE CUR_STEP;

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

这样我知道行，但是底下的如何改“@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
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'
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;
CLOSE CUR_STEP;
DEALLOCATE CUR_STEP;
 
推荐文章
独立的小狗  ·  Catch the IEX if You Can: PowerShell Deobfuscation - Technoir - Blog of Satharus
2 月前
痛苦的双杠  ·  python读取和保存json文件_python json 保存 编码
2 月前
绅士的毛衣  ·  基于JavaScript 声明全局变量的三种方式详解 - 听风。
2 月前
兴奋的草稿本  ·  ASP.NET Core 表单中的标记帮助程序 | Microsoft Learn
8 月前
力能扛鼎的饼干  ·  在宁全国人大代表开展年中专题调研_ 代表活动_ 南京市人大
1 年前
爱旅游的盒饭  ·  江苏省人民政府 政府办公室(厅)文件 省政府办公厅关于印发江苏省制造业智能化改造和数字化转型三年行动计划（2022－2024年）的通知
1 年前
安静的弓箭  ·  2018世界街球大奖赛基里连科现身并点赞_国家体育总局
1 年前
刚失恋的啤酒  ·  swift5弃用方法'withUnsafeBytes' is deprecated解决办法_ios 获取data withunsafebytes-CSDN博客
1 年前
Link管理   ·   Sov5搜索   ·   小百科
link管理 - 链接快照平台