Collation conflict with CS 2.1 (Beta)

What is a collation? Before I get into the exception details, a collation is something that defines what the bit pattern looks like for each character and is used sorting, ordering and comparisons. You can read more details here.

When I had originally upgraded from CS 2.0 to 2.1 Beta 1, I had started to get a collation error in SQL – but only when I clicked on any specific tag to see posts listed by that tag. I had posted this to the CS forums with no luck. Now, I had selected the default SQL collation when I was installing it on one of my dev boxes at home. After profiling SQL Server, the culprit was the stored procedure called “cs_Tags_Get”.

In my example I was selecting the Food tag which resulted in the following SQL and parameters to be executed:

SELECT SectionID from cs_Sections WHERE SectionID IN (3) and IsActive = 1′
@CategoryList='<Categories><Category>Food</Category></Categories>’
@SettingsID=1000

And this resulted in the following exception: “Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in the equal to operation.”

For the sake of completeness, below is the SQL exception you will see on ASP.NET:

[SqlException: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.]
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +742
   System.Data.SqlClient.SqlCommand.ExecuteReader() +41
   CommunityServer.Data.SqlCommonDataProvider.GetTags(String sectionQuerySql, Object tagXml) +273
   CommunityServer.Data.SqlCommonDataProvider.GetTagsBySections(Int32[] sectionIDs, String[] tags) +32
   CommunityServer.Components.Tags.GetTagsBySections(Int32[] sectionIDs, String[] tags) +210
   CommunityServer.Components.Tags.GetTagsBySection(Int32 sectionID, String[] tags) +33
   CommunityServer.Blogs.Controls.TagCloud.get_Tags() +62
   CommunityServer.Controls.BaseTagCloud.DataBind() +25
   CommunityServer.Controls.BaseTagCloud.OnLoad(EventArgs e) +16
   System.Web.UI.Control.LoadRecursive() +35
   System.Web.UI.Control.LoadRecursive() +98
   System.Web.UI.Control.LoadRecursive() +98
   System.Web.UI.Control.AddedControl(Control control, Int32 index) +307
   System.Web.UI.ControlCollection.Add(Control child) +153
   System.Web.UI.Control.AddParsedSubObject(Object obj) +38
   System.Web.UI.Control.System.Web.UI.IParserAccessor.AddParsedSubObject(Object obj) +4
   _ASP.View_TagBrowser_ascx.__BuildControl__control3(Control __ctrl) +94
   System.Web.UI.CompiledTemplateBuilder.InstantiateIn(Control container) +10
   CommunityServer.Controls.LayoutTemplate.AttachChildControls() +97
   CommunityServer.Controls.TemplatedWebControl.CreateChildControls() +108
   System.Web.UI.Control.EnsureChildControls() +100
   System.Web.UI.Control.PreRenderRecursiveInternal() +38
   System.Web.UI.Control.PreRenderRecursiveInternal() +125
   System.Web.UI.Control.PreRenderRecursiveInternal() +125
   System.Web.UI.Control.PreRenderRecursiveInternal() +125
   System.Web.UI.Page.ProcessRequestMain() +1499

So, what is the resolution? Unfortunately other than ensuring your collations are the same there is not much else you can do. Check this KB article on how to change database collation.

Published by

Amit Bahree

This blog is my personal blog and while it does reflect my experiences in my professional life, this is just my thoughts. Most of the entries are technical though sometimes they can vary from the wacky to even political – however that is quite rare. Quite often, I have been asked what’s up with the “gibberish” and the funny title of the blog? Some people even going the extra step to say that, this is a virus that infected their system (ahem) well. [:D] It actually is quite simple, and if you have still not figured out then check out this link – whats in a name?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.