none
Synchronization Error Upgrade SQL Compact 3.5 to SP1 or SP2 long column names RRS feed

  • Question

  • I have been working on my Synchronization project for a couple of years now. This is a large project. We created the project in Visual Studio 2008 with SQL Compact 3.5 and ADO.NET Synchronization Services 1.0.

    Synchronization has been running fine until SQL Compact 3.5 SP1 was available. A few months ago we had new developers join our team. They unknowingly installed SQL Compact 3.5 SP1. They started receiveing the errors below. At the time we had them roll back to the basic 3.5 with no service pack. This resolved the error at that time.

    Now that Office 2010 is being rolled out soon in our company, we have had many test pc's upgraded to Office 2010. Now when we run our sync app on these machines, we start getting the same error as we have seen before (see below). After much research on the internet I found that Office 2010 is installing Business Connectivity Services (BCS).http://msdn.microsoft.com/en-us/library/ee557658.aspx With BCS being installed, I found that BCS also installs SQL Compact 3.5 SP1.

    So back to Synchronization and the service pack upgrade issue... I tracked down the sync stack trace error and it is blowing up when adding table columns to a Generic.Dictionary collection.

    Why is it that we see this significant change merely going to SP1 or SP2?

    I believe that what is happening is that some of our column names are being cut off reducing them to a size that matches another column of the same (now cut off) name.

    I know that we have columns with long names, but these should not be a factor. What I mean is that SP1 and SP2 should be backwards compatible. Why do we have this new functionality that is breaking my application? Due to the large scope of these column name changes through the database, the application code, the application sql, foreign keys, and indexes, we are not sure if we want to reduce the column name size or just move to synchronization with SQL Express. My guess is these errors will not be going away even if we move to SQL Express as our backend data source.

    Error:

    "an item with the same key has already been added"

    Stack Trace:

       at System.ThrowHelper.ThrowArgumentException(ExceptionResource resource)

       at System.Collections.Generic.Dictionary`2.Insert(TKey key, TValue value, Boolean add)

       at Microsoft.Synchronization.Data.SqlServerCe.SqlCeSyncUtil.GetTableColumns(SqlCeCommand cmdUtil, String tableName)

       at Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.ApplyChanges(SyncGroupMetadata groupMetadata, DataSet dataSet, SyncSession syncSession)

       at Microsoft.Synchronization.SyncAgent.DownloadChanges(SyncGroupMetadata groupMetadata)

       at Microsoft.Synchronization.SyncAgent.DataSynchronize()

       at Microsoft.Synchronization.SyncAgent.Synchronize()

    Can someone please explain what this new process is doing and why this new process has been released with SQL CE 3.5 SP1?

    This may be related to a change in a Synchronization upgrade but from my experience the error comes when upgrading SQL CE.

    Thanks, Jason

     


    Wired4This
    • Edited by Jason_Keith Tuesday, August 24, 2010 4:05 PM additional info
    Tuesday, August 24, 2010 3:30 PM

All replies

  • We would like to have a in-house repro of your issue.  Please provide the SQL CE table schema (with long column name mentioned) and other related tables which are of the same sync group. Thanks.


    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, September 3, 2010 12:41 AM
    Answerer
  • Here is one of our tables with a particularly long column name. Column 'NoticeSnapshot_CertificateLicenseTypeCapacityGUID' is at 49 chars.

    This is the schema from the central server. I assume you can easily recreate the schema for SQL CE.

    Thanks for looking at this, Jason 

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[tblNoticeSnapshot_CertificateLicenseTypeCapacity](
    	[NoticeSnapshot_CertificateLicenseTypeCapacityGUID] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_tblNoticeSnapshot_CertificateLicenseTypeCapacity_NoticeSnapshot_CertificateLicenseTypeCapacityGUID] DEFAULT (newid()),
    	[NoticeSnapshot_CertificateGUID] [uniqueidentifier] NOT NULL,
    	[LicenseTypeDesc] [varchar](50) NULL,
    	[LicenseTypeCapacity] [int] NULL,
    	[CreatedBy] [int] NOT NULL,
    	[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_tblNoticeSnapshot_CertificateLicenseTypeCapacity_CreatedDate] DEFAULT (getdate()),
    	[UpdatedBy] [int] NOT NULL,
    	[LastUpdated] [datetime] NOT NULL CONSTRAINT [DF_tblNoticeSnapshot_CertificateLicenseTypeCapacity_LastUpdated] DEFAULT (getdate()),
     CONSTRAINT [PK_tblNoticeSnapshot_CertificateLicenseTypeCapacity] PRIMARY KEY CLUSTERED 
    (
    	[NoticeSnapshot_CertificateLicenseTypeCapacityGUID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    SET ANSI_PADDING OFF
    

     I'm also adding another table to help recreate my issue for you.

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[refQualityProgramReviewDecisionTypes](
    	[QualityProgramReviewDecisionTypesID] [int] IDENTITY(1,1) NOT NULL,
    	[QualityProgramReviewDecisionTypesDesc] [varchar](50) NOT NULL,
    	[QualityProgramReviewDecisionTypesAbbr] [varchar](5) NULL,
    	[Active] [bit] NOT NULL CONSTRAINT [DF_refQualityProgramReviewDecisions_Active] DEFAULT ((1)),
    	[CreatedBy] [int] NOT NULL,
    	[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_refQualityProgramReviewDecisions_CreatedDate] DEFAULT (getdate()),
    	[UpdatedBy] [int] NULL,
    	[LastUpdated] [datetime] NOT NULL CONSTRAINT [DF_refQualityProgramReviewDecisions_LastUpdated] DEFAULT (getdate()),
     CONSTRAINT [PK_refQualityProgramReviewDecisions] PRIMARY KEY CLUSTERED 
    (
    	[QualityProgramReviewDecisionTypesID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    SET ANSI_PADDING OFF
    GO
    

     


    Wired4This
    • Edited by Jason_Keith Friday, September 3, 2010 3:52 PM added another table schema
    Friday, September 3, 2010 2:16 PM
  • Hello Jason,

    One question I have to ask you is, are you using the same CE file that was created using RTM and trying to open it in SP1?

    THanks,

    Patrick

    Tuesday, September 7, 2010 11:39 PM
  • Hi Jason,

    What I am seeing is that even if I upgrade from RTM to SP1 using the table structure you describe, I don't see column name being truncated when I open the file in SP1.

    Can you explain the scenario in which truncation happens little more?

    I seriously doubt Sync component will truncate the column name and this is where I am a little confused.

    Do you mean the metadata column names being truncated? or the data columns?

    Thanks,

    Patrick

    Wednesday, September 8, 2010 12:18 AM
  • Hi John,

    I was able to repro your problem. Here is what I found.

    I tested the following table scenario with 3 differnt versions of Sql Compact 3.5: RTM, SP1, and SP2.

    The cause of the problem seems to be that the two columns with long length have same name up to certain point (e.g. QualityProgramReviewDecisionTypes[ID | Desc])

    When I tried the 3 versions, they all failed with the error you described. Strangely enough, on RTM, this also failed which is contrary to what you mentioned above.

    The table looks like below:

    CREATE TABLE [table] (_track_insert varbinary(8) NOT null unique, col_int int not null primary key, QualityProgramReviewDecisionTypesID int, QualityProgramReviewDecisionTypesDesc uniqueidentifier)

    So, to pinpoint the problem, I reduced the length of column names to the following.

    CREATE TABLE [table] (_track_insert varbinary(8) NOT null unique, col_int int not null primary key, DecisionTypesID int, DecisionTypesDesc uniqueidentifier) and it worked on all 3 versions.

    We recognize that this is some limitation on SQL CE. We will delegate this problem to a responsible team, and in the mean time, we kindly ask you to reduce the column name length. I personally searched for the hard number on this limit, but I was not able to find anything yet. We know the above 16~20 chars should be fine.

    I want to ask you the following questions: - Initially, which SQL CE 3.5 version did you use? - Is it possible that you provide us trace information? To turn on tracing, take a look at http://msdn.microsoft.com/en-us/library/ms733025.aspx Please use level 4 verbose mode.

    Thank you,

    Patrick

    Wednesday, September 8, 2010 5:56 PM
  • Thanks Patrick for the time you have invested in this problem. I am very happy to see you reproduce my problem.

    I am sure you are correct when you say: "The cause of the problem seems to be that the two columns with long length have same name up to certain point"

    For an answer to one of you previous questions, we see the "same key" error when we kick off synchronization from the application.

    Here is a link to the version we installed.

    Microsoft SQL Server Compact 3.5 and Microsoft Synchronization Services for ADO.Net v1.0 for Windows Desktop

    http://www.microsoft.com/downloads/details.aspx?FamilyID=7849b34f-67ab-481f-a5a5-4990597b0297&displaylang=en

    When we install and use this version we do not see any sync errors.

    I'll try the tracing option and give you the results.

    Thanks, Jason


    Wired4This
    Thursday, September 9, 2010 3:42 PM
  • Patrick, When you say "We recognize that this is some limitation on SQL CE", do you think a solution would be to move to SQL Express as our local database?
    Wired4This
    Thursday, September 9, 2010 8:40 PM
  • Hi Patrick,

    I did some testing (reflecting on the Sync Fx code) and this seem to be within Sync Framework and not SQL CE.

    The call to Microsoft.Synchronization.Data.SqlServerCe.SqlCeSyncUtil.GetTableColumns has a function ParameterNameOf() which takes the column name and generates a value in the format @P_xxxxxx.

    Inside ParameterNameOf function, there is a piece of code that checks if the length of the columnname is greater than 0x80 and trims it.

    Using the same column names you used in your test, i was able to confirm the trimming and this fires up the error when the paramater is added to the dictionary.

    Friday, September 10, 2010 1:27 AM
    Moderator
  • Hi JuneT and Jason,

    Looks like this issue is indeed in the Sync Fx SQL CE Client Provider.

    Here is a related thread I dug up:

    http://social.microsoft.com/Forums/en-US/uklaunch2007ado.net/thread/ef6be283-3084-4e00-a997-3764bac1f35d

    Looks like solution might be to shorten column name to something shorter, or to use mapping for columns on client side.

    Another work around might be to define InsertCommand, UpdateCommand, and DeleteCommand on SyncAdapter.

    The main issue is that the code tries to generate parameter names in byte representation and chops off by 128.

    "Basically what it boils down to is, you can't have two columns within the same table that have the first same 30 characters."

    This issue is fixed in Peer to Peer Sync Provider, however. So, moving up to peer to peer provider would be a future consideration.

    Hope this helps,

    Patrick

    • Proposed as answer by Patrick S. Lee Monday, September 20, 2010 6:18 PM
    Monday, September 20, 2010 6:17 PM