February 14, 2007 at 2:03 pm
Hello everyone, yes it's me again, second post in two days. But I'm stumped on this one.
I’m running merge replication between two servers:
A_SQL2K: SQL 2000 - 8.00.760 - SP3, Enterprise Edition (publisher, distributor)
B_SQL2K: SQL 2000 - 8.00.760 - SP3, Enterprise Edition (updating subscriber)
The publication consists of one article:
TABLE dbo.FT_documents
FT_documents_id int IDENTITY(1,2) NOT FOR REPLICATION,
author_profiles_id int NOT NULL,
date_modified smalldatetime DEFAULT getdate() NULL,
title nvarchar(100) NULL,
document_text text NULL,
rowguid uniqueidentifier DEFAULT newid() NOT NULL ROWGUIDCOL,
CONSTRAINT PK_FT_documents
PRIMARY KEY NONCLUSTERED (FT_documents_id) WITH FILLFACTOR=50,
CONSTRAINT UK_FTDoc_AutProID
UNIQUE NONCLUSTERED (author_profiles_id)
(I also have Full-Text index running on this table for the document_text column, background updateindex. Becuase of this I have TEXT_IN_ROW on and I cannot use UPDATE to perform the task below.)
I’m running into some conflict issues. When an existing document gets updated with a newer version we are using a Stored Procedure to delete the existing version and then do an insert of the newer version.
It appears that the delete portion on server A_SQL2K works but then when it goes to do the insert I get a conflict :
The row was inserted at 'A_SQL2K.DOCUMENTS' but could not be inserted at 'B_SQL2K.DOCUMENTS'. Violation of UNIQUE KEY constraint 'UK_FTDoc_AutProID'. Cannot insert duplicate key in object 'FT_documents'.
I understand that it cannot insert it on server B_SQL2K if that author_profiles_id already exists but the row is actually not inserted on server A_SQL2K, it is deleted but not inserted and before the insert happens on server B_SQL2K why is the Delete not happening first?
Are the downloads and uploads not serialized so the insert is trying replicate before the actual delete is replicated? Is there a way to serialize this?
What am I missing? Any insight anyone can offer on this one would be greatly appreciated. Right now I am manually resolving these conflicts becuase these documents are uploaded via an application and it is getting time consuming.
If I'm not being clear please let me know.
Thanks for any and all help...again,
Barbara
February 15, 2007 at 1:03 am
Barbara,
For a start and if possible, I'd look at applying some SQL hotfixes to bring the servers upto at least say SP3a Build 8.00.1025 or SP4 (+ relevant patches). There have been a number of merge replication related fixes since your build. Go to Microsoft's Support site and search for 'kbsqlserv2000presp4fix' and optionally 'merge' and you should see some merge related fixes (probably not related to your issue though) eg.
810688 (http://support.microsoft.com/kb/810688/) FIX: Merge Agent can resend changes for filtered publications
816780 (http://support.microsoft.com/kb/816780/EN-US/) FIX: Merge Agent failures with articles that have indexed views defined
867880 (http://support.microsoft.com/kb/867880/EN-US/) FIX: Merge Agent may fail with an "Invalid character value for cast specification" error message
Mark
February 15, 2007 at 8:48 am
Thanks for the recommendations Mark; if all goes well are actually hoping to move to 2005 within the next month or so. But in the meantime, does anyone know if downloads and uploads are serialized or not? And if not is there a way to serialize them?
I'm guessing that my problem is that the insert is trying replicate before the actual delete is replicated?
Thanks again - Barbara
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply