Test Data Transfer Tool - Is it possible to tweak an collation conflict

(0) Share

Report

Posted on by Community Member

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

*This post is locked for comments

I have the same question (0)

All responses (10)

Answers (1)

Tommy Skaue Moderator on at

Like (0)

Report

RE: Test Data Transfer Tool - Is it possible to tweak an collation conflict

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

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Test Data Transfer Tool - Is it possible to tweak an collation conflict

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 :(

Was this reply helpful? Yes No
Tommy Skaue Moderator on at

Like (0)

Report

RE: Test Data Transfer Tool - Is it possible to tweak an collation conflict

What collation are you running on the hosting operating system?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Test Data Transfer Tool - Is it possible to tweak an collation conflict

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 :-(

Was this reply helpful? Yes No
Verified answer

Marcel Domingus 55 on at

Like (0)

Report

RE: Test Data Transfer Tool - Is it possible to tweak an collation conflict

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

Was this reply helpful? Yes No
Tommy Skaue Moderator on at

Like (0)

Report

RE: Test Data Transfer Tool - Is it possible to tweak an collation conflict

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.

Was this reply helpful? Yes No
Marcel Domingus 55 on at

Like (0)

Report

RE: Test Data Transfer Tool - Is it possible to tweak an collation conflict

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.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Test Data Transfer Tool - Is it possible to tweak an collation conflict

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

Was this reply helpful? Yes No
Marcel Domingus 55 on at

Like (0)

Report

RE: Test Data Transfer Tool - Is it possible to tweak an collation conflict

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

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Test Data Transfer Tool - Is it possible to tweak an collation conflict

Hi Marcel

Thanks for the great help :-)

Best Regards

/Graves

Was this reply helpful? Yes No