You can translate the question and the replies:

SQL Collation conflict between base views on different servers.

This error message is sent when executing queries against two joined sql tables: QUERY [JDBC ROUTE] [ERROR] Received exception with message 'Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.' In a SQL join, this is resolved by converting the collation in the JOIN...ON expression: on tablea.[joinfield] collate SQL_Latin1_General_CP850_CI_AI = tableb.[joinfield] how do we do this in joined Denodo base views?
user
13-09-2019 11:33:12 -0400

3 Answers

Hi, I am able to resolve the similar issue by creating the view using **Create from query** option under the configured data source. To do this, I followed below steps: * In Virtual DataPort, select the configured data source and click the create from query. * In “Create from query”, use the query with sql server "COLLATE Latin1_General_CI_AS" on the join condition to ensure character set match. Now, I was able to obtain the results as expected when I execute the base view created from query. For more information, you can refer to the [Creating Base Views from SQL Queries](https://community.denodo.com/docs/html/browse/7.0/vdp/administration/creating_data_sources_and_base_views/jdbc_sources/jdbc_sources#creating-base-views-from-sql-queries) section of the Virtual DataPort Administration Guide. Hope this helps!
Denodo Team
16-09-2019 08:43:37 -0400
hi, not really the answer. we have base views written off both servers. we built derived views from both. we joined the views. we crash with the above error message. we don't want to work in sql, storing views/sprocs, and then going into denodo to create custom base queries as you describe. how does denodo modify the collation method as we do in sql? thank you
user
19-09-2019 14:49:27 -0400
Hi, In order to resolve the collation related error, I would follow the below steps: * Create two new data source with the same connection configuration. * Create base view in both the configured data sources. * Build the derived views over the created base views. * Now, I would perform the join operation over both the derived views. If you still need help and if you are a valid Support User, you may open a Support Case at the Denodo Support Site and the Support Team will help you. Hope this helps!
Denodo Team
27-09-2019 08:07:31 -0400
You must sign in to add an answer. If you do not have an account, you can register here