Monday, August 29, 2011

Starting in EnterpriseOne 9.0 the platform pack delivers pre-built databases that are simply attached and are set to a collation of Latin1_General_CI_AS_WS when created at Oracle. This collation is different than the default SQL Server collation and different than the old (pre-9.0) collations. Because of this, you will usually find a mix of collations on an E1 install, particularly upgrades. The collation for E1 databases will likely be different than the collation for tempdb and master. This can cause problems in an EnterpriseOne system and could result in "Cannot resolve collation conflict" or "Cannot resolve the collation conflict" errors appearing in logs during upgrades or JOIN operations.

You can determine the collation of your system's databases with this code:

If you are on version 9.0 or above you will likely have different collations for your E1 databases and the SQL system databases. It is possible that you may see different collations between E1 databases if they are upgraded databases.

Background

The reason for E1 databases being delivered with odd collations was a width sensitivity issue for Double-Byte languages. The solution was to create the EnterpriseOne databases with a width-sensitive collation - Latin1_General_CI_AS_WS. This solved the width sensitivity problem but has caused a large amount of grief for customers since it does not match SQL Server's default collation of SQL_Latin1_General_CP1_CI_AS on US English Windows servers. (Heck, Latin1_General_CI_AS_WS doesn't match any language's default collation, guaranteeing collation conflicts.) Oracle's recommendation is to install SQL Server with a non-default collation that will match the incoming E1 databases which keep their collation when attached. Installing SQL Server with a collation that matches the incoming E1 databases will cause databases created on that SQL Server (master, tempdb, etc.) to take the SQL Server collation that will then match the E1-delivered collation and eliminate collation conflicts with JOINS.

If one fails to install SQL Server with the non-default collation (which is highly likely) Oracle recommends changing the collation of the existing databases.Changing a database's collation is no simple matter however and should be well thought out and planned. Oracle recommends using R98403E but there are several methods and scripts online that can help.

If you are seeing errors in jde logs indicating a collation conflict you may well have to change collation. If you are having problems with your own query containing a JOIN statement that joins databases with a different collations, you may wish to write the query and specify the collation. Ex: JOIN JDE900.OL900.F9860 on (sys.objects.name = JDE900.OL900.F9860.SIOBNM COLLATE SQL_Latin1_General_CP1_CI_AS).

I find that the collation issue in E1 to be a general pain but apparently it is necessary to deliver databases that handle the width sensitivity issue. I personally think the platform pack code could be modified to determine if the double-byte problem is present before splatting databases onto a server that create collation conflicts. I welcome any feedback on this issue.

3 comments:

There are two issues if one does not follow the Oracle suggestion of changing the SQL Server default collation during SQL Server install:

1- The collation of the delivered E1 databases being different than the SQL Server system databases (master, tempdb, msdb, model).

2- If this is an upgrade, the delivered E1 databases (Central Objects) being different than the SQL Server system databases (master, tempdb, msdb, model) *and* the upgraded E1 databases (Business Data).

So, either way, if one does not change the SQL Server collation during its install there is a chance that you will run into problems. I am not hearing any definite problems from collation conflicts except when attempting to perform a JOIN or UNION between two databases with different collations. This happens during two likely scenarios:

1- A user/developer-created query in an external application or an ad hoc query in a data access tool like SQL Server Management Studio.

2- A query in a report that performs a JOIN or UNION or a report that uses TEMPDB.

Great post.Last time I ran into this issue I quickly exported the users, re-installed SQL Server with an old collation and re-attached the databases and import the users again. This was the fastest way I could find to make sure my master db has the same collation as my JDE databases. This only took me approx an hour.