Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

(0) Share

Report

Posted on by Ariston J.

Hi All,

May I know if anyone encounter issue below and hope can share how it fix.

I got below error in Management Reporter 2012 Service.

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation. Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
Component: Microsoft.Dynamics.Integration.Common.TraceLog
User: XXX\Admin
Machine: XXXXXX

SQL_5F00_Latin1_5F00_General_5F00_CP1_5F00_CI_5F00_AS-and-Latin1_5F00_General_5F00_CI_5F00_AS.png

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (1)

Suggested answer

Vilmos Kintera 46,149 on at

Like (0)

Report

It is because some AX tables come with a fixed collation, and not the one that SQL uses by default. Modify your SQL statement and put COLLATE SQL_Latin1_General_CP1_CI_AS for both sides of your equation after the name of the column.

Was this reply helpful? Yes No
Ariston J. on at

Like (0)

Report

Hi Vilmos Kintera,

Thanks for response, but I got this error in Management Reporter not in AX and also there is no customisation done in Management Reporter all are standard.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Ariston,

Have u found the solution for it? I'm facing same issue while running one report, it doesn't happens for all. Some reports still can work, some can't.

Thanks.

Regards,

Ryan.

Was this reply helpful? Yes No
Ariston J. on at

Like (0)

Report

Ryan,

Sorry, but I haven't get the clear solution up to this time. Even though I recreated a new AX Environment with Management Reporter the issue are still exists in Management Reporter Console. I suspected it was something like SQL Databases configuration, but don't know what exactly need to configure.

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

Like (0)

Report

Hi

It seems the only (official) requirement in the MMR documentation is that the collation should be case insensitive. If I understand your case correctly, your AX database uses a different collation than the MMR datamart database. I suspect the MMR datamart database collation is defined when you set it up, and you have no control over what collation is being used. The error you get is most likely because of unicode difference between the collations, meaning it uses two different methods of storing special characters.

I'm curious to see what the solution is here.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi,

For my case, AX and MMR databases are the same collation. Only thing that I reckoned is Database server instance and Databases are different collation. It's hard to say the suspected cause. I did try to set SQL trace and check, but not much help for me.

If you have any ideas, let shed me some light :)

Thanks.

Ryan.

Was this reply helpful? Yes No
Douglas Noel 3,905 on at

Like (0)

Report

Hi,

did you check the collation of the used temp db databases too?

I remember some stuff with different kind of collations in conjunction with management reporter and ax, but can't remember exactly.

Regards

Douglas

Was this reply helpful? Yes No
Verified answer

Douglas Noel 3,905 on at

Like (2)

Report

Hi,

did you get it?

There seems to be an interesting fix within CU16 for MR2012:

377152: Integration fails if collation is different between tempdb and Ax database

regards

Douglas

Was this reply helpful? Yes No
Suggested answer

Ariston J. on at

Like (0)

Report

Hi Douglas,

Yes, you're right after upgrading my MR 2012 to CU16 the issue in collation conflict has been fixed and it working great now.

Thank you for advice.

Was this reply helpful? Yes No