AX 2009 Setup: Collation Order Problem

[chris (toronto)]

Hello,

I just tried to install AX 2009, and have run into a collation problem.

When our AX4 system was initailly installed, the collation order was set to
Latin1_General_CI_AI. (my mistake)

After investigating collation order I now understand why A LOT querys
returned results sorted in an "odd" way.

Upon attempting to install AX2009 the installer has halted after selecting
the DB and raised the following error:

Setup cannot use the database Dynamics on server XXXX because the collation
is incorrect. The collation must be CI Case Insensitive, AS Accent Sensitive,
Not WS Width Sensitive.

So, as you would do, I log into sql server and attempt to change the
collation order to be "Latin1_General_CI_AS"

SQL Server reports the follwoing for a LOT of tables:

The object 'CK__INVENTTAB__RECID__00191323' is dependent on database
collation. The database collation cannot be changed if a schema-bound object
depends on it. Remove the dependencies on the database collation and then
retry the operation.

Then i go have a smoke and worry about me employment status.

Any ideas on how to work throught this error?

Chris

[Angel Saenz-Badillos[MS]]

Chris, solving Collation issues is never fun. The easiest solution here is
going to be to install Ax 2009 on a new Database.

Assuming that is not possible the best solution I know of involves using BCP
to move all the data out from all the tables, Script out the db and all
objects in it (make sure to remove all references to the wrong COLLATION!)
recreating the database with the desired collation, running the scripts to
restore the database objects and using BCP to bring all the data back in.

I was actually glad to see your post in a way, we have beefed up collation
verification in AX 2009 so that people don't end up with "odd" query results.
I am sorry to hear that it is causing problems here.

Angel

[glibs]

I would try to use DTS. I usually use it to copy data from MS SQL 2005 ack
to MS SQL 2000. The idea is the same: create new BD and copy data. And I
agree it is not a pleasure to convert DB.

[chris (toronto)]

Angel / Glibs,

Thanks for yout thoughts on this... looks like it going to be painful.

Looks like there are about 30 objects that are stopping the collation change.

Just so I understand what you are telling me I think I need to do the
following:

1) Backup everything

2) Find the 30 objects and determine the table in question for each.

3) bulk copy all table data out of the table.

4) remove all data from the table.

4) remove the object.

5) re-import the data.

6) change the collation order on the table.

7) re-create the object.

After all of this is done I think I should be able to change the collation
order on the database?

Sound about right?

Thanks again!

Chris

[Gandhinath Swaminathan [MSFT]]

The difference between your old collation and new collation is accent
sensitivity (AI vs AS). This is parimarly used for sorting and not for
searching. So your unique keys/ primary keys should not be affected by your
change. You should be able to change your collation on table/database level
rather than BCP. This should be possible in SQL Server 2005.

However back up your database before you do invasive changes.
--
This posting is provided "AS IS" with no warranties, and confers no rights
"chris (toronto)" <christ...@discussions.microsoft.com> wrote in message
news:832EBAAF-0D3B-4D1E...@microsoft.com...