Different collation between two servers

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Different collation between two servers

Different collation between two servers

Rate Topic

Display Mode

Topic Options

Author

Message

koln

Posted Monday, April 19, 2010 3:54 PM

Valued Member

Group: General Forum Members
Last Login: Thursday, February 07, 2013 10:11 AM
Points: 70, Visits: 224

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.

Post #906390

willian.funes

Posted Monday, April 19, 2010 4:10 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 23, 2013 8:19 AM
Points: 36, Visits: 126

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.

Post #906396

mister.magoo

Posted Monday, April 19, 2010 4:23 PM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:46 PM
Points: 1,287, Visits: 3,850

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

Post #906398

koln

Posted Tuesday, April 20, 2010 11:31 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, February 07, 2013 10:11 AM
Points: 70, Visits: 224

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.

Post #907106

Lynn Pettis

Posted Tuesday, April 20, 2010 11:39 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 10:35 PM
Points: 21,588, Visits: 27,383

Show the code, it will help us help you.

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #907116

koln

Posted Tuesday, April 20, 2010 11:48 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, February 07, 2013 10:11 AM
Points: 70, Visits: 224

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%'))

Post #907129

LutzM

Posted Tuesday, April 20, 2010 3:30 PM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 3:17 PM
Points: 6,731, Visits: 12,131

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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function

Post #907296

rakesh.more@nelito.com

Posted Wednesday, July 13, 2011 5:27 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, January 03, 2013 11:13 PM
Points: 13, Visits: 18

nice idea

Post #1140891

« Prev Topic | Next Topic »

Permissions