Collation error

SQL Server Administration
16

You are welcome, Sameer.

There is one minor detail from my previous comment that I need to clarify. Upon further investigation, it appears that the Latin1_General_CI_AI collation of the syscolumns.collation field (what is causing the error) is a bug that was introduced in SQL Server 2012. There have been two changes to syscolumns between SQL Server 2000 and 2012, though the first change didn't break your 3rd party app. Here is the basic progression of syscolumns between those versions:

  • As of SQL Server 2000 (maybe prior): systypes and syscolumns are system tables in each DB. No issue intermixing the collation columns from both tables as they have the same collation (which is the database's default collation as this is database-level meta-data).

  • Starting in SQL Server 2005: systypes and syscolumns become system compatibility views in each DB. The collation column in systypes comes from an undocumented built-in function. The query for the syscolumns view uses a UNION ALL, and both parts get their collation column from the same undocumented built-in function used in systypes. No issue intermixing the collation columns from both tables as they again have the same collation (they take on the database's default collation). Even with a different source for the value, the behavior ends up being the same as before. (Please note, though, that as of SQL Server 2005, dbo.systypes and dbo.syscolumns are deprecated and now sys.types and sys.columns should be used instead.)

  • Starting in SQL Server 2012: the definition of syscolumns changes slightly, and the second part of the UNION ALL query now selects from an internal system view. This changes the collation of the collation field. It now appears to be using the collation of the [mssqlsystemresource] hidden database (which is: Latin1_General_CI_AI).

    I would say that this is a bug because the behavior changed and is now inconsistent. HOWEVER, given that these are compatibility views, I would also think that fixing this is a very low priority for Microsoft, so don't expect it to happen. Still, I will file it as a bug as we shall see.

The only two possibilities for fixing this error are:

  1. Update the query, though if it's compiled into a 3rd party app, then you probably can't. And even if you could, that query still has other major issues.
  2. Change the database's default collation to Latin1_General_CI_AI. This should fix the error, but that query is still quite bad and mishandles several datatypes.

Take care, Solomon..

P.S. When requesting help in the future, please specify the version(s) of SQL Server that you are using. That extra info helps reduce the time it takes to debug things like this. Especially if you were using one version where something worked, but then upgraded to a newer version and that same thing stopped working.