Different collation between two servers

  • 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.

  • 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.

  • 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
  • [/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]

  • 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.

  • Show the code, it will help us help you.

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

  • 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]

  • :-)nice idea

  • Viewing 8 posts - 1 through 7 (of 7 total)

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