SQL 2005 collation conflict
-
Hi I am new to SQL 2005.
I installed a SQL 2005 standard edition on a win 2003 machine and assigned the sql collation as SQL_Latin1_General_CP850_CI_AI.
Later after adding users (logins- windows) to the sql server and database I found this unusal exception error like :
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 have installed the same with the settings on another machine and didnt have any collation issue at all.
The only difference is the machine which cause this error has a domain name with a hyphen-minus ( - ) symbol like Micro-Soft. And the other machine which didnt have any issue was with a domain name without the hyphen-minus ( - ) symbol like Microsoft.
To what i have found the hyphen-minus is a sprecial character or a reserved symbol use by SQL Server.
The collation conflict is intermittent and does appear always. And it appear only when i access to view the Securables under logins name properties.
Is this the possible cause for such a collation error to happen?
Can anyone help me on this please?
Here is the screen capture of the error occured:
I have google to source much info on this but not able to find any information on this.
Thanks and regards,
chandra
-
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 -
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
-
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 -
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
-
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 -
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
-
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
-
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 -
(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 -
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
-
-
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
-
Hi,
Please visit following link, I think this should solve your problem.
Kindest Regards,Pinal Dave
sqlauthority.com -
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
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply