none
collation conflict while provisioning Sql Server DB RRS feed

  • Question

  • I'm trying to get a simple Sql-Server to Sql-Azure sync setup using technique found here:

    http://blogs.msdn.com/b/sync/archive/2010/08/31/sql-server-to-sql-azure-synchronization-using-sync-framework-2-1.aspx

    But I seem to be failing at the first hurdle:  provisioning my on-premise DB.  I am using the following code to provision:

    		private static void Setup()
    		{
    			var scopeDescription = GetSyncScope();
    
    			ProvisionDb( scopeDescription, OnPremiseConnectionString );
    
    			ProvisionDb( scopeDescription, AzureConnectionString );
    		}
    
    		private static DbSyncScopeDescription GetSyncScope()
    		{
    			var scopeDescription = new DbSyncScopeDescription( ALL_TABLES_SCOPE );
    
    			foreach (var table in _tablesInDependencyOrder)
    			{
    				using (var onPremiseConn = new SqlConnection( OnPremiseConnectionString ))
    				{
    					var description = SqlSyncDescriptionBuilder.GetDescriptionForTable( table, onPremiseConn );
    
    					scopeDescription.Tables.Add( description );
    				}
    			}
    
    			return scopeDescription;
    		}
    
    		private static void ProvisionDb( DbSyncScopeDescription scopeDescription, string connectionString )
    		{
    			using (var connection = new SqlConnection( connectionString ))
    			{
    				var provisioning = new SqlSyncScopeProvisioning( connection, scopeDescription );
    				if (!provisioning.ScopeExists( ALL_TABLES_SCOPE ))
    					provisioning.Apply();
    			}
    		}
    

    But am getting the following exception when I try to "ProvisionDb( scopeDescription, OnPremiseConnectionString );":

     

    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()
      at Slps.Online.Tools.DbSync.Program.ProvisionDb(DbSyncScopeDescription scopeDescription, String connectionString) in F:\Slps\Branches\20100906\dev\Slps.Online.Tools.DbSync\Program.cs:line 138
      at Slps.Online.Tools.DbSync.Program.Setup() in F:\Slps\Branches\20100906\dev\Slps.Online.Tools.DbSync\Program.cs:line 110
      at Slps.Online.Tools.DbSync.Program.Main() in F:\Slps\Branches\20100906\dev\Slps.Online.Tools.DbSync\Program.cs:line 43

    Any assistance would be appreciated....

     

     

    Wednesday, October 27, 2010 2:37 PM

Answers

  • Hi,

    SyncFx doesn't handle collation difference for your databases. Please check both your on-premise SQL Server and SQL Azure tables that you plan to sync. For on-premise SQL Server, please make sure that  every table column's collation (Especially PK in you case since you are not using filtering) match to the default database collation. For SQL Azure, its database collation can only be SQL_Latin1_General_CP1_CI_AS. Please ensure every PK column of your tables have the same collation too.

    When SyncFx create the sync metadata table for each data table, the PK column of your data table will be stored in sync metadata table with the database default collation. If the columns in two tables have different collations, TSQL doesn't allow them to join to each other directly.

    Thanks,
    Dong


    This posting is provided AS IS with no warranties, and confers no rights.
    Wednesday, October 27, 2010 5:01 PM
    Moderator

