Executing a staging 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.
The TimeXtender staging area database and one or more of the data source databases are using different collation options.
Check "Force Codepage Conversion" in the data source settings. This will ensure that TimeXtender is able to translate the source data into a format the staging area database can understand.
Note that if you add an additional connection that requires this option to an existing data source, you must select "Force Codepage Conversion" in the options for that new connection. You must take this step even if you've already selected the option in the primary data source's settings. The reason for this is that additional connections do not inherit any settings from the primary data source.
Having this option enabled for the main data source but not for an additional connection is a common cause for this error.
I right click on the Data Source, then click on "Data source Settings F4", but I can not find the option "Force Codepage Conversion" to check.
I am using TX DWA Server 22.214.171.124
I'm getting this error during transfer from the first DWH to the next DWH. Both have the same collation (Latin1_General_CI_AS)
Discovery Hub version is 17.12.8
I apologize for the lateness of this response, but hopefully it can help folks moving forward! The option for forcing code page conversions is in the Edit data source window, not the Data Source Settings window.
Something to keep in mind is that once the database is created, changing the collation in database settings will not actually update the database collation like you might expect.
Please check the database collation settings of your two databases, either by looking at database properties in SSMS, or by running the following query against the two databases:
SELECT CONVERT (varchar, SERVERPROPERTY('collation'));
If the two databases have different collations, you can update them to what they should be in SSMS to make them match, or drop the databases and re-create them through Discovery Hub.
If the two databases really do have the same collation, please submit a ticket and we'll look into this further.
In our case the collations are all the same, except for the TX repositories that don't seem to follow server defaults. We do have a cross-sql-version transfer (from 2016 to 2012)
Error went away after switching on SSIS transfer.
For other people reading this:
Doug's command will show you the server default collation which doesn't have to be the same as the database collation (although you better keep them in line!) To show database collations you can run select name, collation_name from sys.databases.
Thank you for the update, and the clarification on the effects of my script there!
I'm glad this problem seems to be resolved, but of course data transfer should work no matter what method you choose.
I'll follow up internally and see if we can discover what might have caused your issue.