OpenSync

Home Forums OpenSync Sync fails in Opensync3

This topic contains 55 replies, has 10 voices, and was last updated by  tony722 2 days, 4 hours ago.

Viewing 11 posts - 46 through 56 (of 56 total)
  • Author
    Posts
  • #67347

    Frank
    Participant

    Using Version 3.0.28 same issue, logs attached:

    Failed: Violation of PRIMARY KEY constraint ‘PK_itemnoninventory’. Cannot insert duplicate key in object ‘dbo.itemnoninventory’. The duplicate key value is (8000049C-1596268091).: itemnoninventory SalesAndPurchase in table items:-2147217873

    Attachments:
    You must be logged in to view attached files.
    #67353

    Yudel Rosales
    Keymaster

    You will have to remove the primary key and the index on those tables in order to run. That happens with the old QuickBooks company file.

    #67356

    Frank
    Participant

    The company file is brand new, I sold the company and they have QB 2020 I have 2019, I made a new databease and am just connecting to it but the company file in quickbooks is new also.Is this solution still right knowing all that?

    #67357

    Frank
    Participant

    I removed it and it worked, we are good thanks

    #67361

    Yudel Rosales
    Keymaster

    you are very welcome

    #68247

    tony722
    Premium Member

    This is an issue again in 3.0.31.

    I really don’t like the idea of deleting the PK indexes as they provides a lot of sanity checking and data consistency (the last thing I need is for there to be duplicate items in the table with the same key) . . . not to mention performance benefits of using indexes.

    So far gotten the error with iteminventory, itemnoninventory, itemothercharge, and itemsubtotal

    #68248

    tony722
    Premium Member

    And yes removing the index does indeed create duplicate items. So deleting the index is not an option. (See attached screenshot)

    Attachments:
    You must be logged in to view attached files.
    #68257

    Yudel Rosales
    Keymaster

    I emailed you

    #68258

    tony722
    Premium Member

    One work-around is to add an “INSTEAD OF INSERT” trigger on each of these tables in Microsoft SQL Server which effectively does an “upsert” (update record if key exists, otherwise insert).

    Attached is a script to create these triggers using SQL Server Management Studio.

    Note, while I wouldn’t expect this to be an issue for anyone, there is a chance this could have a side affect on your business logic if you are inserting records into any of the “items” tables and you are depending on a SQL server error being thrown if a duplicate key exists (because it will now just automatically update the existing record if you insert a duplicate record into any of the “items” tables–rather than throwing an error).

    Attachments:
    You must be logged in to view attached files.
    #68261

    mhuckaby
    Premium Member

    I’m having the same issue, including with the estimate table. I’m using MySQL.

    #68262

    tony722
    Premium Member

    Yeah, unfortunately it doesn’t look like my workaround would work with MySQL as MySQL lacks the ability to do “INSTEAD OF” triggers.

Viewing 11 posts - 46 through 56 (of 56 total)

You must be logged in to reply to this topic.