This article was created in response to a support issue logged with K2. The content may include typographical errors and may be revised at any time without notice This article is not considered official documentation for K2 software and is provided “as is” with no warranties.

Issue

When using the Database consolidation tool, an error message appears during the Workspace step ("Failed"). In the log file it appears the locale id '1033' of the source column 'Name' and the locale id '1036' of the destination column 'Name,' do not match.
If your SQL server is in French:
L'ID '1033' des paramètres régionaux de la colonne source 'Name' ne correspond pas à l'ID '1036' des paramètres régionaux de la colonne de destination 'Name'.
The full error message appears like this:

14:0: FrmMain.UpdateInfo: 15:37:46:>> Data Transfer Event: Begin processing table: [Workspace].[Action]
 Error: Aggregate exception: System.AggregateException: Une ou plusieurs erreurs se sont produites. ---> Microsoft.SqlServer.Management.Common.TransferException: An error occurred while transferring data. See the inner exception for details. ---> System.InvalidOperationException: L'ID '1033' des paramètres régionaux de la colonne source 'Name' ne correspond pas à l'ID '1036' des paramètres régionaux de la colonne de destination 'Name'.
 à System.Data.SqlClient.SqlBulkCopy.AnalyzeTargetAndCreateUpdateBulkCommand
(BulkCopySimpleResultSet internalResults)
   à System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestContinuedAsync
(BulkCopySimpleResultSet internalResults, CancellationToken cts, TaskCompletionSource`1 source)

Symptoms

Using the consolidation tool results in a "Failed" error message due to collation being used.

Troubleshooting Steps

By executing the following queries for language and collation analysis, check that all the databases are in the same collation "French_CI_AS"/"Latin1_General_CI_AS":

  • Server collation and language
SELECT CONVERT (varchar, SERVERPROPERTY('collation')) AS "Server Collation",  @@language  AS "Server Language"
  • Server language
SELECT *
  FROM sys.syslanguages
 WHERE langid=@@langid
  • List all the databases with corresponding collation
SELECT Databases.* 
  FROM Sys.Databases
  • Analyze the columns
SELECT OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) AS [Schema],  
        T.[name] AS [table_name], AC.[name] AS [column_name],  
        TY.[name] AS system_data_type, AC.[max_length], 
        AC.[precision], AC.[scale], AC.[is_nullable], AC.[is_ansi_padded],AC.Collation_name
  FROM sys.[tables] AS T  
       INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id] 
       INNER JOIN sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id] AND AC.[user_type_id] = TY.[user_type_id]  
 WHERE T.[is_ms_shipped] = 0 
      --AND  AC.name like '%AIStartDate%'
      AND  (T.[name] = 'Action' AND OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) = 'WorkSpace')
      OR  AC.name like 'Name'
ORDER BY T.[name], AC.[column_id]

If the collation is not identical, you can change it by doing the following:    
(For example: only the K2 Workspace has a collation of "French_CI_AS", and the other is "Latin1_General_CI_AS")

  1. Create a script from the database (with the data):
    • Right click on the database "K2 WorkSpace", Task, Generate Script
    • Click on the Next button
    • Select "Script entire database and all database objects" and click on "Next" button
    • Click on the "Advanced" button and change "Type of data to script": to "Schema and data." Click on OK
    • Check the output directory and click on the "Next" button
    • Click on Next button
  2. Delete the database "K2 WorkSpace"
  3. Create the K2 WorkSpace database with the correct collation ("Latin1_General_CI_AS",)
  4. Modify the script in step 1 and remove the database creation part
  5. Launch the script.