Skip to main content
To homepage To homepage

Error: The locale id of the source column and the locale id of the destination column do not match

Christian Hauggaard
Community Manager
Forum|alt.badge.img+5

Symptoms

Executing a data area table results in an error message.

The locale id '####' of the source column '<ColumnName>' and the locale id '####' of the destination column '<ColumnName>' do not match.

Cause

There are two potential causes for this error:

  1. The database used for the Prepare instance and one or more of the source databases are using different collation options. Check the collation settings in SSMS to verify whether this is the case.
  2. A numeric field is being converted to a text field, and you are using the Ingest Instance SQL storage. 

Resolution

Depending on the cause above, the error can be resolved by:

  1. Change the collation of the databases so that they match each other. Alternatively change the collation of the problematic columns within the database. Alternatively create a custom view specifying the collation. 
  2. Use a data type override for the relevant numeric column in the Ingest Instance data source similar to below in order to convert the numeric field to text prior to bringing it into your data area. 

     

    Did this topic help you find an answer to your question?

    4 replies

    P
    Contributor
    Forum|alt.badge.img+2

    Hi @Christian Hauggaard 

    We have an issue where the collation of the data source DB (Chinese_PRC_CI_AS AS) and the collation of the ODX SQL Storage DB (SQL_Latin1_General_CP1_CI_AS) do not match. Using COLLATE on the columns in a Query table results in “locale id” error message above. Is the only solution to change collation on the ODX Storage DB?

    Best,

    Pontus 

    Christian Hauggaard
    Community Manager
    Forum|alt.badge.img+5

    Hi @pontus.berglund I have tried reproducing the issue you describe. I am able to transfer successfully from a SQL source with collation Chinese_PRC_CI_AS to ODX SQL storage with collation SQL_Latin1_General_CP1_CI_AS. Do you have some sample data I can test with which you believes results in a collation error?

    P
    Contributor
    Forum|alt.badge.img+2

    Hi @Christian Hauggaard ,

    Sorry, I was not very detailed in my original comment. The problem is not that we are unable to read from the source, but that Chinese characters show up as “???” in the ODX SQL DB table. In our attempt to solve this, we tried using the COLLATE clause in a query table like this:

    SELECT [ChineseColumn] COLLATE Chinese_PRC_CI_AS FROM [dbo].[ChineseTable]
    This however produced the “locale id” error.

    SQL_Latin1_General_CP1_CI_AS is unicode compatible, so if [ChineseColumn] had data type nvarchar instead of varchar, I believe we would be getting the correct characters. We tried to create the following query table:

    SELECT CAST([ChineseColumn] AS NVARCHAR) FROM [dbo].[ChineseTable] but this also gave us the locale id error.

    As for testing, the error could be replicated by creating a source table with this code:

    CREATE TABLE dbo.ChineseTable
	(
	ChineseField varchar(50) COLLATE Chinese_PRC_CI_AS NULL
	)
INSERT INTO [dbo].[ChineseTable]
SELECT N'你好'

    Then creating a data source connection to this table from an ODX using SQL storage in a SQL DB with collation SQL_Latin1_General_CP1_CI_AS.

    P
    Contributor
    Forum|alt.badge.img+2

    Hi again @Christian Hauggaard 

    We managed to solve it with this query table:

    SELECT CAST([ChineseColumn] AS NVARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS [ChineseColumn] FROM [dbo].[ChineseTable]

     

    Christian Hauggaard
    1 person likes this
    • Christian Hauggaard
      Christian Hauggaard

    Reply


    Most helpful members this week

    Terms and ConditionsCookie settings

    Cookie policy

    We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

     
    Cookie settings