All replies

  • I managed to change the collation of the On-Premise D to 

    SQL_Latin1_General_CP1_CI_AS

    (I'm not sure if I'll be able to do this against my production DB, but I just want to see if I can get sync working), and tried again - but then I get the same exception, but with the two collations reversed:

     

    Unhandled Exception: System.Data.SqlClient.SqlException: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

     

    for what it's worth, the Server collation is Latin1_General_CI_AS - I have no idea where the other collation name is coming from...

    Wednesday, October 27, 2010 3:40 PM
  • If you changed your on-premise DB to 'SQL_Latin1_General_CP1_CI_AS' and your Server is set to "Latin1_General_CI_AS", then you will get conflict errors. As in most database data scenarios, you need to have matching collations in order to share data.
    Wednesday, October 27, 2010 4:59 PM
  • Hi,

    SyncFx doesn't handle collation difference for your databases. Please check both your on-premise SQL Server and SQL Azure tables that you plan to sync. For on-premise SQL Server, please make sure that  every table column's collation (Especially PK in you case since you are not using filtering) match to the default database collation. For SQL Azure, its database collation can only be SQL_Latin1_General_CP1_CI_AS. Please ensure every PK column of your tables have the same collation too.

    When SyncFx create the sync metadata table for each data table, the PK column of your data table will be stored in sync metadata table with the database default collation. If the columns in two tables have different collations, TSQL doesn't allow them to join to each other directly.

    Thanks,
    Dong


    This posting is provided AS IS with no warranties, and confers no rights.
    Wednesday, October 27, 2010 5:01 PM
    Moderator
  • Wendy/Dong

    Thanks for your replies.  I'm documenting my findings here for others that may be looking at Sql Server <-> Azure scenarios

    Wendy: I guess my initial problem was that I had not changed collation of the DB - My understanding is that if a DB is set to a particular collation different to the server collation, then all objects created within that DB are created with the *db collation*, so I was not expecting to see any problems.  My initial reaction was that SyncFx was trying to create objects using a different collation. But as Dong points out, SyncFx makes no assumptions about collation, so the problem is something else

    Dong: I believe that you hit the nail on the head.  I executed the following query against my DB:

    select distinct COLLATION_NAME from INFORMATION_SCHEMA.COLUMNS
    

    and found that my on premise DB actually uses 3 different collations (not including the NULL collation which I guess is "default").

    In order to fix this, I need to change the DB to use a single collation, which according to Dong should be the server collation. (Dong: Can you clarify: should I normalize my collations to all be the Azure collation?  if on-premise and azure were different collations, would this cause problems?)

    To fix the DB to be the same as Server Collation I used:

    alter database SLP collate Latin1_General_CI_AS
    

    However, this change does not fix any objects that already have been created with a different collation. So, I used the following code (which I found on http://www.db-staff.com/index.php/microsoft-sql-server/69-change-collation) to change the collations of objects within my DB:

    declare
      @NewCollation varchar(255)
      ,@Stmt nvarchar(4000)
      ,@DBName sysname
    set @NewCollation = 'Latin1_General_CI_AS' -- change this to the collation that you need
    set @DBName = DB_NAME()
     
    declare
      @CName varchar(255)
      ,@TName sysname
      ,@OName sysname
      ,@Sql varchar(8000)
      ,@Size int
      ,@Status tinyint
      ,@Colorder int
     
    declare curcolumns cursor read_only forward_only local
    for select
        QUOTENAME(C.Name)
       ,T.Name
       ,QUOTENAME(U.Name) + '.' +QUOTENAME(O.Name)
       ,C.Prec
       ,C.isnullable
       ,C.colorder
      from syscolumns C
       inner join systypes T on C.xtype=T.xtype
       inner join sysobjects O on C.ID=O.ID
       inner join sysusers u on O.uid = u.uid
      where T.Name in ('varchar', 'char', 'text', 'nchar', 'nvarchar', 'ntext')
       and O.xtype in ('U')
       and C.collation != @NewCollation
      and objectProperty(O.ID, 'ismsshipped')=0
      order by 3, 1
     
    open curcolumns
    SET XACT_ABORT ON
    begin tran
    fetch curcolumns into @CName, @TName, @OName, @Size, @Status, @Colorder
    while @@FETCH_STATUS =0
    begin
     set @Sql='ALTER TABLE '+@OName+' ALTER COLUMN '+@CName+' '+@TName+ isnull ('('
    +convert(varchar,@Size)+')', '') +' COLLATE '+ @NewCollation
    +' '+case when @Status=1 then 'NULL' else 'NOT NULL' end
     exec(@Sql) -- change this to print if you need only the script, not the action
     fetch curcolumns into @CName, @TName, @OName, @Size, @Status, @Colorder
    end
    close curcolumns
    deallocate curcolumns
    commit tran
    

     

    After running this, the exception no longer occured.

     

    Thanks for help everyone.

    Pete

     

     

     

     

     

    Thursday, October 28, 2010 9:17 AM
  • Hi,

    I'm glad to know that your figure out the root cause of your scenario. Regarding on-premise database ans SQL Azure database have different collations, SqlSyncProvider doesn't care about it, and sync should just work. The only exception is that the two collations have different unicode handling. For example, varchar type of column in Japanese collation can store unicode characters, but it is not true for Latin collation. If you sync a varchar column that contains a unicode string from Japanese collation database to a latin collation database, every two-byte character in this string will be truncated to be single byte. It is controlled by SQL Server and is not related to SqlSyncProvider.

    Thanks,
    Dong


    This posting is provided AS IS with no warranties, and confers no rights.
    Thursday, October 28, 2010 5:52 PM
    Moderator