ASP.Net Membership API Cannot resolve collation conflict for equal to operation

by Damiaan Peeters 31. December 2008 09:36

Error

I was using the ASP.NET SQL Server Setup Wizard (aspnet_regsql.exe) to deploy te database to the production SQL Server 2005.

image

Creation or change of the SQL Server database failed.

Setup failed.

Exception:
An error occurred during the execution of the SQL file 'InstallRoles.sql'. The SQL error number is 468 and the SqlException message is: Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
[cut]

SQL file loaded:
InstallRoles.sql

Commands failed:

[cut] …  the failed SQL code

SQL Exception:
System.Data.SqlClient.SqlException: Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
[cut
]…
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at System.Web.Management.SqlServices.ExecuteFile(String file, String server, String database, String dbFileName, SqlConnection connection, Boolean sessionState, Boolean isInstall, SessionStateType sessionStatetype)

Cause

Apparently this error happend when creating the asp.net membership stored procedures: aspnet_UsersInRoles_AddUsersToRoles or aspnet_UsersInRoles_RemoveUsersFromRoles.

The reason is pretty straithforward.

The aspnet_Roles table uses collation “latin1_General_CI_AS”.

image

While my database has a SQL_Latin1_General_CP1_CI_AS collation by default (just like my SQL server collation).

image

Solution

Use the SQL Profiler to get the Code of the of the Stored procedure which need to be created.  The SQL scripts of the ASP.Net Membership can also be found under: C:\Windows\Microsoft.NET\Framework\v2.0.50727  (InstallRoles.sql)

Change the line(s) in the scripts:

look for this line: DECLARE @tbNames table(Name nvarchar(256) NOT NULL PRIMARY KEY) and add the collation like specified in the “aspnet_Roles.RoleName” column.  Your new line should look like this:

DECLARE @tbNames table(Name nvarchar(256) COLLATE Latin1_General_CI_AS NOT NULL PRIMARY KEY)

You need to do this for the 2 stored procedures.

Who.I.am

Certified Umbraco Master, Part of Umbraco Certified partner comm-it, .Net and Azure developer, seo lover. Magician in my spare time.

Month List