KNOWLEDGE BASE

Error Code: 2F0F5E42 and Collation Conflict With Extract Creation Using Microsoft SQL Server and Flat File Data on Tableau Desktop


Published: 23 Jan 2024
Last Modified Date: 06 Feb 2024

Issue

The following error occurs when creating an extract of a data source that combines Microsoft SQL server data and flat file data starting with Tableau Desktop 2023.1 or newer.

Error message:
Error Code: 2F0F5E42
[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "XXXXX" in the equal to operation.
[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared.
Unable to create extract

Environment

  • Tableau Desktop 2023.1 or newer
  • Microsoft SQL Server
  • Flat files (Excel, csv, etc)

Resolution

Below are possible workarounds:

Workaround 1
Create a TDC file following this Knowledge article, and place it in the "My Tableau Repository\Datasources" directory.

Workaround 2
Use Tableau Desktop 2022.4 or older.

Workaround 3
Use a live connection.

Workaround 4
Blend the flat file data to the Microsoft SQL Server data source.

Workaround 5
Change the columns collation of the field specified in the join condition in Microsoft SQL Server side, if possible.
Note: According to Microsoft website, you can change column collation for charvarchartextncharnvarchar, and ntext type fields only. Note that changing the column collation risks index corruption, etc.

Below is a sample query for changing the Column-level collations:

ALTER TABLE dbo.TableName ALTER COLUMN FieldName DataType(digit) COLLATE SQL_Latin1_General_CP1_CI_AS;

ALTER TABLE dbo.test ALTER COLUMN ID nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS;

Cause

This behavior is related to a known issue with Reference ID W-14783051.
Did this article resolve the issue?