SQL collation conflict with temp table and procedure params coming from Delphi app

Question

I've been working with MS SQL a few years now, and I've never encountered anything like this on my previous job. But where I work now, I got an error that I'd really like to know the cause from.

I made a stored procedure and called it in my Delphi 5 (yeah I know) app with some parameters. This worked fine on two databases (copies from different times). But now I tried it on another DB (again a copy), but it gave me the following error:

Cannot resolve the collation conflict between "Latin1_General_CI_AS" and 
"SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

I got this by creating a temp table and then trying to insert some data. I'm not even joining. And the funny thing is: when I remove the whole WHERE clause, it works. When I leave it (although it only compares parameters with one table), it fails.

create table #TOP (EDAID int, ParentID char(30), ChildID char(30),
    Position int, OrgQty_modified_manually bit)

This fails:

insert into #TOP
select EDAID, ParentID, ChildID, Position, OrgQty_modified_manually
from EDA_SOBOM
where OrderNr = @OrderNr
    and Position = @Position
    and LN = @LN
    and DL = @DL
    and rtrim(ChildID) = @CurrentPart
    and rtrim(ParentID) = @ParentID

This works:

insert into #TOP
select EDAID, ParentID, ChildID, Position, OrgQty_modified_manually
from EDA_SOBOM

The procedure parameters are declared like this: @PartID char(30), @Position int, @OrderNr char(8), @LN char(2), @DL char(2), @ParentID char(30), @Modified bit output

I found a solution here: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

So I added this right after the CREATE:

ALTER TABLE #TOP
  ALTER COLUMN ParentID
    VARCHAR(30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

ALTER TABLE #TOP
  ALTER COLUMN ChildID
    VARCHAR(30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

And that made the whole thing work again...but I don't get it why the WHERE clause with only parameter comparisons fails...can parameters have collation too?

DB has collation SQL_Latin1_General_CP1_CI_AS. Table EDA_SOBOM also has collation SQL_Latin1_General_CP1_CI_AS on the char columns. I found this by the following query:

SELECT col.name, col.collation_name
FROM sys.columns col
WHERE object_id = OBJECT_ID('EDA_SOBOM')

Is there another place where collation can be set apart from the DB level and column level?

I wonder what's going on...

Answer 1

To resolve the collation conflict add "COLLATE DATABASE_DEFAULT" keywords around “=” operator.

SELECT col.name, col.collation_name
FROM sys.columns col
WHERE object_id COLLATE DATABASE_DEFAULT = OBJECT_ID('EDA_SOBOM') COLLATE DATABASE_DEFAULT

Answer 2

There is a server level collation setting that acts as the default for all system dbs. There is a database level collation, as you said. And columns and expressions can have a collation defined.

Many problems arise when a database has a different collation from the system databases especially tempdb.

Without repeating Peter Wishart's answer, which I agree with, I would just add that when developing a product you should decide what level of collation flexibilty you are going to allow. To avoid problems you have to design code around that choice. If you are not going to require your database objects to be consistent with the server collation, then you have to apply collation modifiers or control the collations used when tables are created in tempdb, or system tables are used or when compares are done. That can be a lot of code in a large product.

There is another collation that is often overlooked in SQLServer. That is the default collation used in any .Net SPs or Functions. That collation is defined based on the SQLServer process's windows user profile. It's often not called a collation in docs, It's part of the windows regional settings. Called LCID in the registry.

So even if your database, sqlserver, table, column collations all match, you can still have mismatches if you do string compares in CLR stored procedure code, unless you write that code to avoid them.

Answer 3

Collation conflicts come up on any operators that compare strings of different collations, i.e. the equals in your select.

TempDb takes the server default collation, whereas your real Dbs may have a different one, causing any temp tables created using DDL to have a collation difference.

You can add "collate database_default" clauses after your equality operators which should fix it. Or you could create your temp table using:

select top 0 EDAID, ParentID, ChildID, Position, OrgQty_modified_manually 
into #top
from EDA_SOBOM

This will force the temp table columns to take the data type (& collation) from your database.