Microsoft Dynamics GP and SQL Server Collations
I have seen this question posted in multiple shapes and forms, but could well be rounded up as follows: "I installed Microsoft SQL Server with an Arabic_BIN collation. In addition, I installed Microsoft Dynamics GP on my server and a few clients. All my clients can access the system with no problems. However, I have this one user who 'accidentally' switched his/her Windows locale to English (United States) and is getting all sorts of errors when accessing the system or trying to enter transactions or master records."
This is not at all uncommon, but to put an end to the myths sorrounding this issue it is necessary to understand how collations work in both Microsoft Windows and Microsoft SQL Server.
Windows Collations
Windows collations are collations defined for SQL Server to support Windows locales. By specifying a Windows collation for SQL Server, the instance of SQL Server uses the same code pages and sorting and comparison rules as the Microsoft Dynamics GP client that is running on a computer for which you have specified the associated Windows locale. For example, the French Windows collation for SQL Server matches the collation attributes of the French locale for Windows.
There are more Windows locales than there are SQL Server Windows collations. The names of Windows locales are based on a language and territory, for example, French (Canada). However, several languages share common alphabets and rules for sorting and comparing characters. For example, 33 Windows locales, including all the Portuguese and English Windows locales, use the Latin1 code page (1252) and follow a common set of rules for sorting and comparing characters.
The SQL Server Windows collation, based on the Latin1_General code page and sorting rules, supports all 33 of these Windows locales. Also, Windows locales specify attributes that are not covered by SQL Server Windows collations such as currency, date, and time formats. Because countries and regions such as Great Britain and the United States have different currency, date, and time formats, they require different Windows collations. They do not require different SQL Server collations, because they have the same alphabet and rules for sorting and comparing characters.
In SQL Server, Windows collations are combined with a series of suffixes to additionally define sorting and comparison rules based on case, accent, kana, and width sensitivity.
SQL Server Collations
SQL collations are a compatibility option to match the attributes of common combinations of code-page number and sort orders that have been specified in earlier versions of SQL Server. Many of these collations support suffixes for case, accent, kana, and width sensitivity, but not always.
In SQL Server 2005, you should primarily use Windows collations. This is particularly true if you have a mix of Unicode and non-Unicode columns in your database. Windows collations actually apply Unicode-based sorting rules to both Unicode and non-Unicode data. This means that SQL Server internally converts non-Unicode data to Unicode to perform comparison operations. This provides consistency across data types in SQL Server and also provides developers with the ability to sort strings in their applications that use the same rules that SQL Server uses.
SQL collations, on the other hand, apply non-Unicode sorting rules to non-Unicode data, and Unicode sorting rules to Unicode data, by using a corresponding Windows collation for the Unicode data. This difference can cause inconsistent results for comparisons of the same characters. Therefore, if you have a mix of Unicode and non-Unicode columns in your database, they should all be defined by using Windows collations so that the same sorting rules are used across Unicode and non-Unicode data.
You should use SQL collations only to maintain compatibility with existing instances of earlier versions of SQL Server or to maintain compatibility in applications that were developed by using SQL collations in earlier versions of SQL Server.
There can be differences in performance between Windows collations and SQL collations, but that would be topic for another blog. For now, if you need to change your DYNAMICS or company databases collation, make sure to check back in the future where I will discuss a few methods to accomplish this.
Until next post!
MG.-
Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/
This is not at all uncommon, but to put an end to the myths sorrounding this issue it is necessary to understand how collations work in both Microsoft Windows and Microsoft SQL Server.
Windows Collations
Windows collations are collations defined for SQL Server to support Windows locales. By specifying a Windows collation for SQL Server, the instance of SQL Server uses the same code pages and sorting and comparison rules as the Microsoft Dynamics GP client that is running on a computer for which you have specified the associated Windows locale. For example, the French Windows collation for SQL Server matches the collation attributes of the French locale for Windows.
There are more Windows locales than there are SQL Server Windows collations. The names of Windows locales are based on a language and territory, for example, French (Canada). However, several languages share common alphabets and rules for sorting and comparing characters. For example, 33 Windows locales, including all the Portuguese and English Windows locales, use the Latin1 code page (1252) and follow a common set of rules for sorting and comparing characters.
The SQL Server Windows collation, based on the Latin1_General code page and sorting rules, supports all 33 of these Windows locales. Also, Windows locales specify attributes that are not covered by SQL Server Windows collations such as currency, date, and time formats. Because countries and regions such as Great Britain and the United States have different currency, date, and time formats, they require different Windows collations. They do not require different SQL Server collations, because they have the same alphabet and rules for sorting and comparing characters.
In SQL Server, Windows collations are combined with a series of suffixes to additionally define sorting and comparison rules based on case, accent, kana, and width sensitivity.
SQL Server Collations
SQL collations are a compatibility option to match the attributes of common combinations of code-page number and sort orders that have been specified in earlier versions of SQL Server. Many of these collations support suffixes for case, accent, kana, and width sensitivity, but not always.
In SQL Server 2005, you should primarily use Windows collations. This is particularly true if you have a mix of Unicode and non-Unicode columns in your database. Windows collations actually apply Unicode-based sorting rules to both Unicode and non-Unicode data. This means that SQL Server internally converts non-Unicode data to Unicode to perform comparison operations. This provides consistency across data types in SQL Server and also provides developers with the ability to sort strings in their applications that use the same rules that SQL Server uses.
SQL collations, on the other hand, apply non-Unicode sorting rules to non-Unicode data, and Unicode sorting rules to Unicode data, by using a corresponding Windows collation for the Unicode data. This difference can cause inconsistent results for comparisons of the same characters. Therefore, if you have a mix of Unicode and non-Unicode columns in your database, they should all be defined by using Windows collations so that the same sorting rules are used across Unicode and non-Unicode data.
You should use SQL collations only to maintain compatibility with existing instances of earlier versions of SQL Server or to maintain compatibility in applications that were developed by using SQL collations in earlier versions of SQL Server.
There can be differences in performance between Windows collations and SQL collations, but that would be topic for another blog. For now, if you need to change your DYNAMICS or company databases collation, make sure to check back in the future where I will discuss a few methods to accomplish this.
Until next post!
MG.-
Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/
Comments
https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;en-us;926651
David Musgrave [MSFT]
Escalation Engineer - Microsoft Dynamics GP
Microsoft Dynamics Support - Asia Pacific
Microsoft Dynamics (formerly Microsoft Business Solutions)
http://www.microsoft.com/Dynamics
mailto:David.Musgrave@online.microsoft.com
http://blogs.msdn.com/DevelopingForDynamicsGP
Any views contained within are my personal views and not necessarily Microsoft policy.
This posting is provided "AS IS" with no warranties, and confers no rights.
Thanks for posting the KB article number! I have also wanted to post a follow up on performance issues experienced with different collations, however, I am not sure I will be able to get to it anytime soon.
MG.-
Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com