Post reply

SQL 2005 collation conflict

  • July 17, 2007 at 2:57 am

    #177448

    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

     

  • July 17, 2007 at 3:13 am

    #719935

    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

  • July 17, 2007 at 3:39 am

    #719939

    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

     

     

  • July 17, 2007 at 3:47 am

    #719944

    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

  • July 17, 2007 at 4:14 am

    #719949

    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 

     

     

  • July 17, 2007 at 5:19 am

    #719965

    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

  • July 18, 2007 at 12:14 am

    #720274

    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

  • July 19, 2007 at 12:03 am

    #720615

    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

     

  • July 19, 2007 at 4:09 am

    #720668

    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

  • July 19, 2007 at 7:52 am

    #720774

    (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

  • July 20, 2007 at 12:09 am

    #721011

    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 

  • July 23, 2007 at 2:38 am

    #721460

    hi Chandra

    Did you test this.

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

  • July 23, 2007 at 7:24 pm

    #721732

    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

     

  • July 25, 2007 at 12:57 am

    #722162

    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

  • July 25, 2007 at 10:59 am

    #722374

    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