April 22, 2014 at 10:31 pm
how to reslove this error
Msg 468, Level 16, State 9, Line 7
Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
April 22, 2014 at 11:19 pm
shashianireddy 30786 (4/22/2014)
how to reslove this errorMsg 468, Level 16, State 9, Line 7
Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
These two collations are not compatible as such, hence a collation clause is needed when comparing values from the two.
😎
Collation clause example:
DECLARE @CI TABLE
(
CI_ID INT IDENTITY(1,1) NOT NULL
,CI_TXT NVARCHAR(50) COLLATE Latin1_General_CI_AI NOT NULL
);
DECLARE @CS TABLE
(
CS_ID INT IDENTITY(1,1) NOT NULL
,CS_TXT NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
);
INSERT INTO @CI (CI_TXT) VALUES (N'ABC'),(N'DEF'),(N'GHI'),(N'JKL'),(N'MNO');
INSERT INTO @CS (CS_TXT) VALUES (N'ABC'),(N'DEF'),(N'GHI'),(N'JKL'),(N'MNO');
SELECT
*
FROM @CI CI
INNER JOIN @CS CS
ON CI.CI_TXT COLLATE SQL_Latin1_General_CP1_CI_AS = CS.CS_TXT;
April 23, 2014 at 1:00 am
With out seeing your code, I can't give any pointers for where the error may be happening.
The only advice I can give is that it will most likely be generated from the join part of your query or in the where clause when there is a comparison.
An example from a system here is :-
snowdrop..employee_table.EMPLOY_REF = dbo.TodayEmployee_Table.Employ_Ref
gives a similar error to yours
and
snowdrop..employee_table.EMPLOY_REF COLLATE Latin1_General_CI_AS = dbo.TodayEmployee_Table.Employ_Ref
does not.
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
April 23, 2014 at 10:03 am
Note that one of your collations is accent-sensitive and the other isn't. When applying the suggested solutions to your code, make sure you specify the one you need. For example, if you're joining tables on something like a customer code, it's probably not accent-sensitive, but if you're joining on a surname it might be.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply