SQL 2005 collation conflict

Question

SQL 2005 collation conflict

Viewing 15 posts - 1 through 15 (of 20 total)

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

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 1

This error occcurs when databases with different collation interacts. This can be solved by specifying the collate collation name in the from clause of your query.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

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

Hi,

Thanks for the swift response...

sorry,

How do i do this " specifying the collate collation name in the from clause of your query."?

how i do this query and where should it be apply to pls?

Is there any steps of guide i can use to do this pls?

Thanks and regards,

chandra

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 3

select a.*,b.*

from tablea a,tableb b

where a.col1=b.col1 collate collation name

the collation name should correspond to the collation on the column being used in the left hand side. Let me know if you still have any questions.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

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

The error message i get doesnt state the table where this error is coming from. Is there a way I can identify from which database or table this error is exactly coming from pls?

What the exact error i see is as:

(

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)

)

I wasnt doing any query but was trying to view the Securables features of a login user when this error pop up.

Thanks and regards,

chandra

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 5

Check the syslogins, sys.server_principals views to check for the collation because they store your login info.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

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

Hi,

I yet to check on the infor you had given. Once I have done I will revert back to you of the outcome.

Thanks again for the help rendered.

Thanks and regards,

chandra

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

Hi,

When i checked on the sys.login and etc i didnt see anything on collation. Maybe i am checking the wrong views. But i did a query to extract the collation settings from all the sys databases and the server.

This is the result:

Name	Collation_name	compatibility_level
master	SQL_Latin1_General_CP850_CI_AI	90
tempdb	SQL_Latin1_General_CP850_CI_AI	90
model	SQL_Latin1_General_CP850_CI_AI	90
msdb	SQL_Latin1_General_CP850_CI_AI	90
Hi-P_DEV	Latin1_General_BIN	90

Server Property (collation)

SQL_Latin1_General_CP850_CI_AI

Is there anything wrong here pls? I see the system databases collations are the same as the server collation. Your advice pls..

Thanks and regards,

chandra

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 8

Now drop the logn and recreate it using SSMS and then grant access to the database and try logging into the server using that login

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Gift Peddie SSC Guru Points: 73570 More actions · Answer 9

(Hi-P_DEV

Latin1_General_BIN)

This is your problem BIN(binary sort) it is the fastest sort order but require case sensitive data so it is in conflict with your default collation. You need to change the collation of that database to case insensitive collation to resolve your problem. Hope this helps.

Kind regards,
Gift Peddie

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

Hi Gift,

Thank you for pointing to this. In that case shouldnt it affect the other server with the same collation settings too? But the other servers seems to have no collation issue. I am puzzled with this. I will try your advice and revert back the outcome

Hi Sugesh,

Thank you for the advice. I will try your suggestion and revert back the outcome.

The thing is the server is at our customer site and most likely next week alone i will be able to test all your suggestion.

The database I created is for Navision and the collation setting is something we had used for a couple of customers and never had this issue. Only with this customer, we are encountering the collation issue.

Thanks and regards,

chandra

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 11

hi Chandra

Did you test this.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

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

Hi Sugesh,

I am sorry ,I havent done any test yet due to other works on going for me.

The server is at our customer site. I have to wait for their approval too.

I will revert to you soon.

Thanks alot.

Nice Day.

Thanks and regards,

chandra

Pinal Dave Old Hand Points: 336 More actions · Answer 13

Hi,

Please visit following link, I think this should solve your problem.

http://blog.sqlauthority.com/2007/06/11/sql-server-cannot-resolve-collation-conflict-for-equal-to-operation/

Kindest Regards,

Pinal Dave
sqlauthority.com

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

Dear Dave,

I am sorry to take your time here. I am quite new to SQL 2005.

The issue is I am seeing a collation conflict without doing any query.

Its just pop once a while whenever I view the permissions in the securables which is from the Security- Logins- User properties.

Where I should add the query to as the conflict doesnt comes from a particular table? And the conflict message doesnt state any particular table.

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 double byte character concern.

As there are user who use chinese character.

__________

One expert point to me that its due to the navision database and it should be case and accent sensitive due to BIN. He suggested to change it to case sensitive.

Another expert had point to me to drop and recreate the login.

I have tried the drop and recreate login but still it does gives a collation conflict in union operation.

I can email you the navision collation settings if you want as i have a screen capture of it.

I have the same setting on another server and its running without any conflict issue.

I dont know how I can apply or use the soution to the conflict issue i am seeing.

Pls advice me.

Thanks and regards,

chandra

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

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