InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 10:48:22 GMT20http://www.sqlservercentral.com/Forums/Topic957573-8-1.aspxYou need to have the same collation per column for each union statement.Example[code="sql"]SELECT col1 collate Latin1_General_CS_AS, col2 collate Greek_CI_ASFROM MyTableWHERE conditionUNION ALLSELECT col1 collate Latin1_General_CS_AS, col2 collate Greek_CI_ASFROM MyTable2WHERE condition[/code]will work whereas the following won't[code="sql"]SELECT col1 collate Greek_CI_AS, col2 collate Latin1_General_CS_ASFROM MyTableWHERE conditionUNION ALLSELECT col1 collate Latin1_General_CS_AS, col2 collate Greek_CI_ASFROM MyTable2WHERE condition[/code]Thu, 22 Jul 2010 15:07:28 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic957573-8-1.aspxError : Cannot resolve collation conflict for UNION operation.I getting these error becuase one query i using collate to join two table from different database and after unioni don't have use collat in query . how should i solved these problemSELECT DISTINCT '100' AS CONO, --OH.CUST_ID, OH.CO_NUMBER, dbo.GetClosedCustomerOrder(OH.CO_NUMBER, dbo.GetCustomerOrderType(OH.CO_NUMBER)) ORNO, --CX.M3_CustomerID CUNO, ISNULL(CM.CustomerContactEmail, '') CUNO, dbo.GetCustomerOrderType(OH.CO_NUMBER) ORTP, 'TOR' FACI, 'TOR' WHLO, OH.CUST_PO_NO CUOR, 'NTX' TAXC, --CX.M3_CustomerID PYNO, ISNULL(CM.CustomerContactEmail, '') PYNO, dbo.fn_ConvertDateForM3(OH.ORD_CREATE_DATE) CUDT, 'CHK' PYCD, 'USD' CUCD, ISNULL(SDL.PrintLanguage, '') ADID, CASE SUBSTRING(ISNULL(CM.CustomerContactEmail, ''), 1, 1) WHEN '6' THEN '001' ELSE '' END BLID, dbo.fn_ConvertDateForM3(OH.ORD_CREATE_DATE) ORDT, 'HSC' As DIVIFROM FSDATASQLM39.dbo.SH_ORDHD OH, [FSDB-M39].dbo.FS_Customer CM, [FSDB-M39].dbo.FS_ShipToDeliveryLocation SDL, ( SELECT CO_NUMBER, MIN(LN_STA) Status FROM FSDATASQLM39.dbo.SH_ORDDT Group By CO_NUMBER HAVING Min(LN_STA) > 4) COLWHERE OH.ORD_CREATE_DATE >= Convert(DateTime, '2007-01-01 00:00:00')AND OH.CO_NUMBER = COL.CO_NUMBERAND OH.CUST_ID = CM.CustomerID COLLATE Latin1_General_BINAND OH.SHIP_TO_ID = SDL.ShipToDeliveryLocationID COLLATE Latin1_General_BINAND SDL.CustomerKey = CM.CustomerKey AND OH.CO_NUMBER NOT LIKE 'RA%'AND OH.CO_NUMBER NOT LIKE 'F%GST'AND UPPER(LTRIM(RTRIM(OH.CUST_PO_NO))) NOT LIKE '%LB-DEMAND%'AND COL.Status NOT IN ('9')AND LTRIM(RTRIM(OH.CUST_ID)) COLLATE DATABASE_DEFAULT NOT IN ( Select Distinct LTRIM(RTRIM(CustomerID)) From EXCLUDED_HISTORY ) Group by OH.CO_NUMBER, CM.CustomerContactEmail, OH.CUST_PO_NO, OH.ORD_CREATE_DATE, SDL.PrintLanguage, CM.CustomerID, OH.SHIP_TO_ID, CM.CustomerName, OH.CUST_ID, OH.CO_NUMBERi using collate above query but in below query i don't have use collate UNION-- Open Min Max Orders having Closed LinesSELECT '100' AS CONO, --OH.CustomerID, OH.CONumber, dbo.GetClosedCustomerOrder (OH.CONumber, dbo.GetCustomerOrderType(OH.CONumber)) ORNO, --CX.M3_CustomerID CUNO, ISNULL(CM.CustomerContactEmail, '') CUNO, dbo.GetCustomerOrderType(OH.CONumber) ORTP, 'TOR' FACI, 'TOR' WHLO, OH.CustomerPONumber CUOR, 'NTX' TAXC, --CX.M3_CustomerID PYNO, ISNULL(CM.CustomerContactEmail, '') PYNO, dbo.fn_ConvertDateForM3(OH.COCreatedDate) CUDT, 'CHK' PYCD, CASE OH.COControllingCode WHEN 'F' THEN 'EUR' ELSE 'USD' END CUCD, --ISNull(AD.M3_ShiptoID,'') ADID, CASE ISNULL(SDL.PrintLanguage, '') WHEN '' THEN '001' Else SDL.PrintLanguage End ADID, IsNull(CM.CustomerClass8,'') BLID, dbo.fn_ConvertDateForM3(OH.COCreatedDate) ORDT, 'HSC' As DIVIFROM [FSDB-M39].dbo.FS_COHeader OH, [FSDB-M39].dbo.FS_Customer CM, [FSDB-M39].dbo.FS_ShipToDeliveryLocation SDL, ( SELECT DISTINCT COHeaderKey FROM [FSDB-M39].dbo.FS_COLine WHERE COLineStatus < 5 ) COL, [FSDB-M39].dbo.FS_COLine CLWHERE OH.CustomerID = CM.CustomerID COLLATE Latin1_General_BINAND OH.COHeaderKey = COL.COHeaderKeyAND OH.COHeaderKey = CL.COHeaderKeyAND OH.ShipToDeliveryLocationKey = SDL.ShipToDeliveryLocationKey AND (OH.CONumber LIKE '%M%' OR OH.CONumber LIKE '%Z' OR OH.CONumber LIKE '%H' OR (OH.CONumber LIKE '%S' AND SUBSTRING(OH.CONumber, LEN(OH.CONumber) - 1 , 1) <> 'O' AND SUBSTRING(OH.CONumber, LEN(OH.CONumber) - 1 , 1) <> 'D'))AND CL.COLineStatus IN ('5', '6', '7', '8')AND UPPER(LTRIM(RTRIM(OH.CustomerPONumber))) NOT LIKE '%LB-DEMAND%'AND dbo.GetClosedCustomerOrder (OH.CONumber, dbo.GetCustomerOrderType(OH.CONumber)) = 'F56784M'GROUP BY OH.CONumber, CM.CustomerContactEmail, OH.CustomerPONumber, OH.COCreatedDate, SDL.PrintLanguage, CM.CustomerID, OH.ShipToDeliveryLocationID, CM.CustomerName, OH.CustomerID, CM.CustomerClass8, OH.COControllingCodeThu, 22 Jul 2010 14:28:16 GMTshrestha_rosy