Geeks With Blogs

News Awarded Microsoft MVP C#.NET - 2007, 2008 and 2009


I am born in Bangladesh and currently live in Melbourne, Australia. I am a Microsoft Certified Application Developer MCAD Chartered Member (C# .Net)and born in Bangladesh.
I am founder and Chief Executive Officer of
Simplexhub, a highly experienced software development company based in Melbourne Australia and Dhaka, Bangladesh. Co-founder and core developer of Pageflakes www.pageflakes.com.
Simplexhub, is on its mission to build a smart virtual community in Bangladesh and recently launched beta realestatebazaar.com.bd an ASP.NET MVC application written in C#.NET.


Some of My Articles
Flexible and Plugin based .Net Application..
Mass Emailing Functionality with C#, .NET 2.0, and Microsoft® SQL Server 2005 Service Broker'
Write your own Code Generator or Template Engine in .NET
Shahed Khan blog

In one of our site where we used a ASP.NET Membership API, while deploying to production we started getting "Cannot resolve collation conflict for equal to operation" on the event of Adding Roles to User and Deleting Roles from User. I ll show how I solved it later but lets look at it from step 1 ....

asp.net was throwing out the following error.

Server Error in '/' Application.

Cannot resolve collation conflict for equal to operation.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Cannot resolve collation conflict for equal to operation.
Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Cannot resolve collation conflict for equal to operation.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857338
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734950
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
   System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +31
   System.Data.SqlClient.SqlDataReader.get_MetaData() +62
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +886
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +122
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) +62
   System.Web.Security.SqlRoleProvider.AddUsersToRolesCore(SqlConnection conn, String usernames, String roleNames) +424
   System.Web.Security.SqlRoleProvider.AddUsersToRoles(String[] usernames, String[] roleNames) +705
   System.Web.Security.Roles.AddUserToRole(String username, String roleName) +341
   RemindRecall.WebApplication.AddUserPage1.CreateUserWizard1_CreatedUser(Object sender, EventArgs e) in C:\Development\RemindRecall\RemindRecall\RemindRecall.WebApplication\AdminPages\AddUserPage1.aspx.cs:52
   System.Web.UI.WebControls.CreateUserWizard.OnCreatedUser(EventArgs e) +105
   System.Web.UI.WebControls.CreateUserWizard.AttemptCreateUser() +341
   System.Web.UI.WebControls.CreateUserWizard.OnNextButtonClick(WizardNavigationEventArgs e) +105
   System.Web.UI.WebControls.Wizard.OnBubbleEvent(Object source, EventArgs e) +453
   System.Web.UI.WebControls.CreateUserWizard.OnBubbleEvent(Object source, EventArgs e) +149
   System.Web.UI.WebControls.WizardChildTable.OnBubbleEvent(Object source, EventArgs args) +17
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
   System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102


Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42

I started looking deeper into the problem then I tried to add roles from the ASP.NET Configuration website and still faced the same issue.

Cannot resolve collation conflict for equal to operation. at System.Web.Administration.WebAdminPage.CallWebAdminHelperMethod(Boolean isMembership, String methodName, Object[] parameters, Type[] paramTypes) at ASP.security_users_adduser_aspx.UpdateRoleMembership(String u, CheckBox box) in c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\ASP.NETWebAdminFiles\Security\Users\addUser.aspx:line 61 at ASP.security_users_adduser_aspx.UpdateRoleMembership(String u) in c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\ASP.NETWebAdminFiles\Security\Users\addUser.aspx:line 52 at ASP.security_users_adduser_aspx.CreatedUser(Object sender, EventArgs e) in c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\ASP.NETWebAdminFiles\Security\Users\addUser.aspx:line 13 at System.Web.UI.WebControls.CreateUserWizard.OnCreatedUser(EventArgs e) at System.Web.UI.WebControls.CreateUserWizard.AttemptCreateUser() at System.Web.UI.WebControls.CreateUserWizard.OnNextButtonClick(WizardNavigationEventArgs e) at System.Web.UI.WebControls.Wizard.OnBubbleEvent(Object source, EventArgs e) at System.Web.UI.WebControls.CreateUserWizard.OnBubbleEvent(Object source, EventArgs e) at System.Web.UI.WebControls.Wizard.WizardChildTable.OnBubbleEvent(Object source, EventArgs args) at System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) at System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)


