sql server replication: Merge replication conflicts on Identity Columns -- sql server replication

Merge replication conflicts on Identity Columns

kengillett NO[at]SPAM gmail.com
7/19/2007 12:00:00 AM

sql server replication: Hi all,

I've set up a merge replication using sql server 2005.

All settings for "Copy Use Triggers" were set to false
All Identity ranges were set to be handled automatically.

There is a table - "Stock" - which has an ident field. An insert
trigger on this table inserts a new record into another table -
"barcodes" - which itself has its own ident field.

When a new "stock" record is inserted on the subscriber I get a merge
conflict which says:

"A row insert at 'xxx.YYYYYYYYY' could not be propagated to
'zzz.YYYYYYYYY'. This failure can be caused by a constraint violation.
Explicit value must be specified for identity column in table
'barcodes' either when IDENTITY_INSERT is set to ON or when a
replication user is inserting into a NOT FOR REPLICATION identity
column."

It suggested I use sp_adjustpublisheridentityrange, which I did on
both tables, but still the conflict won't resolve.

Any suggestions will be most gratefully received

Thanks

Ken

Re: Merge replication conflicts on Identity Columns

Hilary Cotter
7/20/2007 7:35:35 AM

This should resolve itself the next time the merge agent runs. It is
possibly that you have entered so much data on the subscriber that you
identity range is completely out of whack.

Issue a dbcc checkident(''tablename') on your subscriber and note the value
for the current identity value and current column value. If the current
identity value is greater than your current column value set the current
identity value to be the current column value, i.e.

dbcc checkident(''tablename',reseed, 14)

--
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
[quoted text, click to view]