Skip to main content

Non-English Language Environments

There are specific SQL Server requirements when installing Access Analyzer in a non-English Language environment, specifically when the environment uses a non-Latin alphabet.

Requirements​

The following collation requirements need to be met prior to the Access Analyzer installation.

Database & Server Collation Settings​

The collation settings at the database level must match what is set at the server level.

Symptoms

Common errors that occur are:

  • Implicit conversion of VARCHAR value to VARCHAR cannot be performed because the collation of the value is unresolved due to a collation conflict.
    • Could not find stored procedure #SA_ImportObject
    • Cannot drop the procedure #SA_ImportObject, because it does not exist or lack of permission
  • Cannot resolve the collation conflict between SQL_Latin1_General_CP1_CI_AS and French_CI_AS in the equal to operation.

Cause

These errors occur because the Access Analyzer solutions use many temporary functions and procedures which in turn use the collation at the server level. Temporary tables created within a stored procedure use the TEMPDB database’s collation instead of the current user database’s collation. Therefore, there will be issues in analysis due to the mismatch.

Resolution

The following is a work-around which we use to avoid collation errors. However, when making changes at the SQL Server level, use caution as it actually rebuilds all user/system database objects. If there are schema bound objects (i.e. Constraints), the whole operation will fail. Make sure to have all of the information or scripts needed to recreate the Access Analyzer user’s databases and all of the objects in them. Customers should use a localized version of the SQL Server, and this should not be done in production environments.

Change Collation at the Database Level​

Follow the steps to change the collation at the database level.

Step 1 – Access the Database Properties in SQL Server Management Studio.

SQL Server Management Studio Database Properties window

Step 2 – Select Options and set the collation.

Now that the collations match, proceed with Access Analyzer installation.

Change Collation at the SQL Server Level​

Follow the steps to change the collation at the SQL Server level.

SQL Server Configuration Manager

Step 1 – Stop the SQL Server service from the Configuration Manager.

Step 2 – Open CMD console as Administrator, and go to the following path (or the path where the binary files are):

…\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn  

Step 3 – Execute the following command (or whichever collation is needed):

sqlservr.exe  -m -T4022 -T3659 -q "French _CS_AS"

See the Microsoft Collation and Unicode support article for collation matches.

Step 4 – Wait until it finishes and start the SQL Server service again.

Now that the collations match, proceed with Access Analyzer installation.

Case Sensitive Collation​

Access Analyzer does not support case sensitive collation settings. Case insensitive collations are notated by having CI in the collation, for example Latin1_General_CI_AS.

Cause

For example, SYS.INDEXES will be unable to be found if there was an English install of SQL Server but a Turkish collation which is case sensitive. So 'SYS.INDEXES != 'sys.indexes' in the environment.

Resolution

All collation settings must be case insensitive.

Troubleshooting​

The following are possible problems for future consideration.

During comparison or joining of columns, collation conflict error occurs in two cases if collation of one column does not match with collation of another column:

This can be generated by the following script:

CREATE TABLE TestTab
(PrimaryKey int PRIMARY KEY,
CharCol char(10) COLLATE French_CI_AS,
CharCol2 char(10) COLLATE greek_ci_as
)
INSERT INTO TestTab VALUES (1, 'abc', 'abc') 
SELECT * FROM TestTab WHERE CharCol = CharCol2

  • Error Returned – Cannot resolve the collation conflict between Greek_CI_AS and French_CI_AS in the equal to operation.

  • Resolution – If the select statement is changed as below, then it would run successfully.

    SELECT * FROM TestTab WHERE CharCol = CharCol2 COLLATE Albanian_CI_AI

NOTE: Explicit collation (Albanian_CI_AI) is not one of any column, but after that it will complete successfully. The collation of two columns have not been matched, instead the third rule of collation precedence was implemented. See the Microsoft Collation Precedence article for additional information.

Resources​

The following articles may be of assistance: