Problem when syncing with ID ranges.

Question

I got 3 DB's.

• 1 Azure
• 2 Local

These local databases have ID ranges.

DB1= [0 - 9999]
DB2= [10000 - 19999] 

The problem is when I'm trying to sync it gives errors against my constraints I specified for my ID ranges.

Example:

DB1 Add ID = 1

--> sync

DB2 (Needs an ID between 10000 - 19999) --> failed to sync!

Can I resolve this somehow? 

Answer 1

can you post the exact error you're getting?

Answer 2

Thanks for the fast reply!

Here is some detailed information about my constraints.

Sync completed with warnings in 25.16 seconds.
Upload:   0 changes applied/0 failed 
Download: 0 changes applied/16 failed

Download - 
errors for first 5 rows that failed to apply:
Error #1: SqlException Error Code: -2146232060 - SqlError Number:547, Message: The INSERT statement conflicted with the CHECK constraint "Id_Range_Constraint_tblParty". The conflict occurred in database "DZine_IStyling_Replica", table "dbo.tblParty", column 'PARTYID'. SqlError Number:3621, Message: The statement has been terminated. 


Error #2: SqlException Error Code: -2146232060 - SqlError Number:547, Message: The INSERT statement conflicted with the CHECK constraint "Id_Range_Constraint_tblPartyAgreementInvolvement". The conflict occurred in database "DZine_IStyling_Replica", table "dbo.tblPartyAgreementInvolvement", column 'PARTYAGREEMENTID'. SqlError Number:3621, Message: The statement has been terminated. 


Error #3: SqlException Error Code: -2146232060 - SqlError Number:547, Message: The INSERT statement conflicted with the CHECK constraint "Id_Range_Constraint_tblPartyAgreementInvolvement". The conflict occurred in database "DZine_IStyling_Replica", table "dbo.tblPartyAgreementInvolvement", column 'PARTYAGREEMENTID'. SqlError Number:3621, Message: The statement has been terminated. 


Error #4: SqlException Error Code: -2146232060 - SqlError Number:547, Message: The INSERT statement conflicted with the CHECK constraint "Id_Range_Constraint_tblPartyAgreementInvolvement". The conflict occurred in database "DZine_IStyling_Replica", table "dbo.tblPartyAgreementInvolvement", column 'PARTYAGREEMENTID'. SqlError Number:3621, Message: The statement has been terminated. 


Error #5: SqlException Error Code: -2146232060 - SqlError Number:547, Message: The INSERT statement conflicted with the CHECK constraint "Id_Range_Constraint_tblTelecom". The conflict occurred in database "DZine_IStyling_Replica", table "dbo.tblTelecom", column 'TELECOMID'. SqlError Number:3621, Message: The statement has been terminated.


For more information, provide tracing id ‘47295df2-efb9-4768-878f-c0601fbcd3a0’ to customer support.

Answer 3

you've implemented the range as a constraint so that would raise an error. if DB1 uploads an PartyID of 1, when that get's downloaded in DB2, the insert would fail because of the constraint which says the value should only be between 10000 to 19999.

if this is an identity column, you can set DB1 seed value to 1, DB2 seed value to 10000.  just make sure that the range dont overlap (e.g., make sure DB1 will only really use up to 9,999 and dont overlap to 10000)

Answer 4

Yeah that's the first thing I tried.

In this scenario I get:
DB1 Add ID  = 1
DB2 Add ID = 10 000

--> Sync = OK

DB1 Add some ID --> becomes --> 10 001 it increases the last entered ID in the table

This is why I did both constrain check and set seed...

So do you got any suggestions how to avoid this problem?

Answer 5

you're right. that's the behaviour you would get. if the identity value inserted with set identity_insert on is greater than the previous identity value, the next identity value is reset and will just increment the last inserted value.

my test app has some hacking done on SPs and i wouldnt advise you do the same on Data Sync :)

Answer 6

So the only option left is create a unique PK ??

My problem here is I got tables with some many relations that would cause so many changes :/

Aren't their other passes to avoid this ?

Answer 7

a uniqueidentifier with default to newid() is your best option for the PK. but yes, it requires some more work on the table relationships.

Answer 8

if you're up for it, try this: (NOTE: This is a hack, so do so at your own risk!)

modify the _bulkinsert stored procedure and add this just before the line SET IDENTITY_INSERT ON

declare @localdentityvalue bigint
set @localdentityvalue = IDENT_CURRENT('sampletable')

then add this line just after SET IDENTITY_INSERT OFF

DBCC CHECKIDENT(sampletable, RESEED, @localdentityvalue)

you must apply the changes on all replicas/copies you are synchronizing. what it does is you're storing and resetting the original, local identity value after sync applies its inserts. 

 

Answer 9

I'm gonna do it this way, and this have to be applied in the DataSync.tbl_dss_bulkinsert transaction right ?

So If I have a table tblParty I should make this changes ?

Answer 10

yes, except in my case i put the retrieval of the IDENT_CURRENT before the SET IDENTITY_INSERT ON, but i dont think that matters.

Answer 11

Ill try and let you know, thanks JuneT for this tip ;-)

Answer 12

It worked beautifull, hope I don't get to many issues on my way ;-)

Answer 13

you've been warned it will work but you're on your own :)

Answer 14

While this maybe a good way to try to workaround the problem, I would advise not to put this into production.

If there are product updates that end up regenrating metadata, then you will have lost all your changes

And generally product group advises customers to not modify internal structures and data. So if the internal metadata tables, SPs, triggers etc are going to be touched by the customers, and you have a support case, then it could not be looked at because what you maybe seeing is not the default behavior. And the product team cannot guarantee the behavior nor the consequences.

Just be aware of this and as June noted, you will be on your own :)

A better way is to change your schema to have a uniqueidentifier or a composite PK (maybe add a constant site/node identifier column)

---

This posting is provided AS IS with no warranties, and confers no rights

Answer 15

Hi JuneT,

I know this is an old thread, but I'm looking for a solution to the same type of problem and it sounds like you have successfully implemented this, but I took note of your warning multiple times throughout the thread.

Have you come accross any issues with this approach? We've developed a whole identity management functionality which reeds each client and the server and everything is working at the clients (because of the small caveat for SQL Compact that identity_insert doesn't reseed). The problem is at the server identity)insert is reseeding and if users add data through our web app it errors because it is outside the server range.

Is there any downside to your noted approach?

Thanks,

Mike

Answer 16

I haven't really tested the hack for other scenarios (thus, all the disclaimers and warnings).

my issue with this hack is that you have to always include the hack when inserting rows. its fine if you've encapsulated it in a stored proc, but then you always have to keep in mind to always use the stored proc as well.