Then I thought it must me a SQL Server Issue as the MembershipAPI is running fine in the development box and giving errors in the production.

I fired up SQL Profiler to look further deep and figured out on the event of adding a role to the user MembershipAPI calling the following StoredProcedure and failing there.

exec dbo.aspnet_UserInRoles_AddUsersToRoles

I opened up the StoredProc in EnterpriseManager and pressed the Check Syntax, I found its coming up with the same error

"Cannot resolve collation conflict for equal to operation"
The same appeared for the following StoredProc as well
dbo.aspnet_UserInRoles_RemoveUsersToRoles

I started doing more research on the StoredProcs and figured out the following line is the culprit...

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

After doing more googling on the specific problem we figured out there is a Collate Problem in the SQL Server Database. I checked the master database found the
collation name: Latin1_General_CI_AS
where our database collation name was different
collation name: SQL_Latin1_General_CP1_CI_AS

then I explicitly mention Collate similiar to the master database in the statement and changed the line to

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

That solved the problem... Yaaaaaaaaaaaaaaaaay

Summary

I changed 2 Asp.NET Membership API related StoredProc ...

aspnet_UsersInRoles_RemoveUsersFromRoles
aspnet_UsersInRoles_AddUsersToRoles

this is the line which caused the issue for me.

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

I changed and explicitly mentioned the COLLATE similiar to our master database in production server

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

It works fine now....

Hope this helps....

Posted on Monday, May 7, 2007 7:22 PM | Back to top


Comments on this post: ASP.NET MembershipApi Cannot resolve collation conflict for equal to operation

# re: ASP.NET MembershipApi Cannot resolve collation conflict for equal to operation
Requesting Gravatar...
Hi,

Many, many thanks for this - you have solved a major headache for me

Kind Regards,
Left by Matt on Aug 10, 2007 1:26 AM

# re: ASP.NET MembershipApi Cannot resolve collation conflict for equal to operation
Requesting Gravatar...
damet garm haji
Left by ali iran on Oct 21, 2007 12:06 AM

# re: ASP.NET MembershipApi Cannot resolve collation conflict for equal to operation
Requesting Gravatar...
Thanks a million, this worked great for me
Left by sean on Jan 03, 2008 7:33 AM

# re: ASP.NET MembershipApi Cannot resolve collation conflict for equal to operation
Requesting Gravatar...
Thank you. Thank you. Thank you.
Left by Pamela on Feb 04, 2008 1:28 PM

# re: ASP.NET MembershipApi Cannot resolve collation conflict for equal to operation
Requesting Gravatar...
Yep!! Works for me too!
Left by CObus on Feb 10, 2008 9:32 PM

# re: ASP.NET MembershipApi Cannot resolve collation conflict for equal to operation
Requesting Gravatar...
Great, thanks for sharing, works like a charm!
Left by Nicki on Sep 08, 2008 11:25 PM

# re: ASP.NET MembershipApi Cannot resolve collation conflict for equal to operation
Requesting Gravatar...
Thank you so much!
Left by Nils on Jan 08, 2009 12:45 AM

# re: ASP.NET MembershipApi Cannot resolve collation conflict for equal to operation
Requesting Gravatar...
Brilliant...was struggling with this annoying problem and this post solved it in two mintues, thanks!!!
Left by From the UK on Jan 16, 2009 7:22 AM

# re: ASP.NET MembershipApi Cannot resolve collation conflict for equal to operation
Requesting Gravatar...
Thanks alot! Worked like a charm :)
Left by Daniel from Norway on May 20, 2009 1:35 AM

# re: ASP.NET MembershipApi Cannot resolve collation conflict for equal to operation
Requesting Gravatar...
many...many...many ..thanks for you
Left by Baraa on Oct 22, 2009 6:54 AM

Your comment:
 (will show your gravatar)


Copyright © Shahed Khan | Powered by: GeeksWithBlogs.net