Different collation between two servers

Question

Different collation between two servers

koln

SSCrazy

Points: 2189
More actions
April 19, 2010 at 3:54 pm

#220624

Hi folks...Im trusting this community will help me with a solution:
I have set up a linked server from Server 1 to Server 2..both are SQL 2005, with same SP3.
I am trying to do a simple select statement that retrieves data from a database from a table on Server 1 and data from another database on Server 2.
But when I do, I receive the following message, due to Server 1 having the SQL_Latin1_General_CP1_CI_AI' collation and Server 2 having 'SQL_Latin1_General_CP1_CI_AS'
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation
Please let me know what I can do..thank you.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Willian.Funes Ten Centuries Points: 1203 More actions · Answer 1

I think, you just need to manage the same Collate in both database (Check it) or specify the collate you want to use in query. If this doesn't work you can find a windows app that change all collate from a database in Internet.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 2

You need to specify the collation you want to use

For example

SELECT Table1.Column1, Table1.Column2

FROM LinkedServer.db1.owner.Table1 AS Table1

JOIN db2.owner.Table2 AS Table2

ON Table1.Column1 = Table2.Column1 COLLATE SQL_Latin1_General_CP1_CI_AI

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

koln SSCrazy Points: 2189 More actions · Answer 3

Mister Magoo...your solution worked for my simple select statement..thank you !

..BUT, when I add a case statement to the select statement, it stops working...any idea why ?

or any other solutions out there ?

thank you for your help, as I really need to nail this down.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Lynn Pettis

SSC Guru

Points: 442467

April 20, 2010 at 11:39 am

#1154822

Show the code, it will help us help you.

koln SSCrazy Points: 2189 More actions · Answer 5

ok....this first one works:

SELECT RTRIM(LTRIM(_MatterInfoVW2_1.lawfirm_code)) AS CLIENT_CD, RTRIM(LTRIM(_MatterInfoVW2_1.LAWFIRM_NAME)) AS CLIENT_NM,

RTRIM(LTRIM(_MatterInfoVW2_1.CLNT_MATT_CODE)) AS PROJECT_CD, RTRIM(LTRIM(_MatterInfoVW2_1.MATTER_NAME)) AS PROJECT_NM,

ISNULL(_MatterInfoVW2_1.TXT1, '') + ISNULL(_MatterInfoVW2_1.TXT2, '') + ISNULL(_MatterInfoVW2_1.TXT3, '') + ISNULL(_MatterInfoVW2_1.TXT4, '')

+ ISNULL(_MatterInfoVW2_1.TXT5, '') + ISNULL(_MatterInfoVW2_1.TXT6, '') AS DESCRIPTION, RTRIM(LTRIM(_MatterInfoVW2_1.OFFC))

AS LOCATION_CD, RTRIM(LTRIM(_MatterInfoVW2_1.CLNT_TYPE_CODE)) AS TYPE_CD, _MatterInfoVW2_1.OPEN_DATE,

_MatterInfoVW2_1.CLOSE_DATE, _MatterInfoVW2_1.ytd_fees AS FEES_AMOUNT

FROM Server2.DB2.dbo._MatterInfoVW2 AS _MatterInfoVW2_1 LEFT OUTER JOIN

INT_AUX_PROJECT ON _MatterInfoVW2_1.MATTER_NAME COLLATE SQL_Latin1_General_CP1_CI_AI = INT_AUX_PROJECT.PROJECT_NM AND

_MatterInfoVW2_1.CLNT_MATT_CODE COLLATE SQL_Latin1_General_CP1_CI_AI= INT_AUX_PROJECT.PROJECT_CD

WHERE (_MatterInfoVW2_1.MATTER_NAME IS NOT NULL) AND (_MatterInfoVW2_1.MATTER_NAME IS NOT NULL) AND

(NOT (RTRIM(LTRIM(_MatterInfoVW2_1.CLNT_MATT_CODE)) = '')) AND (NOT (RTRIM(LTRIM(_MatterInfoVW2_1.CLNT_MATT_CODE)) LIKE '%P%')) AND

(INT_AUX_PROJECT.PROJECT_CD IS NULL)

but this one does not:

select

rtrim(ltrim(MI.LAWFIRM_CODE)) as CLIENT_CD

,rtrim(ltrim(MI.CLNT_MATT_CODE)) as PROJECT_CD

,ROLE_CD = case (PI.PARTY_TYPE_CODE)

WHEN 'A' then 'Adverse'

WHEN 'C' then 'Matter Client'

WHEN 'F' then 'Friendly'

WHEN 'N' then 'Neutral'

WHEN 'P' then 'Potentially Adverse'

WHEN 'R' then 'Related'

WHEN 'U' then 'Unknown'

END

,IAL.LISTING_ID

,IAL.LISTING_SRC_ID

FROM Server2.DB2.dbo._PartyInfoVW3_NewContacts_Companies as PI INNER JOIN

Server2.DB2.dbo._MatterInfoVW2 AS MI ON PI.CLNT_MATT_CODE COLLATE SQL_Latin1_General_CP1_CI_AI = MI.CLNT_MATT_CODE COLLATE SQL_Latin1_General_CP1_CI_AI inner JOIN

INT_AUX_LISTING as IAL ON PI.NAME COLLATE SQL_Latin1_General_CP1_CI_AI = IAL.DISPLAY_NM

WHERE IAL.OWN_DIR_ID<>-3 and (NOT (PI.NAME IS NULL)) and(RTRIM(LTRIM(MI.CLNT_MATT_CODE)) NOT IN

(SELECT PROJECT_CD

FROM INT_AUX_PROJECT AS IAP

WHERE (DELETE_IND = 0))) and PI.name_type = 'o' and (not(rtrim(ltrim(MI.CLNT_MATT_CODE )) like '%P%'))

LutzM SSC Guru Points: 107049 More actions · Answer 6

Since there is no error message posted I'd assume you need to set the collation for the MI alias used to compare against your subquery too:

and(RTRIM(LTRIM(MI.CLNT_MATT_CODE)) COLLATE SQL_Latin1_General_CP1_CI_AI NOT IN

(SELECT PROJECT_CD

FROM INT_AUX_PROJECT AS IAP

WHERE (DELETE_IND = 0)))

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]

rakesh.more@nelito.com Valued Member Points: 53 More actions · Answer 7

rakesh.more@nelito.com

Valued Member

Points: 53

July 13, 2011 at 5:27 am

#1352071

:-)nice idea