Question Status

Suggested Answer
Gyan Chand Kabra asked a question on 9 Feb 2015 8:17 PM

Hi,

Can i use COLLATE keyword in select statement in  Dynamics AX same as in queries of the SQL server ?

if yes then please let me know the syntax of the select statement in DAX.

mainly collate keyword is used for the sorting or comparing of the data from the language other than English.

Thanks in Advance

 

Regards,

Gyan 

Regards,

Gyan 

Note - This forum post is my own opinion and does not necessarily reflect the opinion or view of Microsoft.

Reply
Suggested Answer
Faisal Fareed responded on 9 Feb 2015 9:16 PM

Hi Gyan,

As per my understanding by default SQL Server uses SQL_Latin1_General_CP1_CI_AS which is not case sensitive. In SQL if you do want to use case sensitive search you have to change collation explicitly with column.

AX installs with default collation of SQL server and is also not a case sensitive language (X++). You can change the collation of the databases used by AX using this link blogs.msdn.com/.../howto-change-collation-of-ax-database.aspx and then play around with your search. There should not be any need to use COLLATE keyword with columns once you have changed database collation.

kind regards,

Faisal Fareed | Microsoft Dynamics AX Technical Consultant |  My blog

This post is my own opinion and I hope it may help you or someone at some stage.

Reply
VDY responded on 9 Feb 2015 11:22 PM

Once i had issue where i wanted to compare data between two tables of AX and the collation of the columns which i am comparing were different  at SQL level.

so i fetched data of one of the table to temp table in AX and then i compared the temp table column with other column in while loop for each record. This solved my purpose of comparing the data even though it has different collation.

See if it can help you out.

- VDY

Reply
Suggested Answer
Martin Dráb responded on 10 Feb 2015 1:12 AM

Just look into documentation: Select Statement Syntax. There clearly isn't any COLLATE keyword.

Martin "Goshoom" Dráb | Freelancer | Goshoom.NET Dev Blog

Reply
Suggested Answer
Faisal Fareed responded on 9 Feb 2015 9:16 PM

Hi Gyan,

As per my understanding by default SQL Server uses SQL_Latin1_General_CP1_CI_AS which is not case sensitive. In SQL if you do want to use case sensitive search you have to change collation explicitly with column.

AX installs with default collation of SQL server and is also not a case sensitive language (X++). You can change the collation of the databases used by AX using this link blogs.msdn.com/.../howto-change-collation-of-ax-database.aspx and then play around with your search. There should not be any need to use COLLATE keyword with columns once you have changed database collation.

kind regards,

Faisal Fareed | Microsoft Dynamics AX Technical Consultant |  My blog

This post is my own opinion and I hope it may help you or someone at some stage.

Reply
Suggested Answer
Martin Dráb responded on 10 Feb 2015 1:12 AM

Just look into documentation: Select Statement Syntax. There clearly isn't any COLLATE keyword.

Martin "Goshoom" Dráb | Freelancer | Goshoom.NET Dev Blog

Reply