Question Status

Verified
Graves asked a question on 12 Sep 2013 1:24 AM

Hi

I am trying to get Test Data Transfer Tool (TTDT) to Work for me, to transfer data between my Dynamics Ax 2012 R2 installations.

But i get aprox. 40 errors:

resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Danish_Norwegian_CI_AS"

We have chosen to use localised database language, but is it possible to tweak TTDT to ignore the Coallation?

or solve it in some other way?

Best Regards

/Graves

Reply
Verified Answer
Marcel Domingus responded on 16 Sep 2013 7:23 AM

Hi, Graves,

I have run into the same problem today while testing TDTT on AX 2012 R2, in our case a collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_CI_AS". This issue is more likely to occur on R2 as the model database (i.e.  AX2012DB_model) has been separated from the 'actual data' database (i.e. AX2012DB) since R2, creating the possibility that the collation of the model database (or tables / fields therein) will be different from the 'actual data' database.

This is even more likely as the model store creation script uses a hardcoded (!) COLLATE SQL_Latin1_General_CP1_CI_AS for string fields, so any AX 2012 database using a collation different from SQL_Latin1_General_CP1_CI_AS will experience this issue.

I have just sent a mail to Microsoft reporting this issue as it is difficult to change the collation of the model store database without access to the creation script (presumably stored in some assembly).

For now, we have worked around this by intercepting the model store creation statements in a temporary model store database using SQL Profiler , stripping the COLLATE clauses and replaying the revised creation script. This will create a (more or less empty) model store having the same collation as the AX 2012 database in which you can subsequently import your own model store file(s).

Regards,

Marcel Domingus

Reply
Tommy Skaue responded on 12 Sep 2013 7:16 AM

I was under the impression the collation had to be the same in order for the tool to work. Or maybe that was the upgrade tool. Interested to see where this one goes. :-)

Tommy Skaue | Dynamics AX Developer from Norway | http://yetanotherdynamicsaxblog.blogspot.no/ | www.axdata.no

Reply
Graves responded on 12 Sep 2013 9:43 AM

Hi tommy

Hmm the collation is the same in my installation.

Have tested the failed tables and everything runs "Danish_...."

But the Tool is still angry :(

Reply
Tommy Skaue responded on 12 Sep 2013 10:04 AM

What collation are you running on the hosting operating system?

Tommy Skaue | Dynamics AX Developer from Norway | http://yetanotherdynamicsaxblog.blogspot.no/ | www.axdata.no

Reply
Graves responded on 12 Sep 2013 10:53 AM

I am running Localised Danish

found a table showing that this should be right:

Danish (Denmark)     0x0406    0x0406    Danish_Norwegian_CI_AS

Dont know if there is any other plases where i should set the localisation, but think it is stange :-(

Reply
Verified Answer
Marcel Domingus responded on 16 Sep 2013 7:23 AM

Hi, Graves,

I have run into the same problem today while testing TDTT on AX 2012 R2, in our case a collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_CI_AS". This issue is more likely to occur on R2 as the model database (i.e.  AX2012DB_model) has been separated from the 'actual data' database (i.e. AX2012DB) since R2, creating the possibility that the collation of the model database (or tables / fields therein) will be different from the 'actual data' database.

This is even more likely as the model store creation script uses a hardcoded (!) COLLATE SQL_Latin1_General_CP1_CI_AS for string fields, so any AX 2012 database using a collation different from SQL_Latin1_General_CP1_CI_AS will experience this issue.

I have just sent a mail to Microsoft reporting this issue as it is difficult to change the collation of the model store database without access to the creation script (presumably stored in some assembly).

For now, we have worked around this by intercepting the model store creation statements in a temporary model store database using SQL Profiler , stripping the COLLATE clauses and replaying the revised creation script. This will create a (more or less empty) model store having the same collation as the AX 2012 database in which you can subsequently import your own model store file(s).

Regards,

Marcel Domingus

Reply
Tommy Skaue responded on 16 Sep 2013 9:37 AM

Great catch, Marcel!

About the modelstore for R2; what about simply creating a new empty database with "correct" collation. Make sure it has a good startup size (6-9 GB for modelstore and indexes) and it grows ok (ie half a gig each time).

Then use AXUTIL or PowerShell to initiate the modelstore against this new database. Make sure to also run the AOSAccount command to grant the AOS Service account proper access. Then finally run the commands to import the complete modelstore to this new database. Obviously there would be some (re-)naming issues of the databases, since the system expects the model database  to be the name of the business database concatenated with "_model".

Just thinking out loud.

Tommy Skaue | Dynamics AX Developer from Norway | http://yetanotherdynamicsaxblog.blogspot.no/ | www.axdata.no

Reply
Marcel Domingus responded on 16 Sep 2013 11:22 AM

Hi, Tommy,

Actually, this was our plan A, but fails because Powershell command

Initialize-AXModelStore -AOSAccount accountname –Createdb –Database YOURMODELDB -Server aos-host

will create the model store tables in the empty database with hardcoded COLLATE Latin1_General_CP1_CI_AS (conflicting with the collation Latin1_General_CI_AS of the operational AX 2012 database), like below:

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = N'dbo' AND TABLE_NAME = 'Layer')

BEGIN

                CREATE TABLE [dbo].[Layer]

                (

                               [Id] INT NOT NULL CONSTRAINT [PK_Id] PRIMARY KEY CLUSTERED,

                               [Name] NVARCHAR(3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

                )

END

I haven't tried AXUTIL, but suspect this will also result in hardcoded COLLATE clauses in the CREATE TABLE statements.

Plan B, recreating the model store data from scratch via SMSS, Tasks | Generate scripts... from the original model database with suppression of the COLLATE clauses will also fail because a handful of encrypted views and stored procedures cannot be recreated this way.

So we settled on plan C, capturing model database creation in SQL Server Profiler XML (with replay) trace file, editing the trace file and then replay in empty database.

Reply
Graves responded on 17 Sep 2013 3:16 AM

Hi

Thanks for the feedback, and i thougth it would be easy :o

But i will try and recreate the db with the sql profiler and see if i can get the db working :-)

Regards

/Graves

Reply
Marcel Domingus responded on 17 Sep 2013 6:52 AM

Hi, Graves,

one WARNING though: review your replay XML for intermediate switches to a different database. Somehow an intermediate "use [master]" statement was inserted in our initial SQL Profiler trace recording, resulting in a partial reconstruction of model artifacts in the model database AND partially in the master database when we replayed it.

So review the recorded XML for such unwanted side-effects before replaying it.

Regards,

Marcel

Reply
Graves responded on 17 Sep 2013 1:22 PM

Hi Marcel

Thanks for the great help :-)

Best Regards

/Graves

Reply