locked
Problem when syncing with ID ranges. RRS feed

  • 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? 

    Monday, February 20, 2012 10:55 AM

Answers

  • 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. 

     
    • Marked as answer by daageu Monday, February 20, 2012 2:40 PM
    Monday, February 20, 2012 12:45 PM
    Answerer

All replies

  • can you post the exact error you're getting?
    Monday, February 20, 2012 10:59 AM
    Answerer
  • 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.




    • Edited by daageu Monday, February 20, 2012 11:12 AM
    Monday, February 20, 2012 11:01 AM
  • 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)

    Monday, February 20, 2012 11:15 AM
    Answerer
  • 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?
    • Edited by daageu Monday, February 20, 2012 11:48 AM
    Monday, February 20, 2012 11:17 AM
  • 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 :)


    • Edited by JuneTEditor Monday, February 20, 2012 12:09 PM
    Monday, February 20, 2012 12:05 PM
    Answerer
  • 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 ?
    • Edited by daageu Monday, February 20, 2012 12:09 PM
    Monday, February 20, 2012 12:07 PM
  • a uniqueidentifier with default to newid() is your best option for the PK. but yes, it requires some more work on the table relationships.
    Monday, February 20, 2012 12:09 PM
    Answerer
  • 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. 

     
    • Marked as answer by daageu Monday, February 20, 2012 2:40 PM
    Monday, February 20, 2012 12:45 PM
    Answerer
  • 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 ?

    • Edited by daageu Monday, February 20, 2012 1:27 PM
    Monday, February 20, 2012 1:23 PM
  • 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.

    Monday, February 20, 2012 1:36 PM
    Answerer
  • Ill try and let you know, thanks JuneT for this tip ;-)
    Monday, February 20, 2012 1:43 PM
  • It worked beautifull, hope I don't get to many issues on my way ;-)
    Monday, February 20, 2012 2:40 PM
  • you've been warned it will work but you're on your own :)
    • Edited by JuneTEditor Monday, February 20, 2012 3:07 PM
    Monday, February 20, 2012 3:06 PM
    Answerer
  • 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

    Monday, February 20, 2012 11:40 PM
  • 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

    Sunday, January 6, 2013 12:06 AM
  • 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.

    Sunday, January 6, 2013 3:01 AM
    Answerer