InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 20:08:20 GMT20http://www.sqlservercentral.com/Forums/Topic906390-146-1.aspx:-)nice ideaWed, 13 Jul 2011 05:27:14 GMTrakesh.more@nelito.comhttp://www.sqlservercentral.com/Forums/Topic906390-146-1.aspxSince 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:[code="sql"]and(RTRIM(LTRIM(MI.CLNT_MATT_CODE)) [b]COLLATE SQL_Latin1_General_CP1_CI_AI [/b]NOT IN(SELECT PROJECT_CD FROM INT_AUX_PROJECT AS IAPWHERE (DELETE_IND = 0)))[/code]Tue, 20 Apr 2010 15:30:03 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic906390-146-1.aspxok....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_AMOUNTFROM 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_CDWHERE (_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_IDFROM 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_NMWHERE 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%'))Tue, 20 Apr 2010 11:48:22 GMTkolnhttp://www.sqlservercentral.com/Forums/Topic906390-146-1.aspxShow the code, it will help us help you.Tue, 20 Apr 2010 11:39:13 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic906390-146-1.aspxMister 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.Tue, 20 Apr 2010 11:31:19 GMTkolnhttp://www.sqlservercentral.com/Forums/Topic906390-146-1.aspxYou need to specify the collation you want to useFor example[code="sql"]SELECT Table1.Column1, Table1.Column2FROM LinkedServer.db1.owner.Table1 AS Table1JOIN db2.owner.Table2 AS Table2 ON Table1.Column1 = Table2.Column1 COLLATE SQL_Latin1_General_CP1_CI_AI[/code]Mon, 19 Apr 2010 16:23:46 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic906390-146-1.aspxI 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.Mon, 19 Apr 2010 16:10:32 GMTwillian.funeshttp://www.sqlservercentral.com/Forums/Topic906390-146-1.aspxHi 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'[i]Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation[/i]Please let me know what I can do..thank you.Mon, 19 Apr 2010 15:54:12 GMTkoln