Collation Conflict

Question

Post reply

Collation Conflict

Erin.

SSCertifiable

Points: 5237
More actions
April 8, 2010 at 12:59 pm

#213623

I am joining multiple views from two different servers. The first server has a collation of Latin1_General_BIN, the server I am joining is SQL_Latin1_General_CP1_CI_AS.
At the end of my join I have COLLATE DATABASE_DEFAULT. This solved my collation problem at first, but I need to add a CASE statement. When I add the CASE statement I get the following error message:
Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to collation conflict.
Does someone have a suggestion as to how I can make this work. I can't ALTER the databases because the vendors only support the specified collations.
Here is some sample code to give you an idea.
SELECT
p.EMPLID AS PersonID,
t.TKPR_NO AS RefNo,
CASE
WHEN xv.EMPLID IS NULL THEN SUBSTRING(j1.DEPTID, 3, 2)
ELSE xv.PG1 END AS PractGrp01
FROM
DATABASE1.dbo.PS_PERSONAL_DATA p
INNER JOIN DATABASE1.dbo.PS_TH3_TKPR_DATA t
ON p.EMPLID = t.EMPLID
LEFT JOIN [SERVER2].DATABASE2.dbo.PG_EXCEPTION_VW xv
ON p.EMPLID = xv.EMPLID
COLLATE DATABASE_DEFAULT

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

I'm not sure if it helps but you could try to enforce a specific collation within the CASE statement:

SELECT

CASE

WHEN xv.EMPLID IS NULL THEN SUBSTRING(j1.DEPTID, 3, 2)

ELSE xv.PG1 END COLLATE database_default AS PractGrp01

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Erin. SSCertifiable Points: 5237 More actions · Answer 2

Thank you for the suggestion, but I am getting the same error.

Msg 457, Level 16, State 1

Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.

Paul White SSC Guru Points: 150467 More actions · Answer 3

CASE

WHEN xv.EMPLID IS NULL

THEN SUBSTRING(j1.DEPTID, 3, 2) -- COLLATE DATABASE_DEFAULT

ELSE xv.PG1 COLLATE DATABASE_DEFAULT

END

AS PractGrp01

The collation of each expression that might be returned by the CASE must match.

I presume in the above that j1.DEPTID already has the default database collation.

Uncomment the COLLATE clause if not.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Erin. SSCertifiable Points: 5237 More actions · Answer 4

Erin.

SSCertifiable

Points: 5237

April 9, 2010 at 7:33 am

#1149086

That worked! Thank you very much.

Paul White SSC Guru Points: 150467 More actions · Answer 5

Erin-489205 (4/9/2010)
That worked! Thank you very much.

Of course it did 😀 😀 😀

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi