locked
SQL Execution Error RRS feed

  • Question

  • Hey all..

     

    I'm using CE Sync Services 3.5 with a VB.NET 2.0 desktop windows application. Recently we made some record updates (changed some EXISTING record values from an update tsql statement using a asp.net 2.0 web page -- the create_timestamp, update_timestamp, create_date  was all set to = Now... and the update_originator_id was set to = 0) on one of the tables and we also added a few new columns to the above mentioned table as well as a couple others tables in the database.

     

    The values are correct in the snyc class and the SQL CE Database sync's to the client but after trying to insert or update a value in the vb.net 2.0 application using the local SQL CE database any update or insert attempt fails. 

     

    So...

    1. I fired up my trusty VS app code debugger and captured the application SQL insert statement with values from the app... 
    2. I then closed the VS  application... 
    3. then I opened the local SQL CE database and did a New Query and pasted the app insert SQL statement into the New CE Query and it is returning: 

    SQL Execution Error

    • Error Source: SQL Server Compact Edition ADO.NET Data Provider.
    • Error Message: A duplicate value cannot be inserted into a unique index. [Table name =  Detail,Constraint name = PK_Detail]

    The application code was not changed at all and worked 99.999% of the time before deleting the local copy of the SQL CE database and then re-syncing the application with the SQL 2005 Server containing the table changes. The data was left on the SQL 2005 Server from prior sync's and not deleted before syncing a new copy of CE from the app sync class.

     

    • The records in the table remained on the SQL Server 2005 server and not deleted... Only the local CE database on the client was removed then put back using a Sync Class. 

     

    • I've double checked to see if the correct values/columns are in the sync class and both database's are indentical on the SQL 2005 as in the local CE db.

     

    • Do I need to remove the SQL 2005 table data and delete the local CE database... then re-Sync the local CE with SQL 2005?

     

    Has anyone else experienced this before or know how to fix it?

     

    Kind regards,

    Bill

    • Moved by Max Wang_1983 Friday, April 22, 2011 10:51 PM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Thursday, May 10, 2007 1:35 AM

All replies

  • Hey sync homies... Identity Crisis Solved! Much like in life if something is constraining it should be removed! LOL... not sure how the constraints got into my table but removing them and re-syncing from the SQL 05 server did the trick! thanks, billb.
    Friday, May 11, 2007 6:35 AM
  • Glad to see the problen was identifid and resolved! I was thinking if there were a bug that could lead to dup rows being sent.

     

    thanks Bill. do post any issues found in your sync app.

     

    thanks

    Yunwen

    Friday, May 11, 2007 4:37 PM
    Moderator
  •  

    Thanks Yunwen not 100% solved yet! The inserts in the application work great but after syncing with the server.. the problem returns if the prior data is not removed from the SQL Server 2005! I had to remove the constraints from the SQL Server and I am not totally 100% sure how the contraints got in the table... maybe a bug.. maybe not!

     

    When I remove the table data from the SQL Server 2005 everything works.... fun stuff!

     

    • I've found the problem to be a unique index that returns to the local sdf after syncing with the Server: UQ__Detail__000000000000003F from the Primary Index Name: PK_Detail <-------- THESE LITTLE GUYS DO NOT PLAY WELL WITH OTHERS... MEANING THAT IF EXISTING TABLE DATA IS LEFT ON THE SQL SERVER THE ERROR RETURNS AND MY APP FAILS ON INSERTS etc..! 

     

    • If the SQL Server Table data is removed... BAM... The Application Works 100% again assuming I keep the original local CE database! 

     

    How can I keep prior table data WITHOUT having to delete it on the SQL SERVER 2005 everytime we make changes?

     

    Thanks,

    Bill

     

     

     

     

    Friday, May 11, 2007 5:37 PM
  • Hey sync mates... got it goin on.... lol... here' s my solution to bouncing tables! Rafik... let me know your thoughts.. Smile

     

    The table that was giving me trouble was returning the below exception:

     

    SQL Execution Error

    • Error Source: SQL Server Compact Edition ADO.NET Data Provider.
    • Error Message: A duplicate value cannot be inserted into a unique index. [Table name =  Detail,Constraint name = PK_Detail]

     

    So after playing with the sync code for a bit and much testing I found this to work real well. NOTE: Rafik's old code:

     

     

    • 1 ' Create SyncTables and SyncGroups

      ' To sync a table, a SyncTable object needs to be created and setup with desired properties:

      ' TableCreationOption tells the agent how to initialize the new table in the local database

      ' SyncDirection is how changes from with respect to client {Download, Upload, Bidirectional or Snapshot}

      '

      ''DEFAULT IS TruncateExistingOrCreateNewTable and UploadOnly FOR THIS TO WORK WITHOUT ERROR

      Dim tableDetail As SyncTable = New SyncTable("Detail")

      tableDetail.CreationOption = TableCreationOption.TruncateExistingOrCreateNewTable

      tableDetail.SyncDirection = SyncDirection.UploadOnly

    The above #1 source works....  But if the SyncDirection is set to SyncDirection.Bidirectional it caused the error to occur after a user synced his/her local db.

     

     If you set the SyncDirection to SyncDirection.UploadOnly as illustrated above in #1 the SQL Execution Error... A duplicate value cannot be inserted into a unique index magically disappears and the app syncs great! Very cool stuff to mess around with for your specific needs.

    • 2 ' Create sync adapter for each sync table and attach it to the agent

      ' Following DataAdapter style in ADO.NET, SyncAdapte is the equivelent for

      ' Sync. SyncAdapterBuilder is a helper class to simplify the process of

      ' creating sync commands.

      '

      Dim DetailBuilder As SqlSyncAdapterBuilder = New SqlSyncAdapterBuilder()

      DetailBuilder.Connection = serverConnection

      DetailBuilder.SyncDirection = SyncDirection.Bidirectional

     

    Enjoy! Any feedback... thoughts welcomed.

    billb 

    Saturday, May 12, 2007 3:55 AM