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
Position int, OrgQty_modified_manually bit)
This fails:
insert into
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
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
ALTER COLUMN ParentID
VARCHAR(30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE
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')
, ?
, ...