SQL Server Destination: Collate Settings Causing Downstream Failures

Question

Operations on string fields are failing with an error similar to the following:

[Microsoft][ODBC Driver 17 for SQL Server][SQL Server] Cannot resolve collation conflict between ""Latin1_General_BIN"" and ""SQL_Latin1_General_CP1_CI_AS"" in the equal operation.      
[Microsoft][ODBC Driver 17 for SQL Server][SQL Server] Statement(s) could not be prepared.
content_copy

Environment

SQL Server connector

Answer

One of the following three options can typically resolve this issue:

1. Change the database_default collation to match that of Fivetran.

2. Account for this COLLATE setting in downstream operations, for example:

   JOIN ON fivetran_replicated_table.id COLLATE 
   SQL_Latin1_General_CP1_CI_AS = table.id COLLATE 
   SQL_Latin1_General_CP1_CI_AS
   content_copy

3. As a very last resort, contact Customer Support who can add a feature flag that allows joins to occur without collation. It is recommended that you only request this if you are confident that all character handling is handled properly in every source.

Cause

This error is caused by incompatible collations. Fivetran uses the collate encoding setting during syncs so that string comparisons (e.g., JOIN operations) can be performed in a way that handles:

• Case-sensitive strings
• Unusual or non-ASCII values

Its usage handles an edge case where a table has a primary key that uses strings (e.g., a result of a hash function).

Take two records that have primary keys with the same character representations but use different cases, such as abc and ABC. The COLLATE setting Latin1_General_BIN will recognize them as different values. This is because it uses case-sensitive encoding.

In contrast, if a setting such as SQL_Latin1_General_CP1_CI_AS is used (which uses case-insensitive encoding), it may mark the two records as the same, even though they're not. It could also cause an error similar to the following:

java.lang.RuntimeException: 
com.microsoft.sqlserver.jdbc.SQLServerException: Violation of PRIMARY KEY constraint 'PK__m_table__3213E83F132AEF4A'.   
Cannot insert duplicate key in object 'test_20201112_1923_14xzddh264qyu1c3b7sjs3yuix.m_table'. The duplicate key value is (abc).
content_copy