May 28, 2015 at 9:06 am
Hi,
We’ve had two customers report the following issue while updating and installing latest application version.
Cannot resolve the collation conflict between "Latin1_General_CI_AS"
and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS"
and "Latin1_General_CI_AS" in the equal to operation.
May 28, 2015 at 9:36 am
Collation will determine how data is sorted and compared, for example should case sensitivity be taken into account when comparing strings etc.
If in this case each column has a different collation, it's unsure what set of rules to use as they could possibly conflict.
One way to prevent it is to ensure you are using the same collation throughout the database.
May 28, 2015 at 9:41 am
To follow up on this, you might still be a little unsure, since in this case the two collations seem to have the major properties in common (most obviously, they're both case insensitive and accent sensitive.
There's a really thorough write-up on the difference between these at http://www.olcot.co.uk/sql-blogs/revised-difference-between-collation-sql_latin1_general_cp1_ci_as-and-latin1_general_ci_as.
Hopefully this helps!
May 28, 2015 at 9:43 am
Small example
DECLARE @TestA TABLE
(
ID INT IDENTITY(1,1) NOT NULL,
Chuff CHAR(1) COLLATE SQL_Latin1_General_CP1_CI_AS
);
DECLARE @TestB TABLE
(
ID INT IDENTITY(1,1) NOT NULL,
Chuff CHAR(1) COLLATE SQL_Latin1_General_CP1_CS_AS
);
INSERT INTO @TestA (Chuff)
VALUES ('a'),('A'),('á'),('a'),('A'),('B'),('b'),('1'),('á');
INSERT INTO @TestB (Chuff)
VALUES ('a'),('A'),('á'),('a'),('A'),('B'),('b'),('1'),('á');
SELECT*
FROM@TestA AS A
ORDERBY A.Chuff ASC;
SELECT*
FROM@TestB AS B
ORDERBY B.Chuff ASC;
--Self Join
SELECT*
FROM@TestA AS A
INNER
JOIN@TestA AS B
ON A.Chuff = B.Chuff;
--Error
SELECT*
FROM@TestA AS A
INNER
JOIN@TestB AS B
ON A.Chuff = B.Chuff;
--Change collation in the query
SELECT*
FROM@TestA AS A
INNER
JOIN@TestB AS B
ON A.Chuff = B.Chuff COLLATE SQL_Latin1_General_CP1_CI_AS;
SELECT*
FROM@TestA AS A
INNER
JOIN@TestB AS B
ON A.Chuff = B.Chuff COLLATE DATABASE_DEFAULT;
May 28, 2015 at 9:46 am
I'm not sure about this, but the cause might have been that a wrong collation was installed when installing SQL Server or creating the database. If you continue to have these errors, you might need to find the root cause.
May 28, 2015 at 9:48 am
Dohsan (5/28/2015)
Collation will determine how data is sorted and compared, for example should case sensitivity be taken into account when comparing strings etc.If in this case each column has a different collation, it's unsure what set of rules to use as they could possibly conflict.
One way to prevent it is to ensure you are using the same collation throughout the database.
Thank you so much for your reply
I have same database Create scripts for around 30 customers. Question is How can I ensure that same collation is not begin used throughout the database ??
Why would it be happening to particular customers? and for all customers does this means that these customers had have changed the database collation during installation of SQL??
Is there a better way to troubleshoot this error?
May 28, 2015 at 9:50 am
I'm not sure I'd have been so quick to change the collation of the column without understanding the implications of doing so. If it's just one piece of code affected, I think I'd have added a COLLATE clause to the query, something like this:
DECLARE @unitID Int
SET @unitID = (SELECT TOP 1 ID FROM Units)
INSERT INTO UnitLogos (LogoPath, UnitID, PrimaryLogo) (SELECT DISTINCT ProgramLogo COLLATE SQL_Latin1_General_CP1_CI_AS, @unitID, 0 FROM Programs WHERE LTRIM(RTRIM(ProgramLogo)) <> '' AND LTRIM(RTRIM(ProgramLogo)) NOT IN (SELECT LogoPath FROM UnitLogos))
UPDATE Programs SET UnitLogoID = (SELECT ID FROM UnitLogos WHERE LTRIM(RTRIM(LogoPath)) = LTRIM(RTRIM(Programs.ProgramLogo))) WHERE LTRIM(RTRIM(Programs.ProgramLogo)) <> ''
GO
John
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply