Monday, August 29, 2011

EnterpriseOne 9.0 SQL Server Collation Issues


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:

--SQL Script begin
SELECT name, collation_name FROM sys.databases
--SQL Script end


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.

To determine your SQL Server collation:

--SQL Script begin
SELECT SERVERPROPERTY('Collation') AS 'Collation'
--SQL Script end



Possible Solutions

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.

Here is a very informative page that includes links to scripts that can help you change database collations if you choose to do so: http://www.sqlserverclub.com/articles/understanding-sql-server-collation-sequences.aspx

Here is an MSDN blog telling you not to: http://blogs.msdn.com/b/qingsongyao/archive/2011/04/04/do-not-alter-database-collation-in-your-server.aspx


Workarounds

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).

Here's an example where I utilize this method: http://jeffstevenson.karamazovgroup.com/2010/07/how-large-are-my-tables.html.


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.


More Information

Here's Oracle's explanation why:  https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=HOWTO&id=1267442.1

Another informative Oracle article that also discusses what happens to upgrade databases' collation:
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=HOWTO&id=1271189.1

To get a list of all SQL Server collations (very useful):

--SQL Script begin
SELECT * FROM fn_helpcollations()
--SQL Script end


Subscribe to Jeff Stevenson's Technology Blog - Get an email when new posts appear

3 comments:

Jeff Stevenson said...

In response to a question:

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.

Johan Teekens said...

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.

Amien said...

HI:
When i installed JD Edwards EnterpriseOne 9.0 (32) Enterprise Server in windows server 2008 R2 64x EE, I don't find any things in the data base (SQL Server 2008 R2 64x). Did I load manually the database if yes HOW?