SQL 2005 - NAVISION 4 SP 3 Collation Conflict

Hi there,

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)

Aditional information:

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,

chandra

Does the error occurr when you untick the “Validate Codepage” feature?

Hi,

I have tired both way of ticking and unticking the “Validate Code Page” feature.

Either way the error still occurs.

Thanks and regards,

chandra

Dear All,

In order to resolve this collation conflict I am thinking to change the collation setting of the navision database.

Need your advice and opinions and correct me if i am heading the wrong direction pls.

The SQL Server collation setting is :

Dictionary order, case-insensitive, accent-insensitive, for use with the 850 (Multilingual) character set

which is also known as COLLATE SQL_Latin1_General_CP850_CI_AI

Currently the navision database is:

Windows collation : 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

which is also known as Latin1_General_BIN

To resolve this collation conflict issue:

I change the collation setting of the navison database via the navision client (creation or alter) from windows collation to a sql collation.

The SQL Collation is:

Western-European dictionary sort order, code page 850, case-insensitive, accent-insensitive (44)

which is also known as COLLATE SQL_Latin1_General_CP850_CI_AI

As such, now the SQL Server collation and Navision database collation will be the same collation:

COLLATE SQL_Latin1_General_CP850_CI_AI

which means the database will support case insensitive, accent insensitive and double byte character set ( for chinese characters) and english.

Now the question is, will there be any issue to Navision Database if the collation is a SQL collation and not a windows collation?

And am i right to say that this way the database will support all the case insensitive, accent insensitive and double byte character set ( for chinese characters) and english? And there wont be any collation conflicts as both the SQL Server and the Navision database uses the same sql collations?

In the Navision 4 SP 3 documentation, I have read that the recommendation is to use windows collation and for windows collation it has to be case sensitive.

The criteria is to have a collation which will support case insensitive, accent insensitive, double byte characters set ( for chinese characters) and english without any collation conflicts.

Pls correct me if i am wrong.

I must say I dont have good knowledge in SQL Server and Navision.

Thanks and regards,

chandra

Moved to SQL Forums