Archive for the ‘Collation’ Category

How to Resolve the “Cannot resolve the collation conflict between…in the equal to operation?”

As a Production Support DBA, I have encountered the collation conflict error, “Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CS_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation” in the SQL Servers that we support and usually they are reported to us either by the Report Writers, SQL Server Developers, or via our monitoring system. While the fast and quick fix solution is to add the “COLLATE DATABASE_DEFAULT” keywords to the columns in the JOIN or WHERE clauses, it may be worthwhile to understand the root cause.

SQL Server collation specifies the sort order for character data types. You can specific collations at different levels:

  • Instance
  • Database
  • Column

When you install SQL Server, you specify the default collation for the instance. During database creation, you can specify the collation. If omitted, it will default to the instance collation. When you create a character column, you specify the column collation. If not specified, it will default to the database collation.

It is critical to note that one of the most common mistakes for installation of SQL Server is not obtaining the correct collation from the application support team or application vendor before running the installation. Usually, this is causes real royal pain, as you will have to rebuild the master database to change the collation. It sounds simpler than the hard work and hours required. You will need to backup all databases, script out all logins and permissions, script out all the SQL Server Agent jobs, operators, and alerts, and then rebuild the master database to change the collation. If successful, then restore all databases, create the logins and resolve the orphaned users, create all the SQL Server Agent jobs, operators, and alerts. If you remember to always ask and confirm the server collation, you may be able to save yourself the trouble and at least a few hours or days depending on the sizes of the databases. Be very careful, if you are about to build a new server for Great Plains or Microsoft Dynamics CRM. Make sure you get the correct server collation or else you will get burned. Hopefully, I have helped avoid you the trouble.

We will now create an example with the following steps to generate the collation error and then resolving the error:

  1. Find the server collation.
  2. Create a test database with a different collation and create a table with data.
  3. Create a second test database with the default server collation and create a table with data the same as in step 2 except in lower cases.
  4. Generate the collation error by joining the columns from the two databases. The error is caused by collation differences between the two databases.
  5. Resolve the collation error by adding the COLLATE DATABASE_DEFAULT clause to the JOIN.

Implementation steps:

  1. Find the server collation. You can use the SERVERPROPERTY() function. In my test server, the collation is: SQL_Latin1_General_CP1_CI_AS. I will create a database with Case Sensitive for testing.
  2. SELECT SERVERPROPERTY('collation') as [Server Collation]
    
  3. Create a test database with a different collation and create a table with data.
  4. -- create the test database in collation case sensitive
    CREATE DATABASE CollationCaseSensitive
    COLLATE SQL_Latin1_General_CP1_CS_AS -- specify the non default server collation
    GO
    
    USE CollationCaseSensitive
    GO
    CREATE TABLE CollationCS
    (
    ID int Identity (1,1)
    , FirstName varchar(50)
    , LastName varchar(50)
    )
    GO
    INSERT CollationCS (FirstName, LastName)
    VALUES ('Lady', 'GaGa')
    ,('Kim', 'Kardashian')
    ,('Barack', 'Obama')
    
  5. Create a second test database with the default server collation and create a table with data the same as in step 2.
  6. -- create the test database in the server default collation: case insensitive
    CREATE DATABASE CollationCaseInsensitive
    GO
    USE CollationCaseInsensitive
    GO
    CREATE TABLE CollationCI
    (
    ID int Identity (1,1)
    , FirstName varchar(50)
    , LastName varchar(50)
    )
    GO
    INSERT CollationCI (FirstName, LastName)
    VALUES ('lady', 'gaga')
    ,('kim', 'kardashian')
    ,('barack', 'obama')
    
  7. Generate the collation error by joining the columns from the two databases. The error is caused by collation differences between the two databases.
  8. -- generate the collation error
    SELECT *
    FROM CollationCaseInsensitive.dbo.CollationCI AS CI 
    	JOIN CollationCaseSensitive.dbo.CollationCS AS CS
    	ON CI.FirstName = CS.FirstName
    

    Your will get this error:

    Msg 468, Level 16, State 9, Line 5
    Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CS_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.

  9. Resolve the collation error by adding the COLLATE DATABASE_DEFAULT clause to the JOIN.
  10. -- to resolve the collation error, add the COLLATE DATABASE_DEFAULT 
    -- on the columns in the JOIN
    SELECT *
    FROM CollationCaseInsensitive.dbo.CollationCI AS CI 
    	JOIN CollationCaseSensitive.dbo.CollationCS AS CS
    	ON CI.FirstName COLLATE DATABASE_DEFAULT = CS.FirstName COLLATE DATABASE_DEFAULT 
    

 

REFERENCES:

SQL Server Collation Fundamentals
http://msdn.microsoft.com/en-us/library/aa174903(v=sql.80).aspx

DATABASEPROPERTY
http://msdn.microsoft.com/en-us/library/aa258275(v=sql.80).aspx

SERVERPROPERTY
http://msdn.microsoft.com/en-us/library/ms174396(v=sql.105).aspx

IDENTITY
http://msdn.microsoft.com/en-us/library/aa933196(v=sql.80).aspx

You must determine the SQL Server collation to choose before running the installation

Add the step of “selecting the correct SQL Server collation” to your installation checklist. You do have a checklist right? If not, please consider reading “The Checklist Manifesto: How to Get Things Right” by Dr. Atul Gawande. 2 hours time well spent in understanding the importance of checklists.

Changing the server collation after installation or after the server has been in production will cause sleepless night or nights. Most likely you can’t even head to bed because you will be busy preparing, working on, and verifying the collation change. If you deteremined that you need to change the server collation for an instance that’s in production, you will need to request a scheduled maintenance downtime window of at least 3 hours (the time varies depending on the total size of the databases). Then you will need to read Tim Ford’s posts on “Rebuilding the SQL Server master database.” Yes. All 3 parts.

If you are preparing your “installation checklist” kudos to you in taking the initiative to prepare. You can confirm with the application vendor(s) or the application development team on what is the required server collation. If you are requested to build a new server and the specifications will be the same as one of the existing server, you can run the SERVERPROPERTY(‘Collation’) function to find the server collation for an existing instance.

-- Querying the server collation
SELECT
SERVERPROPERTY('ServerName') AS [InstanceName]
, SERVERPROPERTY('Collation') AS [ServerCollation]

OUTPUT: Screen-shot

REFERENCES:

Using SQL Server Collations
http://msdn.microsoft.com/en-us/library/ms144260(v=sql.105).aspx

Rebuilding the SQL Server master database
http://www.mssqltips.com/sqlservertip/1531/rebuilding-the-sql-server-master-database-part-1-of-3/