locked
"Latin1_General_CI_AI" in the equal to operation RRS feed

  • Question

  • User2019896008 posted

    Hi,

    I have a SQL database for one of my project.I have publish that in to a .sql file using

    Microsoft SQL server publishing wizard.No problem that was ok and i got a .sql file (3.73MB)

    I have get connected to my online sql server using CTP manager and executed that sql

    file.After sometimes I got these messages

     

    Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 50
    Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation.
    Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 58
    Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation.
    Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 84
    Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation.
    Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 92
    Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation.
    Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_AddUsersToRoles, Line 45
    Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation.
    Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_AddUsersToRoles, Line 52
    Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation.
    Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_AddUsersToRoles, Line 76
    Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation.
    Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_AddUsersToRoles, Line 83
    Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation.
    Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_AddUsersToRoles, Line 90
    Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation.
    Msg 15151, Level 16, State 1, Line 1
    Cannot find the object 'aspnet_UsersInRoles_AddUsersToRoles', because it does not exist or you do not have permission.
    Msg 15151, Level 16, State 1, Line 1
    Cannot find the object 'aspnet_UsersInRoles_RemoveUsersFromRoles', because it does not exist or you do not have permission.

     

    Seems somthing with membership tables.Someone asked me to do this

    ALTER DATABASE your_database_name COLLATE Latin1_General_CI_AI

    I have run and executed it successfully.But still I cant create users (since above user create SP not executed)

    and asked to add COLLATE keyword.I checked that automatically created script there is that COLLATE keyword.

    Any ideas guys

    Thanks  in advanced

    Monday, August 31, 2009 2:35 AM

Answers

  • User-2024178180 posted

    you have to change the collation of column in table. can you please tell me which collation you have in column of your membership table? you can see column collation with following command


    sp_help YourMembershipTableName

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 31, 2009 7:27 AM
  • User13878136 posted

    Changing collation of database does not change collation of unicode columns in already existing tables.

    I really do not reccomend to just change collation of existing columns, or at least make full backup of your database before that.

    Moreover, I can't see reason why did you change collation from SQL_Latin1_General_CP1_CI_AS to Latin1_General_CI_AI.

    If you wanted to get rid of accent sensitive, it was way better to change to SQL_Latin1_General_CP1_CI_AI.

    Changing column collations after that is not risky at all (although I reccommend backup anyway)


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 31, 2009 7:37 AM

All replies

  • User-2024178180 posted

    you have to change the collation of column in table. can you please tell me which collation you have in column of your membership table? you can see column collation with following command


    sp_help YourMembershipTableName

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 31, 2009 7:27 AM
  • User13878136 posted

    Changing collation of database does not change collation of unicode columns in already existing tables.

    I really do not reccomend to just change collation of existing columns, or at least make full backup of your database before that.

    Moreover, I can't see reason why did you change collation from SQL_Latin1_General_CP1_CI_AS to Latin1_General_CI_AI.

    If you wanted to get rid of accent sensitive, it was way better to change to SQL_Latin1_General_CP1_CI_AI.

    Changing column collations after that is not risky at all (although I reccommend backup anyway)


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 31, 2009 7:37 AM
  • User1330877334 posted

    USE master;

    GO

    ALTER DATABASE PRATAP

    COLLATE Latin1_General_CI_AS_KS_WS ;

    GO

     

    --Verify the collation setting.

    SELECT name, collation_name

    FROM sys.databases

    WHERE name = N' PRATAP ';

    GO

    Monday, March 4, 2013 8:54 PM