Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Different collation between two servers Expand / Collapse
Author
Message
Posted Monday, April 19, 2010 3:54 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 14, 2014 5:34 PM
Points: 72, Visits: 254
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
Posted Monday, April 19, 2010 4:10 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 4:03 PM
Points: 43, Visits: 138
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
Posted Monday, April 19, 2010 4:23 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 8:01 AM
Points: 1,651, Visits: 5,201
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


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #906398
    Posted Tuesday, April 20, 2010 11:31 AM
    Valued Member

    Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

    Group: General Forum Members
    Last Login: Monday, April 14, 2014 5:34 PM
    Points: 72, Visits: 254
    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
    Posted Tuesday, April 20, 2010 11:39 AM


    SSC-Insane

    SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

    Group: General Forum Members
    Last Login: Yesterday @ 8:41 PM
    Points: 22,491, Visits: 30,185
    Show the code, it will help us help you.



    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
    Posted Tuesday, April 20, 2010 11:48 AM
    Valued Member

    Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

    Group: General Forum Members
    Last Login: Monday, April 14, 2014 5:34 PM
    Points: 72, Visits: 254
    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
    Posted Tuesday, April 20, 2010 3:30 PM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Tuesday, April 01, 2014 9:38 AM
    Points: 6,908, Visits: 12,624
    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
    Posted Wednesday, July 13, 2011 5:27 AM
    Grasshopper

    GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

    Group: General Forum Members
    Last Login: Monday, June 10, 2013 4:00 AM
    Points: 13, Visits: 20
    nice idea
    Post #1140891
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse