Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
vinayj
Starting Member
32 Posts |
 Posted - 2005-08-11 : 16:41:42
|
| I'm using Merge ReplicationHere is the Scenario, I have three machines. In one I have PUBLISHER where all the articles are published. I have used NOT FOR REPLICATION for IDENTITY VALUES in this Server. The other two machines are SUBSCRIBERS. I have set up merge replication and every thing works fine.(i.e. if I make any changes to Publisher the two subscribers are updated and if I make any change to one of the subscriber the other subscriber and publisher is updated. But My problem is I cannot insert new rows at the SUBSCRIBER.If I try to do that It throws up "VIOLATION OF PRIMARY KEY". But I can INsert New Rows into the PUBLISHER and that newly inserted row is passed to the SUBSCRIBERS.I WANT MY APPLICATION TO TALK TO ONE OF SUBSCRIBERS AND ALLOW TO INSERT DATACan any one help me out |
|
|
mblythe
Starting Member
16 Posts |
Posted - 2005-08-12 : 14:22:10
|
| The NOT FOR REPLICATION option for identities only affects inserts performed by the Merge Agent (not user inserts). The PK violation is likely caused by the fact that you are not using an identity range management scheme. The same identity value is being generated at more than one node, so there is a conflict. You can either manage identity ranges manually (see description below), or use automatic identity range management, available through the UI and sp_addmergearticle. Relevant parameters are:* @auto_identity_range* @pub_identity_range* @identity_range* @thresholdAuto management is create-time only, so you would have to tear down and re-create the publication and subscriptions. Let me know if you need more information.==== From SQL Server 2005 BOLIf you specify manual identity range management, you must ensure that the Publisher and each Subscriber use different identity ranges. For example, consider a table at the Publisher with an identity column defined as IDENTITY(1,1): the identity column starts at 1 and is incremented by 1 each time a row is inserted. If the table at the Publisher has 5,000 rows, and you expect some growth in the table over the life of the application, the Publisher could use the range 1-10,000. Given two Subscribers, Subscriber A could use 10,001–20,000, and Subscriber B could use 20,001-30,000.After a Subscriber is initialized with a snapshot or through another means, execute DBCC CHECKIDENT to assign the Subscriber a starting point for its identity range. For example, at Subscriber A, you would execute DBCC CHECKIDENT('<TableName>','reseed',10001). At Subscriber B, you would execute CHECKIDENT('<TableName>','reseed',20001).To assign new ranges to the Publisher or Subscribers, execute DBCC CHECKIDENT and specify a new value to reseed the table. You should have some way to determine when a new range must be assigned. For example, your application could have a mechanism that detects when a node is about to use up its range and assign a new range using DBCC CHECKIDENT. You can also add a check constraint to ensure that a row cannot be added if it would cause an out of range identity value to be used.====Michael Blythe Technical WriterSQL Server Replication - Microsoft --------------------------------------------------------------------------- This posting is provided "AS IS" with no warranties, and confers no rights. |
 |
|
|
|
|
|
|
|
Merge Replication - Subscriotion Insert