Breaking news from around the world
Get the Bing + MSN extension
Now Available in Community - MBAS 2019 Presentation Videos
Catch the most popular sessions on demand and learn how Dynamics 365, Power BI, PowerApps, Microsoft Flow, and Excel are powering major transformations around the globe. | View Gallery
2019 release wave 2 Discover the latest updates to Dynamics 365Release overview guides and videos Release Plan | Early Access Availability
Ace your Dynamics 365 deployment with packaged services delivered by expert consultants. | Explore service offerings
Connect with the ISV success team on the latest roadmap, developer tool for AppSource certification, and ISV community engagements | ISV self-service portal
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance TechTalks | Customer Engagement TechTalks | Talent TechTalks | Upcoming TechTalks
I found that even for a completely new AX installation, the AX model et baseline databases have discordant columns collations even for columns belonging a same table. We followed the recommendation about collations for AX:
All Microsoft Dynamics AX databases must use the same SQL collation. These databases include the business database, model store database, Microsoft SQL Server Reporting Services database, and Microsoft SQL Server Analysis Services database.
One of the collation is the same as the server’s default collation which is Latin1_general_ci_as. The collation property of every AX database is also latin1_general_ci_as. But for AX Baseline and AX Model database, some tables have different columns with different collation. For example with the table dbo.ModelSecurityPolicyTBD, the column ELEMENTNAME have the old SQL collation and all other columns are the same as the database which match also the default server’s collation.
For this other table most of the columns are in the old SQL collation, and only one match database and server collation:
The query below list all tables with columns for which collations are not all the same.
-- Query that finds collation differences into AX databases
dense_rank() over(order by collation_Name) as ordreCollation
, dense_rank() over(partition by collation_Name order by table_schema, table_name) as ordreTable
Where collation_name is not null
and OBJECTPROPERTYEX(object_id(table_schema+'.'+table_name), 'isUserTable')=1
Order by ordreCollation desc
As a DBA I know that having several collation into a database is running into problems. Query needs to have the collate clause if there is column comparisons or join doesn’t match columns for which collations are not the same. The collate clause may prevent also index use, making queries less efficient.
I wonder if these differences are just overlooked or are properly handled by AX??
A sentence need to be rephrased in the previous post:
Query needs to have the collate clause if there is column comparisons or join between columns for which collations are not the same.
Business Applications communities