SQL collation conflicts with temp parameters and procedure parameters coming from a Delphi application

I work with MS SQL several years ago, and I have never seen anything like this in my previous work. But where I work now, I have a mistake about which I really would like to know the reason.

I made a stored procedure and named it in my Delphi 5 application (yes, I know) with some parameters. This worked fine in two databases (copies of different times). But now I tried this on another DB (again a copy), but this 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 temporary table and then try to insert some data. I don’t even join. And the funny thing is: when I delete the whole WHERE clause, it works. When I leave it (although it only compares the 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

It works:

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

Procedure parameters are declared as follows: @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 collision conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in equal operation .

So, I added this right after 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

... , WHERE ... ?

SQL_Latin1_General_CP1_CI_AS. EDA_SOBOM SQL_Latin1_General_CP1_CI_AS char. :

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

, ?

, ...

+3
3

, , .. .

TempDb , Dbs , , DDL, .

"collate database_default" , . , :

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

temp (& collation) .

+1

, dbs . , . .

, , tempdb.

, , , , flexibilty . , . , , , tempdb, . .

, SQLServer. , SP- .Net. SQLServer. ​​, . LCID .

, , sqlserver, table, column collation , , CLR, , .

0

To resolve the sort conflict, add the COLLATE DATABASE_DEFAULT keywords around the "=" operator.

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

Hope this helps ...

0
source

All Articles