Replication Disaster - Help

I had merge replication fully implemented and working beautifully. However,
I recently discovered a 1:many table (tblLoanException) that did not have a
foreign_key constraint created on it. So, I took the following actions:
1. I scripted out the creation of the foreign key constraint on
tblLoanException
2. I ran the script on my publisher to create the fk constraint - success
3. I ran the script on my subscriber via sp_addscriptexec - success
4. I used sp_addmergefilter to create the appropriate merge filter on the
table and it's foreignkey-reference table (tblLoan)
5. I reinitialized the snapshot (reluctantly, but I had to because of the
merge filter add) - success
6. I re-ran the merge agent, which reinitialized my subscriber data set -
success
-- THIS IS WHERE THINGS WENT INTO THE DEPTHS OF HELL
7. The merge agent initialized the subscriber, but then on the next merge
agent run, deleted all but 5 records from the tblLoan table.
8. I ran an "INSERT INTO" the tblLoan table on the publisher to get
backed-up data reinserted
9. Re-ran the merge agent
10. Same thing - merge agent deleted all but 5 additional tblLoan records,
so now I show 10 tblLoan records.
11. Repeats of steps 8 and 9 continue to yield the same results - 5 add'l
tblLoan records get added, but the rest are deleted.
What in the hell have I done here? At this point, I am unable to fix this
problem and have absolutely no idea why 5 records at a time remain in the
tblLoan table, but the rest continue to be deleted. I have several other
1:many filters on the tblLoan table and have had no issues to date. This is
very troubling, to say the least. Any help that can be offered here will be
extremely appreciated. Thanks!
Jeff Jones
Atlanta, GA
What does conflict viewer tell you? I suspect the answer is there. It is
also possible that your filter condition is incorrect. Make sure if you are
using the hostname parameter you over ride it with -HostName or you use a
pull subscription.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"J Jones" <JJones@.discussions.microsoft.com> wrote in message
news:B5FCD2B4-D15D-4CDD-94EC-B3BF7C9014DE@.microsoft.com...
>I had merge replication fully implemented and working beautifully.
>However,
> I recently discovered a 1:many table (tblLoanException) that did not have
> a
> foreign_key constraint created on it. So, I took the following actions:
> 1. I scripted out the creation of the foreign key constraint on
> tblLoanException
> 2. I ran the script on my publisher to create the fk constraint - success
> 3. I ran the script on my subscriber via sp_addscriptexec - success
> 4. I used sp_addmergefilter to create the appropriate merge filter on the
> table and it's foreignkey-reference table (tblLoan)
> 5. I reinitialized the snapshot (reluctantly, but I had to because of the
> merge filter add) - success
> 6. I re-ran the merge agent, which reinitialized my subscriber data set -
> success
> -- THIS IS WHERE THINGS WENT INTO THE DEPTHS OF HELL
> 7. The merge agent initialized the subscriber, but then on the next merge
> agent run, deleted all but 5 records from the tblLoan table.
> 8. I ran an "INSERT INTO" the tblLoan table on the publisher to get
> backed-up data reinserted
> 9. Re-ran the merge agent
> 10. Same thing - merge agent deleted all but 5 additional tblLoan
> records,
> so now I show 10 tblLoan records.
> 11. Repeats of steps 8 and 9 continue to yield the same results - 5 add'l
> tblLoan records get added, but the rest are deleted.
> What in the hell have I done here? At this point, I am unable to fix this
> problem and have absolutely no idea why 5 records at a time remain in the
> tblLoan table, but the rest continue to be deleted. I have several other
> 1:many filters on the tblLoan table and have had no issues to date. This
> is
> very troubling, to say the least. Any help that can be offered here will
> be
> extremely appreciated. Thanks!
> Jeff Jones
> Atlanta, GA
|||I receive the following message in the tblLoan conflict table:
The row was inserted at '[PublisherDB.tblLoan]' but could not be inserted at
'[SuscriberDB.tblLoan]'. INSERT statement conflicted with COLUMN FOREIGN KEY
constraint 'FK_Loan_TapeData'. The conflict occurred in database 'ResiDD',
table 'TapeData', column 'LoanID'.
This error makes absolutely no sense to me, as the records that get inserted
into tblLoan have a 1:1 relationship w/TapeData, where the TapeData.LoanID
column is an identity field, but the tblLoan.LoanID is a non-identity column.
The tblLoan records that exist on the publisher do in fact match 1:1 to
what's in the TapeData table on both the publisher and subscriber.
Once the above error occurs, the records get deleted from the
publisher.tblLoan. If I re-insert the remaining TapeData recs into tblLoan,
the merge agent will then insert 5 and only 5 tblLoan records at the
subscriber, and will delete the rest.
The merge agent worked flawlessly until I added the constraint and
mergefilter and re-initialized the snapshot. I've subsequently removed the
constraint and mergefilter and reinitialized the snapshot, but am still
receiving the same error. It just makes no sense at this point that it picks
up five records at a time and then deletes tblLoan records on both the
subscriber and publisher. There is absolutely no foreign key issue in this
scenario. The TapeData recs/keys are identical at both the publisher and
subscriber, and the tblLoan table is set to allow matching TapeData recs to
be added on a 1:1 basis on both the publisher and the subscriber.
I'm beyond frustrated with this...
Jeff
"Hilary Cotter" wrote:

> What does conflict viewer tell you? I suspect the answer is there. It is
> also possible that your filter condition is incorrect. Make sure if you are
> using the hostname parameter you over ride it with -HostName or you use a
> pull subscription.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "J Jones" <JJones@.discussions.microsoft.com> wrote in message
> news:B5FCD2B4-D15D-4CDD-94EC-B3BF7C9014DE@.microsoft.com...
>
>
|||Also... if I insert the remaining TapeData records into tblLoan on the
SubscriberDB vs. the PublisherDB, the merge agent still only merges 5 recs
from the SubscriberDB.tblLoan back to PublisherDB.tblLoan.
I found a CHECK constraint on the PublisherDB.TapeData table:
([LoanID] > 38548 and [LoanID] < 42000)
The TapeData.LoanIDs on the publisher are within this range.
The CHECK constraint on the SubscriberDB.TapeData table is:
([LoanID] > 51000 and [LoanID] < 54000)
The TapeData records that are on the Subscriber do not fall within the
constraint, but they made it over ok from the publisher.
But, regardless of the CHECK constraint on the TapeData table, the records
that are getting deleted are in the tblLoan table - not the TapeData. Again,
makes no sense to me. I tried altering the CHECK constraint on the
subscriber to make sure that the TapeData records were in range, but still
experienced the same conflict and subsequent deletion of both publisher and
subscriber tblLoan records.
"Hilary Cotter" wrote:

> What does conflict viewer tell you? I suspect the answer is there. It is
> also possible that your filter condition is incorrect. Make sure if you are
> using the hostname parameter you over ride it with -HostName or you use a
> pull subscription.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "J Jones" <JJones@.discussions.microsoft.com> wrote in message
> news:B5FCD2B4-D15D-4CDD-94EC-B3BF7C9014DE@.microsoft.com...
>
>