SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Different collation between two servers


Different collation between two servers

Author
Message
koln
koln
SSC-Enthusiastic
SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)

Group: General Forum Members
Points: 138 Visits: 264
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.
willian.funes
willian.funes
SSC Journeyman
SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)

Group: General Forum Members
Points: 97 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.
mister.magoo
mister.magoo
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4112 Visits: 7865
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);




  • 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

  • koln
    koln
    SSC-Enthusiastic
    SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)

    Group: General Forum Members
    Points: 138 Visits: 264
    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
    Lynn Pettis
    SSC-Dedicated
    SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

    Group: General Forum Members
    Points: 39217 Visits: 38529
    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)
    koln
    koln
    SSC-Enthusiastic
    SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)

    Group: General Forum Members
    Points: 138 Visits: 264
    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
    LutzM
    SSChampion
    SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

    Group: General Forum Members
    Points: 10289 Visits: 13559
    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
    rakesh.more@nelito.com
    rakesh.more@nelito.com
    Grasshopper
    Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

    Group: General Forum Members
    Points: 19 Visits: 20
    :-)nice idea
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search