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:

1
2
3

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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

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.