I am trying to add a "SQL Server Agent" job to a new database server that has SQL Server 2008 R2 in it. This job does snapshot replication on a "groups" table, and then distributes the snapshot to a subscriber in another server. Unfortunately that job fails at the step that makes a snapshot. It keeps giving me this error message:
我试图将“SQL Server代理”作业添加到其中包含SQL Server 2008 R2的新数据库服务器。此作业在“组”表上执行快照复制,然后将快照分发给另一个服务器中的订户。不幸的是,该作业在创建快照的步骤中失败。它一直给我这个错误信息:
The step is improperly defined and so could not be run
该步骤定义不正确,因此无法运行
The step that fails is this:
失败的步骤是:
- Type: Replication Snapshot
- Command: -Publisher [DBSERVER-NEW] -PublisherDB [ProductionDB] -Distributor [DBSERVER-NEW] -Publication [Replicate_Groups_Table] -DistributorSecurityMode 1
类型:复制快照
命令:-Publisher [DBSERVER-NEW] -PublisherDB [ProductionDB] -Distributor [DBSERVER-NEW] -Publication [Replicate_Groups_Table] -DistributorSecurityMode 1
I tried the same command at the command prompt, and it ran fine. The command that I tried was:
我在命令提示符下尝试了相同的命令,运行正常。我试过的命令是:
snapshot.exe -Publisher [DBSERVER-NEW] -PublisherDB [ProductionDB] -Distributor [DBSERVER-NEW] -Publication [Replicate_Groups_Table] -DistributorSecurityMode 1
Therefore, the command itself should be fine.
因此,命令本身应该没问题。
By the way, the "snapshot.exe" command that I use is in this folder:
顺便说一句,我使用的“snapshot.exe”命令在这个文件夹中:
C:\Program Files\Microsoft SQL Server\100\COM
C:\ Program Files \ Microsoft SQL Server \ 100 \ COM
I have checked the Advanced page of the step, and it looks fine:
我已经检查了该步骤的高级页面,看起来很好:
- On success: Go to the next step
- On failure: Quit the job reporting failure
成功时:转到下一步
失败时:退出作业报告失败
I reboot the new database server. But that didn't help.
我重启了新的数据库服务器。但这没有帮助。
Please note that the job is running in the context of the SQL Server Agent. And I have assigned a local-admin user account to the SQL Server Agent. Therefore, the job should have all the access rights that it needs.
请注意,作业正在SQL Server代理的上下文中运行。我已经为SQL Server代理分配了一个本地管理员用户帐户。因此,作业应具有所需的所有访问权限。
Please note that I have the exact same job running fine in an old database server (SQL Server 2000). And also I did this once before back in 2015 on a SQL Server 2008 R2 database serer; that was fine also. Therefore, I don't know why this time I have that much trouble with it.
请注意,我在旧的数据库服务器(SQL Server 2000)中运行正常。我还在2015年之前在SQL Server 2008 R2数据库服务器上做了一次;那也很好。因此,我不知道为什么这次我有这么多麻烦。
How can I find out exactly what the job is complaining about?
我怎样才能确切地知道这份工作在抱怨什么?
Please let me know if there is anything that I should try next. Thanks in advance.
如果有什么我应该尝试的,请告诉我。提前致谢。
Jay Chan
For your reference, the definition of the job is the following:
供您参考,作业的定义如下:
USE [msdb]
GO
/****** Object: Job [PopulateAndDistributeGroupsTable] Script Date: 04/24/2018 15:22:54 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [REPL-Snapshot] Script Date: 04/24/2018 15:22:54 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'REPL-Snapshot' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'REPL-Snapshot'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'PopulateAndDistributeGroupsTable',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Populate "groups" table based on the group info from BonTrak.',
@category_name=N'REPL-Snapshot',
@owner_login_name=N'OURDOMAIN\Admin', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Populate groups table with group info from BonTrak] Script Date: 04/24/2018 15:22:54 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Populate groups table with group info from BonTrak',
@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'TSQL',
@command=N'delete from groups
insert into groups
select p.ProjectNum, m.Number, g.GroupID
from ProjectMaster p
inner join MechanicalData m on
p.ProjectID = m.ProjectID
inner join InstallationGroupMasterTable g on
m.MechanicalID = g.MechanicalID
where g.IsVoid = 0
order by p.ProjectNum, m.Number, g.GroupID
',
@database_name=N'ProductionDB',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Snapshot Agent startup message] Script Date: 04/24/2018 15:22:54 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Snapshot Agent startup message',
@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'TSQL',
@command=N'sp_MSadd_snapshot_history @perfmon_increment = 0, @agent_id = 3, @runstatus = 1, @comments = ''Starting agent.''',
@database_name=N'distribution',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Run snapshot agent to prepare replicating groups] Script Date: 04/24/2018 15:22:54 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Run snapshot agent to prepare replicating groups',
@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'Snapshot',
@command=N'-Publisher [DBSERVER-NEW] -PublisherDB [ProductionDB] -Distributor [DBSERVER-NEW] -Publication [Replicate_Groups_Table] -DistributorSecurityMode 1',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [PA-SPARE - Run distribution agent to replicate groups table] Script Date: 04/24/2018 15:22:54 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'BRANCH01DB - Run distribution agent to replicate groups table',
@step_id=4,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=3,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'Distribution',
@command=N'-Subscriber [BRANCH01DB] -SubscriberDB [ProductionDB] -Publisher [DBSERVER-NEW] -Distributor [DBSERVER-NEW] -DistributorSecurityMode 1 -PublisherDB [ProductionDB]',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Detect nonlogged agent shutdown] Script Date: 04/24/2018 15:22:54 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Detect nonlogged agent shutdown',
@step_id=5,
@cmdexec_success_code=0,
@on_success_action=2,
@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'TSQL',
@command=N'sp_MSdetect_nonlogged_shutdown @subsystem = ''Snapshot'', @agent_id = 3',
@database_name=N'distribution',
@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'Run this job very frequently',
@enabled=1,
@freq_type=8,
@freq_interval=126,
@freq_subday_type=4,
@freq_subday_interval=5,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20180424,
@active_end_date=99991231,
@active_start_time=50200,
@active_end_time=175959,
@schedule_uid=N'057dbe80-e389-4d33-b6b7-f73315008a44'
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:
GO
2 个解决方案
#1
1
Here I read:
我在这里读到:
From the command prompt or in a batch file, start the Replication Snapshot Agent by running snapshot.exe, specifying the following command-line arguments:
从命令提示符或批处理文件中,通过运行snapshot.exe启动Replication Snapshot Agent,并指定以下命令行参数:
so maybe try to add "snapshot.exe" before your first parameter.
所以也许尝试在第一个参数之前添加“snapshot.exe”。
#2
0
Please refer to my comments to user1443098.
请参阅我对user1443098的评论。
Basically, the steps of the job recorded in msdb.dbo.sysjobstops were missing the "server" name.
基本上,msdb.dbo.sysjobstops中记录的作业的步骤缺少“服务器”名称。
My suggestion is NOT to create the whole job manually. Should use the Wizard to generate a basic job to make sure it works first, and then customize the job to add our own stuff.
我的建议不是手动创建整个作业。应该使用向导生成基本作业以确保它首先工作,然后自定义作业以添加我们自己的东西。
The other suggestion is to compare the steps (in msdb.dbo.sysjobstops) of the job with the steps in a known-good job to find out if there is anything missing.
另一个建议是将作业的步骤(在msdb.dbo.sysjobstops中)与已知良好作业中的步骤进行比较,以确定是否有任何遗漏。
If none of the above work, please try what user1443098 has suggested. That is to add "snapshot.exe" to the beginning of the command. This also involves changing the step from "Replication Snapshot" to "Operating System (CmdExec)", and making sure the search PATH includes the folder where "snapshot.exe" file is. Of course, you need to test the whole command in a Command Prompt first before incorporating the command into the SQL Server Agent job.
如果以上都不起作用,请尝试user1443098建议的内容。这是将“snapshot.exe”添加到命令的开头。这还涉及将步骤从“复制快照”更改为“操作系统(CmdExec)”,并确保搜索PATH包含“snapshot.exe”文件所在的文件夹。当然,在将命令合并到SQL Server代理作业之前,需要先在命令提示符中测试整个命令。
Hope this helps.
希望这可以帮助。
#1
1
Here I read:
我在这里读到:
From the command prompt or in a batch file, start the Replication Snapshot Agent by running snapshot.exe, specifying the following command-line arguments:
从命令提示符或批处理文件中,通过运行snapshot.exe启动Replication Snapshot Agent,并指定以下命令行参数:
so maybe try to add "snapshot.exe" before your first parameter.
所以也许尝试在第一个参数之前添加“snapshot.exe”。
#2
0
Please refer to my comments to user1443098.
请参阅我对user1443098的评论。
Basically, the steps of the job recorded in msdb.dbo.sysjobstops were missing the "server" name.
基本上,msdb.dbo.sysjobstops中记录的作业的步骤缺少“服务器”名称。
My suggestion is NOT to create the whole job manually. Should use the Wizard to generate a basic job to make sure it works first, and then customize the job to add our own stuff.
我的建议不是手动创建整个作业。应该使用向导生成基本作业以确保它首先工作,然后自定义作业以添加我们自己的东西。
The other suggestion is to compare the steps (in msdb.dbo.sysjobstops) of the job with the steps in a known-good job to find out if there is anything missing.
另一个建议是将作业的步骤(在msdb.dbo.sysjobstops中)与已知良好作业中的步骤进行比较,以确定是否有任何遗漏。
If none of the above work, please try what user1443098 has suggested. That is to add "snapshot.exe" to the beginning of the command. This also involves changing the step from "Replication Snapshot" to "Operating System (CmdExec)", and making sure the search PATH includes the folder where "snapshot.exe" file is. Of course, you need to test the whole command in a Command Prompt first before incorporating the command into the SQL Server Agent job.
如果以上都不起作用,请尝试user1443098建议的内容。这是将“snapshot.exe”添加到命令的开头。这还涉及将步骤从“复制快照”更改为“操作系统(CmdExec)”,并确保搜索PATH包含“snapshot.exe”文件所在的文件夹。当然,在将命令合并到SQL Server代理作业之前,需要先在命令提示符中测试整个命令。
Hope this helps.
希望这可以帮助。