SQL 2005 collation conflict

Question

SQL 2005 collation conflict

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

chandra-402010 SSC-Addicted Points: 432 More actions · Answer 1

Dear Sugesh,

I tried the "drop login and create it again and try login into the server using that login". The collation conflict still occurs.

Though I didnt see any collation issues on the 2nd server for the same setting, I think like what the other expert Gift Peddie said could be the issue. Gift was saying that it has to be case sensitive and etc.

The thing is when setting up the navision database via the navision client there is an option to tick for case sensitve, accent sensitive, binary, validate code page. As such, i used the windows collation with binary selected, case insensitive and accent insensitive and validate code page selected.

And for the server, the collation choosen was SQL Collation:

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

The reason to choose this collation setting is to have it to be case insensitive, accent insensitive and to support double byte character set (fpr chinese characters) and english.

And this collation conflicts happens intermittent when just viewing or switching between features of the Login Properties (General, Server Roles, User Mapping,Securables,Status) on the SQL Server Management Studio.

I assume that the conflict is between the Navision Database collation and the Server collation. But I am unable to touch the root cause of it like from where this is exactly coming from. Will this conflict cause any serious issues to the database integrity and reliabilty? Will the data get corrupted due to this conflict?

But I am very touch by all you experts for putting in your time in your tight schedules to help me on this issue. Sincerly I really apprecite all your valuable information.

I am also seeing what other collation settings for the Navision database and SQL server would be able to support case insensitive, accent insenstive, double bye character set for chinese characters and english.

Thanks and regards,

chandra

chandra-402010 SSC-Addicted Points: 432 More actions · Answer 2

Dear All,

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

In order to resolve this collation conflict, I am intending to do the following.

To make the navision database collation same as the sql 2005 server's collation:

The current sql server collation settings:

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

which is shown as

COLLATE SQL_Latin1_General_CP850_CI_AI

in the Server collation properties.

Now I alter the navison database collation:

use master

go

ALTER DATABASE Navision

COLLATE SQL_Latin1_General_CP850_CI_AI

go

sp_helpdb Navision

go

Which is also known as:

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

when selecting collation to create database via the navision client.

As such the navision database will be using SQL collation and no more windows collation has stated in my previous posts.

This will resolve the collation conflict and at the same time it will have support case insensitive, accent insensitive, double byte character set ( for chinese characters) and english.

Just now at office i tried and tested a setup of the mention collation settings. I didnt see any collation conflicts poping up. But not sure if the collation will pop up again.

But I am scared to go with it due to my lack of knowledge and experience with SQL Server.

Do you think this should be ok to go with pls?

Pls correct me if I am wrong.

Thanks and regards,

chandra

Rolando Hernandez SSC Enthusiast Points: 142 More actions · Answer 3

too much posts.

but try this in the queries :

use equal operation as follows :

where table.field collate database_default = table2.field2 collate database_default

it may help

Al Eardley Mr or Mrs. 500 Points: 511 More actions · Answer 4

In migrations I've done to SQL 2005 I've had collation issues where the collation of the tempDB is actually causing the problem. This usually manifests itself when using temporary tables.

Could this be the cause?

lduvall SSCarpal Tunnel Points: 4869 More actions · Answer 5

I'm having the exact same problem the original poster had. The collation conflict appears when I'm attempting to view the "Securables" section for a particular Login under the Security folder in SSMS. I'm wondering if this collation problem could be the root of another problem I'm having with Sharepoint. I noticed that the Sharepoint admin dbs that were created by WSS have a different collation (Latin1_General_CI_AS_KS_WS) than my server itself (SQL_Latin1_General_CP1_CI_AS). Both of these are case insensitive, accent sensitive, so I don't see why that would be such a big deal, but this thread has me thinking that it could be. Is the error popping up in SSMS just saying that I can't view the securables because the query it's running to pull back that info isn't able to resolve the collation conflict? Or do I really have a permissions issue because the collations are different? Any help is greatly appreciated. I just did a default install of SQL Server 2005sp2 and accepted the default collation, so I didn't intentionally choose to make them different.