February 6, 2017 at 1:30 pm
Hello.
I am trying to troubleshoot what should be a simple set up (published database, handful of tables, 1 subscriber set to 'replication support only')
I have tried via GUI as well as via Tsql and run into the same error after the subscription is added
Servers involved include (Server A pub | Remote Distribution database | Server B sub). All servers are sql 2012 SP2.
I traced the distribution server and it indeed has an error inside a system call but why that is happening I don't know.
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)
Add pub code
EXEC sys.sp_addpublication
@publication = N'publication_1 ,
@description = N'Transactional publication of database ''DB_1'' from Publisher ''ServerName'.' ,
@sync_method = N'concurrent' ,
@retention = 0 ,
@allow_push = N'true' ,
@allow_pull = N'true' ,
@allow_anonymous = N'false' ,
@enabled_for_internet = N'false' ,
@snapshot_in_defaultfolder = N'true' ,
@compress_snapshot = N'false' ,
@ftp_port = 21 ,
@ftp_login = N'anonymous' ,
@allow_subscription_copy = N'false' ,
@add_to_active_directory = N'false' ,
@repl_freq = N'continuous' ,
@status = N'active' ,
@independent_agent = N'true' ,
@immediate_sync = N'false' ,
@allow_sync_tran = N'false' ,
@autogen_sync_procs = N'false' ,
@allow_queued_tran = N'false' ,
@allow_dts = N'false' ,
@replicate_ddl = 1 ,
@allow_initialize_from_backup = N'false' ,
@enabled_for_p2p = N'false' ,
@enabled_for_het_sub = N'false';
GO
add article
EXEC sys.sp_addarticle
@publication = N'pub_1' ,
@article = N'Article_1' ,
@source_owner = N'dbo' ,
@source_object = N'Article_1' ,
@type = N'logbased' ,
@description = N'' ,
@creation_script = N'' ,
@pre_creation_cmd = N'none' ,
@schema_option = 0x000000050803109F ,
@identityrangemanagementoption = N'manual' ,
@destination_table = N'Article_1' ,
@destination_owner = N'dbo' ,
@status = 24 ,
@vertical_partition = N'false' ,
@ins_cmd = N'CALL [sp_MSins_dboArticle_1]' ,
@del_cmd = N'CALL [sp_MSdel_dboArticle_1]' ,
@upd_cmd = N'SCALL [sp_MSupd_dboArticle_1]';
subscription add
EXEC sys.sp_addsubscription
@publication = N'pub_1' ,
@subscriber = N'Server_1' ,
@destination_db = N'DB_1' ,
@subscription_type = N'Push' ,
@sync_type = N'replication support only' ,
@article = N'all' ,
@update_mode = N'read only' ,
@subscriber_type = 0;
February 7, 2017 at 6:38 am
Hi,
have you checked every account, with the replication will use.
There are a lot of accounts trying to write or read from the publisher database, distribution database, ore write to the subscriber database.
Have you ever changed a password since last restart of the replication. Are there some events in the windows eventlog, something like "login failure"?
Are you using domainaccounts or local sql accounts for replication?
Kind regards,
Andreas
February 7, 2017 at 7:24 am
I can't help you with those errors but logging the replication with the highest verbose level might give you more clues - Replication Logging.
February 7, 2017 at 7:35 am
Hi!
Thanks for the reply.
I am using a domain account for the remote distributor, which has sysadmin (dev box). This account is only used for replication.
From a permissions / password standpoint I feel pretty good. I can switch the subscription to "automatic" (i.e. snapshot replication) and move right along as i would have expected...I am for whatever reason missing something on the replication support only set up.
The distribution server serves as the distribution server with several distribution databases for most of the lower environment. I don't know the history of how it has been dealt with so that is an X factor also.
I am also in the middle of creating a new remote distribution server to see if it is something goofy with the distribution database / server. If that still has an error I was going to start looking at the database we are trying to set up with replication support only.
February 7, 2017 at 8:06 am
Hi Steve,
I did try to add the logging to the log reader agent but I never got it to create the output file.
Reviewing your article, I tried it a different way adding the square brackets and it worked "THANKS"
Unfortunately the output is similar to what is in the job history, nothing really to go on. I will post some reliant output from the log file and see if it sparks any conversations.
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/>'
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply