January 23, 2008 at 3:32 am
Having some weird goings on while migrating replication from a sql 2000 box to a new sql 2005 box. The publications are all merge. I am moving over the publications one by one, and the first 6 or so were absolutely no problem. Then, for what seems no reason at all i started to run into problem with 2 of the publications (cblMlon0 and cblUlon0). For infot when the replication was initialised it deleted the data and bulk copied the data in. The table was never dropped.
On stopping and starting the merge job it fails with the following error
Cannot insert duplicate key row in object 'dbo.sysmergepublications' with unique index 'nc1sysmergepublications'
Looking in the sysmergepublications table i see all the valid publication, plus a lot of what were the old servers publications! So the new publisher is called SQLMaster and the old one was called Master. In the table the cblmlon0 and cblulon0 publications are in there twice with the two different publishers.
I seem to be able to get round this by running
exec sp_mergesubscription_cleanup @publisher = 'master'
, @publisher_db = 'adv'
, @publication = 'cblmlon0'
So, i seem to get as far as the agent running, and this is where it gets interesting...
Replication monitor shows the job is running fine, and no errors, however the data is out of sync! Any inserts,updates and deletes at the subscriber are not replicated to the publisher (the publication is bidirectional). If i manually try and manually add a dummy row at the subscriber it adds fine, and deleting is fine, updating isnt a problem, however none of this replicates to the publisher.
Inserting at the publisher and deleting replicates correctly, however updates dont! Another odd thing is if i insert at the publisher a dummy row, and then try and update that row at the subscriber i get this error...
Msg 21512, Level 16, State 5, Procedure upd_9B7B4D8B6D044EE38966DDC94A8F1AD5, Line 60
INITCOLVS: The colv1 parameter is shorter than the minimum required size.
Msg 3621, Level 16, State 2, Procedure upd_9B7B4D8B6D044EE38966DDC94A8F1AD5, Line 60
The statement has been terminated.
The statement has been terminated.
MS seems to say this is to do with the column/row level tracking which is feasible as on the old publisher it used to be column level and on the new its row level.
This morning i dropped the two publications and its subscriber, and reset it up from scratch exactly the same way. One of the tables now works absolutely fine, and the other is still having the same problems!
So all in all its pretty screwed atm 🙁 If anyone has any insight/advice/recommendations then i would be grateful.
ps, sorry for the long post!
March 26, 2008 at 3:04 pm
Did you fin d a way to solve this yet. I am migrating next week and I am trying to keep out of any problems. Your knowlege and/fix of this problem would definately help
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
March 27, 2008 at 2:39 am
I never found out what the issue was really, i just removed all the replication and started again, and now its fine. So my advice would be to do the same 🙂 Luckily it started failing before the server went live, so it didnt really matter that there was downtime while i sorted it out.
February 16, 2009 at 1:22 am
This link worked for me.:)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply