1

Brief explanation is I am trying to set up a basic publication / single subscription of about 5 tables using a remote distribution database.

Servers involved include (Server A pub | Remote Distribution database Server B | Server C sub). All servers are sql 2012 SP2. All on the same domain.

FOR THE LIFE of me, I am continually running into an error and I am just not able to figure it out.

Actual Error in Relp Monitor

The error I am receiving is on the log reader the second I add the actual subscription. If I don't use "replication support only" and switch it to automatic things work as expected...but that isn't what we are aiming for.

The process could not execute 'sp_MSadd_replcmds' on <'Distribution Server>'. (Source: MSSQLServer, Error number: 1007) Conversion failed when converting the nvarchar value '.' to data type int. (Source: MSSQLServer, Error number: 1007) Batches were not committed to the Distributor. (Source: MSSQL_REPL, Error number: MSSQL_REPL22020) The last step did not log any message! (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)

I have also enabled verbose logging on the log reader command and it yields almost the same information

2017-02-07 14:55:19.422 Publisher: {call sp_repldone ( 0x0009d8a6000290d90008, 0x0009d8a6000290d90008, 0, 0)}
2017-02-07 14:55:19.438 Publisher: {call sp_replcmds (500, 0, 0, , 0, 500000)}
2017-02-07 14:55:34.773 Status: 4, code: 22044, text: 'Approximately 500000 log records have been scanned in pass # 1, 0 of which were marked for replication.'.
2017-02-07 14:55:34.773 Status: 4, code: 22044, text: 'Approximately 1000000 log records have been scanned in pass # 1, 0 of which were marked for replication.'.
2017-02-07 14:55:34.773 Status: 4, code: 22044, text: 'Approximately 1500000 log records have been scanned in pass # 1, 0 of which were marked for replication.'.
...
...
...
2017-02-07 14:55:34.960 Status: 4, code: 22044, text: 'Approximately 19500000 log records have been scanned in pass # 1, 0 of which were marked for replication.'.
2017-02-07 14:55:34.960 Status: 4, code: 22044, text: 'Approximately 20000000 log records have been scanned in pass # 1, 0 of which were marked for replication.'.
2017-02-07 14:55:34.976 Status: 0, code: 1007, text: 'The process could not execute 'sp_MSadd_replcmds' on '</Distribution Server/>'.'.
2017-02-07 14:55:34.976 The process could not execute 'sp_MSadd_replcmds' on '</Distribution Server/>'.
2017-02-07 14:55:34.976 Status: 0, code: 1007, text: 'Conversion failed when converting the nvarchar value '.' to data type int.'.
2017-02-07 14:55:34.991 Status: 4, code: 6001, text: 'Background thread for 'Command Loader' is exiting.'.
2017-02-07 14:55:34.991 Status: 0, code: 22020, text: 'Batches were not committed to the Distributor.'.
2017-02-07 14:55:35.007 Status: 0, code: 22037, text: 'The last step did not log any message!'.
2017-02-07 14:55:35.007 Disconnecting from OLE DB DISTOLE '</Distribution Server/>'
2017-02-07 14:55:43.010 Disconnecting from OLE DB Publisher '</Publisher Server/>'
2017-02-07 14:55:43.010 Disconnecting from OLE DB </Distribution Server/> '</Distribution Server/>' 

I have been staring at this all day and going to pause to see if I am missing something obvious.

In another test, if the distribution database is local to the publication (i.e. publisher is also a distributor) this works...

If the distribution db is on a remote server I get the error on the log reader job.

Any advice is appreciated.

  • Grant db_owner on publication, subscription & distribution databases to the account using to configure replication/security settings – Rajesh Ranjan Feb 8 '17 at 8:13
  • I would start with this: "'The process could not execute 'sp_MSadd_replcmds' on '</Distribution Server/>'.'.". This proc is executed by the log reader agent, and inserts the commands into the MSRepl_commands table on the distribution db. Fire up profiler to trace this stored proc, and see if you can capture the failed statement. I am almost positive it has to do with not setting up one of the ins/upd/del procs correctly, or something like that. – Greg Feb 9 '17 at 18:55
  • Sorry for the delay. I believe you are correct. SO...I got it to work, with a semi work around. – talktolee Feb 17 '17 at 14:14
  • @greg, Sorry for the delay. I believe you are correct. SO...I got it to work, with a semi work around. After horsing around for hours on it, I asked one of the developers who I work with closely to see if he had any opinions on it. He tested and got the same results Then he added a command at the end to wipe out all commands: EXEC sp_repldone xactid = NULL, xact_segno = NULL, numtrans = 0, time = 0, reset = 1 – talktolee Feb 17 '17 at 14:26
  • Once he did that we didn't get the error anymore and we simply had to generate the insert / update / delete procedures manually and we were off and working. EXEC sp_scriptpublicationcustomprocs 'publication_name'. Still doesn't explain why this errors originally though. – talktolee Feb 17 '17 at 14:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.