"Latin1_General_CI_AI" in the equal to operation

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

Answer 1

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

Answer 2

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)

Answer 3

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