locked
problem synching (is column name being truncated?) RRS feed

  • Question

  • Hi, we were implementing the synch framework and we came across a "duplicate key" error when the actual synch was taking place.
    We narrowed it down to a certain table where the error seemed to originate while adding a column to the table.
    We investigated our table and it had the following columns names:

    vervangingDirecteLeidinggevende1_id
    and
    vervangingDirecteLeidinggevende2_id

    We suspected that maybe the columnlength got truncated so we tried to change the name of the second column to
    TvervangingDirecteLeidinggevende2_id

    afterwards, the synch completed successfully.

    Is this behaviour documented somewhere? because we certainly couldn't find it.




    • Moved by Max Wang_1983 Tuesday, April 19, 2011 6:51 PM Forum consolidation (From:SyncFx - Feedback [ReadOnly])
    Thursday, August 14, 2008 7:00 AM

Answers

  • This is an issue with how the provider binds to CE. SQL CE has a length limit of 128 characters for the parameter name. The provider converts the column name to its unicode byte representation before binding. This multiplies the length 4 times. As a result columnnames greater than 32 characters get truncated and you see this error. For now you will have to have unique first 32 characters of the column names.

     

    thanks

    Sudarshan 

     

    Tuesday, September 9, 2008 8:20 PM
    Moderator

All replies

  • Sjors,

    Can you provide some more detail on what components of sync framework were you using to build the sync application? Was this the first sync? Was this exception raised by the CE provider?

     

    thanks,

    Sudarshan

     

    Thursday, September 4, 2008 5:58 PM
    Moderator
  • problem is really simple to recreate

    - create a new database in sql server 2005
    - add a new table
    - add an identity column marked as primary key
    - add a column with columnname "ThisColumnNameIsWayTooLongForSynch1"
    - add a column with columnname "ThisColumnNameIsWayTooLongForSynch2"
    - save table

    - create a new project in visual studio 2008
    - add a local data cache to the project
    - in the wizard point to the newly created database
    - in the wizard add the table 
    - hit ok

    the wizard will try to do it's first synch and reports the following error right away:

    Synchronizing the database failed with the message: 'an item with the same key has already been added.'

    Remember we haven't added any row to the table yet!.

    When you 
    - add a prefix to one of the two long columnnames so you have:
    - a column with columnname "ThisColumnNameIsWayTooLongForSynch1"
    - a column with columnname "WowThisColumnNameIsWayTooLongForSynch2"

    - start a new vis studio project and try the same thing, the error is not thown and the synch works as expected.



    Friday, September 5, 2008 6:52 AM
  • This is an issue with how the provider binds to CE. SQL CE has a length limit of 128 characters for the parameter name. The provider converts the column name to its unicode byte representation before binding. This multiplies the length 4 times. As a result columnnames greater than 32 characters get truncated and you see this error. For now you will have to have unique first 32 characters of the column names.

     

    thanks

    Sudarshan 

     

    Tuesday, September 9, 2008 8:20 PM
    Moderator
  • Hi Sudarshan,

    Thanks for your reply

    Just to be sure, the case in which we first encountered the problem did have a column length of 32+ characters.
    But the first 32 characters did make the column name unique.

    vervangingDirecteLeidinggevende1_id
    and
    vervangingDirecteLeidinggevende2_id

    Regards,

    Sjors Miltenburg

     Sudarshan Chitre [MSFT] wrote:

    This is an issue with how the provider binds to CE. SQL CE has a length limit of 128 characters for the parameter name. The provider converts the column name to its unicode byte representation before binding. This multiplies the length 4 times. As a result columnnames greater than 32 characters get truncated and you see this error. For now you will have to have unique first 32 characters of the column names.

     

    thanks

    Sudarshan 

     

    Friday, September 12, 2008 7:06 AM
  •  

    "But the first 32 characters did make the column name unique."

     

    I haven't had a chance to test this myself, but within that process it actually prepends P_ to the string.  So most likely its actually the first 30 chars that need to be unique.

     

    Also, did you experience this with VS SP1 or CE 3.5 SP 1 installed?  We only get the problem with those service packs.

     

    previous to SP 1 the SqlCeTableColumns.Add sub looked like:

    Public Sub Add(ByVal column As SqlCeInfoSchemaColumn)
        Me.hashedItems.Add(column.ColumnName, column)
        column.position = Me.counter++
    End Sub
     
    after SP 1
    Public Sub Add(ByVal column As SqlCeInfoSchemaColumn)
        Me.columns.Add(column.ColumnName, column)
        Me.parameters.Add(column.ParamName, column)
    End Sub

     

    So now the ParamName is explicitly used as a key to the dictionary and is failing if the first 30 chars aren't unique.

     

    cmw

    Friday, December 19, 2008 8:48 PM
  • ....................?????????????????????????????????????????????????????????????????????????????????????????????????????????????

     Sjors Miltenburg wrote:
    Hi, we were implementing the synch framework and we came across a "duplicate key" error when the actual synch was taking place.
    We narrowed it down to a certain table where the error seemed to originate while adding a column to the table.
    We investigated our table and it had the following columns names:

    vervangingDirecteLeidinggevende1_id
    and
    vervangingDirecteLeidinggevende2_id

    We suspected that maybe the columnlength got truncated so we tried to change the name of the second column to
    TvervangingDirecteLeidinggevende2_id

    afterwards, the synch completed successfully.

    Is this behaviour documented somewhere? because we certainly couldn't find it.




    Friday, January 2, 2009 8:26 PM