Questions and Answers
Q: This article clarifies which collation methods Sage Pro ERP uses with Microsoft SQL Server 2000 and Microsoft SQL Server 2005
A: Collation Methods
| Collation Level | Collation Name | Explanation |
| Server | Any collation is supported | This is required for Sage Pro because it uses both lowercase or uppercase to refer database objects such as table names, function names, and stored procedures. |
| Database | Latin1_General_CI_AS | This is required for Sage Pro because it uses both lowercase or uppercase to refer database objects such as table names, function names, and stored procedures. |
| Column | Latin1_General_BIN | This collation is required for columns with character data types because it needs to sort and compare data based on binary sort. Binary sort order is case-sensitive, that is lower case proceeds uppercase and accent-sensitive. This is how it mimics Microsoft Visual FoxPro behavior while sorting and comparing SQL Server data. |
Technical Notes
- If adding new fields directly to a Sage Pro table in SQL, all character based columns should use the following collation:Latin1_General_BIN
This process is not recommended and fields should be added through the Data Dictionary in Sage Pro which will automatically use the correct collation method.
- If the collation setting must be manually changed on an existing table, use the following SQL statement:ALTER TABLE MyTable ALTER COLUMN MyColumnName varchar(X) COLLATE Latin1_General_BIN
Important
Database edits should only be done by a qualified Microsoft SQL Server Database Administrator. Create a full and verified backup before proceeding. This should first be attempted in a test installation and the results should be fully verified before implementing in a live system. - SQL will generate an error when using two explicit expressions that have been assigned with different collations. This will also occur if an index is created with columns that have been assigned with different collations.
© Copyright Sage Software
