T-SQL- Error: “Cannot resolve the collation conflict between…”

One day you write some code on a new system, or using tables in the database you’ve never used before, and BOOM! A perfectly ordinary query blows up… what is this collation business??!

SQL Server collation determines how character (char, varchar, nchar, nvarchar, etc.) columns are handled for sorting and comparison. On English US (en-us) systems, the default collation (unless changed during set-up) is SQL_Latin1_General_CP1_CI_AS.

Collation can be set at the server, database, and column level. You can see the default collation in the server properties and in the properties of the model database.

MEDITECH data repository databases use the: SQL_Latin1_General_CP1_CS_AS collation.

Why? Because there are dictionaries in MEDITECH with case-sensitive mnemonics. For that functional meaning to carry over into the DR (and into your SQL) that case-sensitive variance needs to be maintained.

This is most noticeable in the BAR transaction tables in the [Type] column. Where [Type] C = Charge, and [Type] c = comment.

Executing a query or stored procedure that crosses databases with different collations may generate an error similar to:

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

This can occur in SQL Server Reporting Services (SSRS) when using a data source that connects to a database using one collation and running a report which uses a database with a different collation. It may also occur when executing a stored procedure using temp tables from a database using a different collation.

TSQL offers the COLLATE clause to resolve this situation. COLLATE is applied to character-based columns to change the way that SQL handles sorting and comparison between columns. COLLATE is applied on individual columns. Following the column name, add…

Syntax:

          COLLATE<collation name>
          COLLATE DATABASE_DEFAULT

The easiest solution is to use COLLATE DATABASE_DEFAULT on all character columns when creating your temp tables. This uses the collation already defined.

However, COLLATE DATABASE_DEFAULT may fail when executing a procedure (or ad-hoc query) from a database other than the one in which it was created. The cause may be seen when comparing columns from a temp table to columns in a permanent table. This is because DATABASE_DEFAULT uses the collation of the database from where the procedure or query is executed.

The more reliable (thus better) solution is to expressly define the collation, which will ensure proper handling of comparison and ordering: 

       CREATE TABLE #VisitIDs
       (
       SourceID CHAR(3) COLLATE SQL_Latin1_General_CP1_CS_AS
       ,PatientID VARCHAR(30) COLLATE SQL_Latin1_General_CP1_CS_AS
       ,[Name] VARCHAR(30) COLLATE SQL_Latin1_General_CP1_CS_AS
       ,Birthdate DATE
       ,Sex VARCHAR(1) COLLATE SQL_Latin1_General_CP1_CS_AS
       ,UnitNumber VARCHAR(30) COLLATE SQL_Latin1_General_CP1_CS_AS
       ,VisitID VARCHAR(30) COLLATE SQL_Latin1_General_CP1_CS_AS
       ,VisitAccountNumber VARCHAR(30) COLLATE SQL_Latin1_General_CP1_CS_AS
       ,FacilityID VARCHAR(20) COLLATE SQL_Latin1_General_CP1_CS_AS
       ,FacilityName VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS
       ,VisitType VARCHAR(3) COLLATE SQL_Latin1_General_CP1_CS_AS
       ,ConfidentialPatient BIT
       ,ConfidentialVisit BIT
       ,VisitDateTime DATETIME
       ,DischargeDateTime DATETIME
       ,DischargeDispositionID VARCHAR(10) COLLATE SQL_Latin1_General_CP1_CS_AS
       ,DischargeDispositionName VARCHAR(30) COLLATE SQL_Latin1_General_CP1_CS_AS
       ,ProviderID VARCHAR(10) COLLATE SQL_Latin1_General_CP1_CS_AS
       ,ServicesAndLocations VARCHAR(400) COLLATE SQL_Latin1_General_CP1_CS_AS
);

Extra Credit

Write a query to summarize the different COLLATE clauses in use on your database server(s).

First find the default collation for your server:

EXECUTE sp_helpsort;

Then query the table and column meta-data to find and summarize all of the different collations in use, starting from a query like this:

USE [zcus]
GO

DECLARE @cDbName VARCHAR(50) ;
SET     @cDbName=(SELECT DB_NAME()) ;
DECLARE @cDbCollation VARCHAR(50) ;
SET     @cDbCollation = CAST(DATABASEPROPERTYEX(@cDbName, 'Collation') AS VARCHAR(50)) ;

SELECT   [SchemaName]       = S.[name]
        ,[TableName]        = T.[name]
        ,[ColumnName]       = C.[name]
        ,[ColumnCollation]  = C.collation_name
        ,[DatabaseCollation]= @cDbCollation

FROM    sys.schemas S
        JOIN sys.tables T ON ( T.schema_id = S.schema_id )
        JOIN sys.columns C ON ( C.object_id = T.object_id )

WHERE   C.collation_name IS NOT NULL
        AND
        C.collation_name <> @cDbCollation

ORDER BY 1,2
;

And if you need help with MEDITECH DR optimization, reporting, extracting, index creation or analysis please feel free to give your iatricSystems Account Executive a call or email info@iatric.com to discuss how we can help support your team!