EDA Tables Cannot Be Joined with Two Different Collations Against SQL Server: A Solution

May 15, 2019

Overview

External Database Access (EDA) enables you to convert data stored in the Rocket U2 database to a 1NFdatabase, such as Microsoft SQL Server, then access that data using existing UniVerse BASIC programs, RetrieVe, or UniData/UniVerse SQL.

There are a number of reasons to join EDA tables. When combining rows from multiple tables in one query, you need to use the JOIN command. There are several types of joins including inner join, outer join and full other join. The simplest join is called equal join.

By default, the SQL Server database is set to the “SQL_Latin1_General_CP1_CI_AS” collation. The primary key of an EDA file is created by the “SQL_Latin1_General_CP1_CS_AS” collation. So, when you are trying to use an equal join on these two collation fields, you’ll get the following error “’cannot resolve collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “SQL_Latin1_General_CP1_CS_AS” in the equal to operation’ #468’”.

When you map the STATES file using the EDA Schema Manager Tool, the ID key is set to the “COLLATE Latin1_General_CS_AS” collation.

Here is the sample SQL statement.

CREATE SCHEMA STATES

CREATE TABLE STATES.STATES(

            ID VARCHAR(20) COLLATE Latin1_General_CS_AS NOT NULL,

            NAME VARCHAR(32),

            NONCONFORMING_FLAG SMALLINT,

            UNMAPPED_U2FIELD VARCHAR(MAX),

            PRIMARY KEY (ID))

If you convert the CUSTOMER file to SQL Server, the ID key is set to the “COLLATE Latin1_General_CS_AS” collation too. But, the other fields will be set to “SQL_Latin1_General_CP1_CI_AS” collation by default.

Here is the collation setting example for the uv_database in the database properties.

When running a Query with an equal join sql – “SELECT * FROM CUSTOMER.CUSTOMER c, STATES.STATES s WHERE c.STATE = s.ID;”, you’ll get an error.

You can solve this issue. Did you know that SQL Server allows you to change the column collation for the query statement? The next Query example is to alter the STATE column of the CUSTOMER table to “SQL_Latin1_General_CP1_CS_AS” in the Query.

You can alter the STATE of the CUSTOMER.CUSTOMER table to “SQL_Latin1_General_CP1_CS_AS” collation to solve the issue too.

Here is an SQL ALTER command to alter the collation.

ALTER TABLE CUSTOMER.CUSTOMER ALTER COLUMN STATE VARCHAR(4) COLLATE Latin1_General_CS_AS NULL;

After the STATE collation has been changed, the query should work as designed.

Another solution is to alter the default collation to “SQL_Latin1_General_CP1_CS_AS” on a newly created database. All field joins will use the “SQL_Latin1_General_CP1_CS_AS” collation.