0

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:

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

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:

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.

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.

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
Jay C
  • 141
  • 1
  • 12
  • can you export the job and post it (Script as create to from SSMS)? – user1443098 Apr 24 '18 at 19:19
  • The exported job definition is too long to fit into the comments. I am trying to figure out a way to post it. – Jay C Apr 24 '18 at 19:29
  • The job definition of that step (the one that is causing problem) is: EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Run snapshot', @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 – Jay C Apr 24 '18 at 19:55
  • You can modify your original post to include the definition – user1443098 Apr 24 '18 at 20:43
  • Thanks for the tip in posting the job definition. I have edited the original post to include the job definition. – Jay C Apr 25 '18 at 11:29

2 Answers2

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:

so maybe try to add "snapshot.exe" before your first parameter.

user1443098
  • 6,487
  • 5
  • 38
  • 67
  • Thanks. I have tried this, and this actually works. I need to change the Type of the step from "Replication Snapshot" to "Operating System (CmdExec)" in addition to add "snapshot.exe" to the beginning of the Command of the step. Moreover, I need to add this path to the PATH system environment variable: `C:\Program Files\Microsoft SQL Server\100\COM`. Somehow this path was not there before. – Jay C Apr 25 '18 at 12:38
  • The path is where "snapshot.exe" file is. Please note that this new path only shows up in PATH after I have reboot the server. Otherwise, SQL Server Agent that runs the job doesn't see the new path. May be next time I will try restarting SQL Server Agent service instead of rebooting the server. Still I think using this approach to solve the problem seems like a "workaround". As of now, I still attempt to make the command works using Type "Replication Snapshot" instead of "Operating System (CmdExec)". If my attempt fails, I will mark your suggestion as the Answer. – Jay C Apr 25 '18 at 12:41
  • 1
    Finally I figure out what is missing. The problem had to do with the fact that I manually created each step in the SQL Server Agent Job that I am testing (instead of using Wizard). This somehow made a difference. The difference that was causing me the trouble was that all the steps in this job recorded in msdb.dbo.sysjobsteps table were missing the "server" name. They were all NULL. I filled that fields with the publisher database server name, and the problem went away. Last time when I tried upgrading to 2008 in 2015, I got a similar problem but was in a different table (sysjobs). – Jay C Apr 25 '18 at 14:40
  • Next time when I making another similar SQL Server Agent job, I will use the wizard to make sure the basic job works OK, and then customize what the wizard has created for me instead of creating the whole job from scratch manually. – Jay C Apr 25 '18 at 14:42
  • If someone has a similar problem as mine, my suggestion is to compare the steps of a "known good" SQL Server Agent job with the steps of the job that is causing problem to see what is missing. The steps are in msdb.dbo.sysjobsteps table. Better yet, use my advice in my last message and use the Wizard to generate the job and then customize it. – Jay C Apr 25 '18 at 14:48
0

Please refer to my comments to user1443098.

Basically, the steps of the job recorded in msdb.dbo.sysjobstops were missing the "server" name.

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.

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.

Hope this helps.

Jay C
  • 141
  • 1
  • 12