I am facing a collation conflict issue between sql server and navision database.
The collation conflict pops up once a while viewing the the Securables in the Security- Logins- login name properties of the SQL Server Management Studio.
The exact error message is:
Failed to retrieve data for this request.(microsoft.SqlServer.SmoEnum)
An exception occurred while executing a Transact-SQL statement or batch.
Cannot resolve the collation conflict between "Latin1_General_BIN and “SQL_Latin1_General_CP850_CI-AI” in the UNION operation.(Microsoft SQL Server, Error: 468)
The SQL Server collation is:
Dictionary order, case-insensitive, accent-insensitive, for use with the 850 (Multilingual) character set
Properties of SQL Server shows as : SQL_Latin1_General_CP850_CI_AI
THE DATABASE ( NAVISION)
The navision database collation is set from the Navision Client is Windows collation with the follwing settings:
Collation Description: Afrikaans, Basque, Catalan, Dutch, English, Faeroese, German, Indonesian, Italian, Portuguese
Binary - Tick Yes
Case Sensitive - Tick No
Accent Sensitive - Tick No
Validate Code Page - Tick Yes
On the SQL Server, the database collation is seen as :Latin1_General_BIN
I am using this setting due to the need for double byte character set as there are users who use chinese characters.
Can anyone help me on this pls?
What is the correct collation settings to be used on the SQL Server and on the Navision database with case insensitive, accent insensitve and support both english and chinese characters.
The supprised is same collations settings for the SQL 2005 Server and Navision database had been used on another server and it doesnt pop up any collation issue.
Thanks and regards,