none
Sync provisioning not honouring column collation RRS feed

  • Question

  • We use SyncFW 2.1 to sync an on-premise SQL 2008 database with SQL Azure. Even though the default collation in Azure is SQL_Latin1_General_CP1_CI_AS, our tables/columns are created with collate specified as Latin1_General_CI_AS to match our on-premise databases.

    eg.
    CREATE TABLE [dbo].[MyMetadata](
     [Id] [int] NOT NULL,
     [Key] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
     [Value] [nvarchar](max) COLLATE Latin1_General_CI_AS NOT NULL,
     CONSTRAINT [PK_MyMetadata] PRIMARY KEY CLUSTERED
    (
     [Id] ASC,
     [Key] ASC
    ))

    When a PK column with a collation (as in MyMetadata table above) is provisioned by SqlSyncScopeProvisioning all of the stored procs, triggers and even the tracking table get created with the default database collation which is SQL_Latin1_General_CP1_CI_AS in Azure. Since this doesn't match the collation of the columns in the original table we get the following exception from SqlSyncScopeProvisioning.Apply():

    Unhandled Exception: System.Data.SqlClient.SqlException: Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
      at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
      at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
      at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
      at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
      at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
      at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
      at Microsoft.Synchronization.Data.SqlServer.SqlSyncTrackingTableHelper.PopulateFromBaseTable(SqlTransaction trans)
      at Microsoft.Synchronization.Data.SqlServer.SqlSyncTableProvisioning.Apply(SqlTransaction trans)
      at Microsoft.Synchronization.Data.SqlServer.SqlSyncScopeProvisioning.ApplyScope(SqlConnection connection)
      at Microsoft.Synchronization.Data.SqlServer.SqlSyncScopeProvisioning.ApplyInternal(SqlConnection connection)
      at Microsoft.Synchronization.Data.SqlServer.SqlSyncScopeProvisioning.Apply()

    In order to get provisioning to work right now, we generate the provisioning code to a script and manually add collation to necessary PK columns in CREATE TABLE and TABLE VAR statements.

    Is there any way to get provisioning code in sync framework to hounour the collation of the columns and avoid manually editing the script?

    Regards,

    Tim.

    Wednesday, January 19, 2011 11:46 PM

Answers

  • Hi Tim,

    We've faced the same problem and the only way I could see to solve it was to do what you guys have done, add the collation information to the script manually.

    Would be interested to see MS thoughts on this.

    Cheers,

    Andrew.

    • Marked as answer by Tim2065 Friday, January 21, 2011 4:38 AM
    Thursday, January 20, 2011 11:41 AM

All replies

  • Hi Tim,

    We've faced the same problem and the only way I could see to solve it was to do what you guys have done, add the collation information to the script manually.

    Would be interested to see MS thoughts on this.

    Cheers,

    Andrew.

    • Marked as answer by Tim2065 Friday, January 21, 2011 4:38 AM
    Thursday, January 20, 2011 11:41 AM
  • Thursday, January 20, 2011 2:16 PM
    Moderator
  • June,

    Thanks. Yes I have seen this thread. The work-around implemented there was to change all databases/columns to match the Azure default collation. This is not really an option in our case - and I guess for most people this isn't a good option either.

    The problem is clearly a bug in the provisioning code that I think would need fixing.

    Regards,

    Tim

    Btw, in the interest of helping others to avoid the issue, it only occurs where the primary key contains a column with different collation from the default of the database being provisioned. If all your tables have only INT or GUID primary keys, you don't encounter the problem.

    Thursday, January 20, 2011 9:24 PM
  • Thanks Tim and JuneT for the discussion here with this post.

    We understand the inconvenience due to this issue and glad you get it worked out using the generated scripts. yes, this is a known issues to the SyncFx. We will keep it on our radar and make proper decision for the future releases regarding with the non-default collations.

    thanks

    yunwen


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, January 20, 2011 10:03 PM
    Moderator