hey when run this query 

  SELECT * FROM TABLE1
  EXCEPT 
  SELECT * FROM TABLE3

showing the below error

Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the EXCEPT operation.

how to resolve this issue

note
here columns collation of table1 is Latin1_General_CI_AS and columns collation of table3 is SQL_Latin1_General_CP1_CI_AS

database collation is Latin1_General_CI_AS

and also tell me if there any solution to solve query level with out changing tablestructure prope

CREATE TABLE  t1 (col CHAR(1) COLLATE SQL_Latin1_General_CP1_CI_AS)

CREATE TABLE  t2 (col CHAR(1) COLLATE Latin1_General_CI_AS)


 SELECT * FROM t1
  EXCEPT 
 SELECT * FROM t2
---Msg 468, Level 16, State 9, Line 1
---Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the EXCEPT operation.

SELECT col COLLATE database_default FROM t1 
  EXCEPT 
 SELECT col FROM t2 

I run the below stored procedure except set operator query
SELECT * 
FROM OPENQUERY(ENVSVRCMMSBI001,
             N'EXEC [CMMS_ENB_REPORTS].[dbo].[USP_ENV_QUARTERLY_STOCK_CHANGES]
                      @site = ''SLV'',
 @YYMM1 = ''1401'',
 @YYMM2 = ''1404'',
                      @comm_group1 = ''%'',
                     @comm_group2 = ''%'',
 @productcode = ''%'', 
 @productfamily = ''%''           ')

--this procedure generating result columns have Latin1_General_CI_AS i stored this procedure result into table and --check columns collation it is Latin1_General_CI_AS

 EXCEPT

--the below query columns collation is SQL_Latin1_General_CP1_CI_AS becasue the columns of tables in this below --query is SQL_Latin1_General_CP1_CI_AS
--and the below query is taken from above stored procedure code

 SELECT *   FROM
(SELECT  '20'+ left((case when right('1401',1)>4 then null else '1401' end),2) YEAR1 , right((case when right('1401',1)>4 then null else '1401' end),1) QUARTER1, 
  '20'+ left((case when right('1404',1)>4 then null else '1404' end),2) YEAR2, 
  right((case when right('1404',1)>4 then null else '1404' end),1) QUARTER2,
  ISNULL(A.PART_NO,B.PART_NO)PART_NO,ISNULL(A.PART_DESCRIPTION,B.PART_DESCRIPTION)PART_DESCRIPTION ,
   ISNULL(A.ACCOUNTING_GROUP,B.ACCOUNTING_GROUP)ACCOUNTING_GROUP,
   ISNULL(A.ACCOUNTING_GROUP_DESC,B.ACCOUNTING_GROUP_DESC) ACCOUNTING_GROUP_DESC,
   ISNULL(A.INVENTORY_UOM,B.INVENTORY_UOM)INVENTORY_UOM,
   ISNULL(A.ASSET_CLASS,B.ASSET_CLASS)ASSET_CLASS, 
   ISNULL(A.PRODUCT_FAMILY,B.PRODUCT_FAMILY) PRODUCT_FAMILY,
   ISNULL(A.PRODUCT_CODE_FAMILY_DESC,B.PRODUCT_CODE_FAMILY_DESC)PRODUCT_CODE_FAMILY_DESC ,
   ISNULL(A.PRODUCT_CODE,B.PRODUCT_CODE) PRODUCT_CODE ,
   ISNULL(A.PRODUCT_CODE_DESC,B.PRODUCT_CODE_DESC) PRODUCT_CODE_DESC,
   ISNULL(A.COMM_GROUP_1,B.COMM_GROUP_1) COMM_GROUP_1, 
   ISNULL(A.COMM_GROUP_1_DESC,B.COMM_GROUP_1_DESC)COMM_GROUP_1_DESC,
   ISNULL(A.COMM_GROUP_2,B.COMM_GROUP_2) COMM_GROUP_2,
   ISNULL(A.COMM_GROUP_2_DESC,B.COMM_GROUP_2_DESC) COMM_GROUP_2_DESC ,
 ISNULL(A.SITE,B.SITE)SITE ,ISNULL(QUANTITY_FIRST,0)QUANTITY_FIRST,
 ISNULL(TOTAL_INVENTORY_VALUE_FIRST,0) TOTAL_INVENTORY_VALUE_FIRST,
 ISNULL(TOTAL_INVENTORY_VALUE_FIRST_$,0)TOTAL_INVENTORY_VALUE_FIRST_$ ,
 ISNULL(B.QUANTITY_SECOND,0)QUANTITY_SECOND,ISNULL(B.TOTAL_INVENTORY_VALUE_SECOND,0)TOTAL_INVENTORY_VALUE_SECOND,
 ISNULL(B.TOTAL_INVENTORY_VALUE_SECOND_$,0)TOTAL_INVENTORY_VALUE_SECOND_$ FROM 
(SELECT   IP.PART_NO, IVP.PART_DESCRIPTION ,IP.ACCOUNTING_GROUP, IVP.ACCOUNTING_GROUP_DESC,IVP.INVENTORY_UOM,
  IP.ASSET_CLASS,   IVP.PRODUCT_FAMILY ,  IVP.PRODUCT_CODE_FAMILY_DESC  ,
   IP.PRODUCT_CODE ,   IVP.PRODUCT_CODE_DESC,    IP.COMM_GROUP_1  COMM_GROUP_1,  IVP.COMM_GROUP_1_DESC,
  IP.COMM_GROUP_2  COMM_GROUP_2,  IVP.COMM_GROUP_2_DESC ,IVP.SITE ,IVP.QUANTITY QUANTITY_FIRST,
  (CAST(IVP.TOTAL_INVENTORY_VALUE AS FLOAT))  AS TOTAL_INVENTORY_VALUE_FIRST,   
   CASE WHEN IVP.SITE IN ('SLV') THEN (ROUND((IVP.TOTAL_INVENTORY_VALUE * (IVP.CURRENCY_RATE)),2))
   
   ELSE (ROUND((IVP.TOTAL_INVENTORY_VALUE * (1.0 / IVP.CURRENCY_RATE)),2))  END AS   TOTAL_INVENTORY_VALUE_FIRST_$ ,
   PERIOD
   FROM [CMMS_DWH_IFSDM2].[DBO].[INVENTORY_VALUE_PART_SUM_EXT] IVP INNER JOIN
[CMMS_DWH_IFSDM2].[DBO].[INVENTORY_PART] IP ON IVP.PART_NO = IP.PART_NO AND IVP.SITE = IP.SITE    
 WHERE IVP.TOTAL_INVENTORY_VALUE > 0  AND IVP.YEAR = '20'+ left('1401',2) AND  
 QUARTER= CASE WHEN right('1401',1)=1 THEN '1'  WHEN right('1401',1)= 2 THEN '2' WHEN right('1401',1) = 3 THEN '3'  WHEN right('1401',1) = 4 THEN '4' END   
   AND IVP.SITE ='SLV'
 and ( ip.COMM_GROUP_1 in (SELECT * FROM dbo.CSVToLIst('%')) or '%' = '%' )
  and ( ip.COMM_GROUP_2 in (SELECT * FROM dbo.CSVToLIst('%')) or '%' = '%' )
  and ( ivp.PRODUCT_FAMILY in (SELECT * FROM dbo.CSVToLIst('%')) or '%' = '%' )
  and ( ip.PRODUCT_CODE in (SELECT * FROM dbo.CSVToLIst('%')) or '%' = '%' )
  --     GROUP BY  IP.ACCOUNTING_GROUP,  IVP.ACCOUNTING_GROUP_DESC,IVP.CURRENCY_RATE,
  --IP.ASSET_CLASS,   IVP.PRODUCT_FAMILY , IVP.PRODUCT_FAMILY,IP.PRODUCT_CODE ,IVP.ACCOUNTING_GROUP_DESC,
  --IP.COMM_GROUP_1  ,   IVP.COMM_GROUP_1_DESC ,IVP.QUANTITY,  IP.COMM_GROUP_2  ,   IVP.COMM_GROUP_2_DESC  
  --,IVP.SITE, IP.PART_NO,  IVP.PRODUCT_CODE_FAMILY_DESC, IVP.PART_DESCRIPTION,IVP.PRODUCT_CODE_DESC ,
  --IVP.PART_DESCRIPTION ,IVP.INVENTORY_UOM 
  
  ) A 
 FULL OUTER JOIN 
  (SELECT    (CAST(IVP.TOTAL_INVENTORY_VALUE AS FLOAT))TOTAL_INVENTORY_VALUE_SECOND, IP.PART_NO,
    IVP.PART_DESCRIPTION , IP.ACCOUNTING_GROUP, IVP.ACCOUNTING_GROUP_DESC,IVP.INVENTORY_UOM,
  IP.ASSET_CLASS,   IVP.PRODUCT_FAMILY ,
  IVP.PRODUCT_CODE_FAMILY_DESC,
   IP.PRODUCT_CODE ,   IVP.PRODUCT_CODE_DESC,  
  IP.COMM_GROUP_1  COMM_GROUP_1,  IVP.COMM_GROUP_1_DESC,IVP.QUANTITY QUANTITY_SECOND,
  IP.COMM_GROUP_2  COMM_GROUP_2, IVP.COMM_GROUP_2_DESC ,IVP.SITE ,
  CASE WHEN IVP.SITE IN ('SLV') THEN  (ROUND((IVP.TOTAL_INVENTORY_VALUE * (IVP.CURRENCY_RATE)),2))
   ELSE (ROUND((IVP.TOTAL_INVENTORY_VALUE * (1.0 / IVP.CURRENCY_RATE)),2))
    END AS   TOTAL_INVENTORY_VALUE_SECOND_$,
   PERIOD
   FROM [CMMS_DWH_IFSDM2].[DBO].[INVENTORY_VALUE_PART_SUM_EXT] IVP INNER JOIN
[CMMS_DWH_IFSDM2].[DBO].[INVENTORY_PART] IP ON IVP.PART_NO = IP.PART_NO AND IVP.SITE = IP.SITE
WHERE IVP.TOTAL_INVENTORY_VALUE > 0  AND IVP.YEAR =  '20'+ left('1404',2) AND 
 QUARTER= CASE WHEN (right('1404',1)= 1) THEN '1'  WHEN right('1404',1)= 2 THEN '2' WHEN  right('1404',1)= 3 THEN '3' WHEN  right('1404',1)= 4 THEN '4' END   
 AND IVP.SITE ='SLV'
 and ( ip.COMM_GROUP_1 in (SELECT * FROM dbo.CSVToLIst('%')) or '%' = '%' )
  and ( ip.COMM_GROUP_2 in (SELECT * FROM dbo.CSVToLIst('%')) or '%' = '%' )
  and ( ivp.PRODUCT_FAMILY in (SELECT * FROM dbo.CSVToLIst('%')) or '%' = '%' )
  and ( ip.PRODUCT_CODE in (SELECT * FROM dbo.CSVToLIst('%')) or '%' = '%' )
  --    GROUP BY  IP.ACCOUNTING_GROUP, IVP.ACCOUNTING_GROUP_DESC,IVP.CURRENCY_RATE,  IP.ASSET_CLASS,   IVP.PRODUCT_FAMILY ,
  --IVP.PRODUCT_CODE_FAMILY_DESC,   IP.PRODUCT_CODE ,   IVP.PRODUCT_CODE_DESC,  
  --IP.COMM_GROUP_1  ,  IVP.COMM_GROUP_1_DESC ,IVP.QUANTITY,  IP.COMM_GROUP_2  ,  IVP.COMM_GROUP_2_DESC  
  --,IVP.SITE, IP.PART_NO,IVP.PART_DESCRIPTION ,IVP.INVENTORY_UOM 
  
  ) B  ON A.PART_NO =B.PART_NO  AND A.SITE =B.SITE  and a.PERIOD=b.PERIOD

  union all

  SELECT  '20'+ left((case when right('1401',1)>4 then null else '1401' end),2) YEAR1 , right((case when right('1401',1)>4 then null else '1401' end),1) QUARTER1, 
  '20'+ left((case when right('1404',1)>4 then null else '1404' end),2) YEAR2, 
  right((case when right('1404',1)>4 then null else '1404' end),1) QUARTER2,
  ISNULL(A.PART_NO,B.PART_NO)PART_NO,ISNULL(A.PART_DESCRIPTION,B.PART_DESCRIPTION)PART_DESCRIPTION ,
   ISNULL(A.ACCOUNTING_GROUP,B.ACCOUNTING_GROUP)ACCOUNTING_GROUP,
   ISNULL(A.ACCOUNTING_GROUP_DESC,B.ACCOUNTING_GROUP_DESC) ACCOUNTING_GROUP_DESC,
   ISNULL(A.INVENTORY_UOM,B.INVENTORY_UOM)INVENTORY_UOM,
   ISNULL(A.ASSET_CLASS,B.ASSET_CLASS)ASSET_CLASS, 
   ISNULL(A.PRODUCT_FAMILY,B.PRODUCT_FAMILY) PRODUCT_FAMILY,
   ISNULL(A.PRODUCT_CODE_FAMILY_DESC,B.PRODUCT_CODE_FAMILY_DESC)PRODUCT_CODE_FAMILY_DESC ,
   ISNULL(A.PRODUCT_CODE,B.PRODUCT_CODE) PRODUCT_CODE ,
   ISNULL(A.PRODUCT_CODE_DESC,B.PRODUCT_CODE_DESC) PRODUCT_CODE_DESC,
   ISNULL(A.COMM_GROUP_1,B.COMM_GROUP_1) COMM_GROUP_1, 
   ISNULL(A.COMM_GROUP_1_DESC,B.COMM_GROUP_1_DESC)COMM_GROUP_1_DESC,
   ISNULL(A.COMM_GROUP_2,B.COMM_GROUP_2) COMM_GROUP_2,
   ISNULL(A.COMM_GROUP_2_DESC,B.COMM_GROUP_2_DESC) COMM_GROUP_2_DESC ,
 ISNULL(A.SITE,B.SITE)SITE ,ISNULL(QUANTITY_FIRST,0)QUANTITY_FIRST,
 ISNULL(TOTAL_INVENTORY_VALUE_FIRST,0) TOTAL_INVENTORY_VALUE_FIRST,
 ISNULL(TOTAL_INVENTORY_VALUE_FIRST_$,0)TOTAL_INVENTORY_VALUE_FIRST_$ ,
 ISNULL(B.QUANTITY_SECOND,0)QUANTITY_SECOND,ISNULL(B.TOTAL_INVENTORY_VALUE_SECOND,0)TOTAL_INVENTORY_VALUE_SECOND,
 ISNULL(B.TOTAL_INVENTORY_VALUE_SECOND_$,0)TOTAL_INVENTORY_VALUE_SECOND_$ FROM 
(SELECT   IP.PART_NO, IVP.PART_DESCRIPTION ,IP.ACCOUNTING_GROUP, IVP.ACCOUNTING_GROUP_DESC,IVP.INVENTORY_UOM,
  IP.ASSET_CLASS,   IVP.PRODUCT_FAMILY ,  IVP.PRODUCT_CODE_FAMILY_DESC  ,
   IP.PRODUCT_CODE ,   IVP.PRODUCT_CODE_DESC,    IP.COMM_GROUP_1  COMM_GROUP_1,  IVP.COMM_GROUP_1_DESC,
  IP.COMM_GROUP_2  COMM_GROUP_2,  IVP.COMM_GROUP_2_DESC ,IVP.SITE ,IVP.QUANTITY QUANTITY_FIRST,
  (CAST(IVP.TOTAL_INVENTORY_VALUE AS FLOAT))  AS TOTAL_INVENTORY_VALUE_FIRST,   
   CASE WHEN IVP.SITE IN ('SLV') THEN (ROUND((IVP.TOTAL_INVENTORY_VALUE * (IVP.CURRENCY_RATE)),2))
   
   ELSE (ROUND((IVP.TOTAL_INVENTORY_VALUE * (1.0 / IVP.CURRENCY_RATE)),2))  END AS   TOTAL_INVENTORY_VALUE_FIRST_$ ,
   PERIOD
   FROM [CMMS_DWH_IFSDM1].[DBO].[INVENTORY_VALUE_PART_SUM_EXT] IVP INNER JOIN
[CMMS_DWH_IFSDM1].[DBO].[INVENTORY_PART] IP ON IVP.PART_NO = IP.PART_NO AND IVP.SITE = IP.SITE    
 WHERE IVP.TOTAL_INVENTORY_VALUE > 0  AND IVP.YEAR = '20'+ left('1401',2) AND  
 QUARTER= CASE WHEN right('1401',1)=1 THEN '1'  WHEN right('1401',1)= 2 THEN '2' WHEN right('1401',1) = 3 THEN '3'  WHEN right('1401',1) = 4 THEN '4' END   
   AND IVP.SITE ='SLV'
 and ( ip.COMM_GROUP_1 in (SELECT * FROM dbo.CSVToLIst('%')) or '%' = '%' )
  and ( ip.COMM_GROUP_2 in (SELECT * FROM dbo.CSVToLIst('%')) or '%' = '%' )
  and ( ivp.PRODUCT_FAMILY in (SELECT * FROM dbo.CSVToLIst('%')) or '%' = '%' )
  and ( ip.PRODUCT_CODE in (SELECT * FROM dbo.CSVToLIst('%')) or '%' = '%' )
  --     GROUP BY  IP.ACCOUNTING_GROUP,  IVP.ACCOUNTING_GROUP_DESC,IVP.CURRENCY_RATE,
  --IP.ASSET_CLASS,   IVP.PRODUCT_FAMILY , IVP.PRODUCT_FAMILY,IP.PRODUCT_CODE ,IVP.ACCOUNTING_GROUP_DESC,
  --IP.COMM_GROUP_1  ,   IVP.COMM_GROUP_1_DESC ,IVP.QUANTITY,  IP.COMM_GROUP_2  ,   IVP.COMM_GROUP_2_DESC  
  --,IVP.SITE, IP.PART_NO,  IVP.PRODUCT_CODE_FAMILY_DESC, IVP.PART_DESCRIPTION,IVP.PRODUCT_CODE_DESC ,
  --IVP.PART_DESCRIPTION ,IVP.INVENTORY_UOM 
  
  ) A 
 FULL OUTER JOIN 
  (SELECT    (CAST(IVP.TOTAL_INVENTORY_VALUE AS FLOAT))TOTAL_INVENTORY_VALUE_SECOND, IP.PART_NO,
    IVP.PART_DESCRIPTION , IP.ACCOUNTING_GROUP, IVP.ACCOUNTING_GROUP_DESC,IVP.INVENTORY_UOM,
  IP.ASSET_CLASS,   IVP.PRODUCT_FAMILY ,
  IVP.PRODUCT_CODE_FAMILY_DESC,
   IP.PRODUCT_CODE ,   IVP.PRODUCT_CODE_DESC,  
  IP.COMM_GROUP_1  COMM_GROUP_1,  IVP.COMM_GROUP_1_DESC,IVP.QUANTITY QUANTITY_SECOND,
  IP.COMM_GROUP_2  COMM_GROUP_2, IVP.COMM_GROUP_2_DESC ,IVP.SITE ,
  CASE WHEN IVP.SITE IN ('SLV') THEN  (ROUND((IVP.TOTAL_INVENTORY_VALUE * (IVP.CURRENCY_RATE)),2))
   ELSE (ROUND((IVP.TOTAL_INVENTORY_VALUE * (1.0 / IVP.CURRENCY_RATE)),2))
    END AS   TOTAL_INVENTORY_VALUE_SECOND_$,
   PERIOD
   FROM [CMMS_DWH_IFSDM1].[DBO].[INVENTORY_VALUE_PART_SUM_EXT] IVP INNER JOIN
[CMMS_DWH_IFSDM1].[DBO].[INVENTORY_PART] IP ON IVP.PART_NO = IP.PART_NO AND IVP.SITE = IP.SITE
WHERE IVP.TOTAL_INVENTORY_VALUE > 0  AND IVP.YEAR =  '20'+ left('1404',2) AND 
 QUARTER= CASE WHEN (right('1404',1)= 1) THEN '1'  WHEN right('1404',1)= 2 THEN '2' WHEN  right('1404',1)= 3 THEN '3' WHEN  right('1404',1)= 4 THEN '4' END   
 AND IVP.SITE ='SLV'
 and ( ip.COMM_GROUP_1 in (SELECT * FROM dbo.CSVToLIst('%')) or '%' = '%' )
  and ( ip.COMM_GROUP_2 in (SELECT * FROM dbo.CSVToLIst('%')) or '%' = '%' )
  and ( ivp.PRODUCT_FAMILY in (SELECT * FROM dbo.CSVToLIst('%')) or '%' = '%' )
  and ( ip.PRODUCT_CODE in (SELECT * FROM dbo.CSVToLIst('%')) or '%' = '%' )
  --    GROUP BY  IP.ACCOUNTING_GROUP, IVP.ACCOUNTING_GROUP_DESC,IVP.CURRENCY_RATE,  IP.ASSET_CLASS,   IVP.PRODUCT_FAMILY ,
  --IVP.PRODUCT_CODE_FAMILY_DESC,   IP.PRODUCT_CODE ,   IVP.PRODUCT_CODE_DESC,  
  --IP.COMM_GROUP_1  ,  IVP.COMM_GROUP_1_DESC ,IVP.QUANTITY,  IP.COMM_GROUP_2  ,  IVP.COMM_GROUP_2_DESC  
  --,IVP.SITE, IP.PART_NO,IVP.PART_DESCRIPTION ,IVP.INVENTORY_UOM 
  
  ) B  ON A.PART_NO =B.PART_NO  AND A.SITE =B.SITE  and a.PERIOD=b.PERIOD) A


-----when ever I run above procedure except query it is showing the below error
Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the EXCEPT operation.

--this procedure generating result columns have Latin1_General_CI_AS i stored this procedure result into table and --check columns collation it is Latin1_General_CI_AS


--the above query columns collation is SQL_Latin1_General_CP1_CI_AS becasue the columns of tables in this below --query is SQL_Latin1_General_CP1_CI_AS
--and surprising issue is the above query is taken from above stored procedure code
--actually we need to convert stored procedure into query and i am checking all result data of storedprocedure and ----query is matching or not


--how to resolve this issue

second thing

i have two tables table1,table2 i want unmatched rows from table1 to table2 based on all columns with out using joins

becasue columns are almost 25. i can't write big query. any simple query to return unmatched rows based on all columns values in a row

Hi,

Uri answer absolutely correct.

You need to set right collation in varchar fields

SELECT Field1  SQL_Latin1_General_CP1_CI_AS,

....

fieldN collate SQL_Latin1_General_CP1_CI_AS

FROM OPENQUERY(ENVSVRCMMSBI001,
             N'EXEC [CMMS_ENB_REPORTS].[dbo].[USP_ENV_QUARTERLY_STOCK_CHANGES]
                      @site = ''SLV'',
 @YYMM1 = ''1401'',
 @YYMM2 = ''1404'',
                      @comm_group1 = ''%'',
                     @comm_group2 = ''%'',
 @productcode = ''%'', 
 @productfamily = ''%''           ')

but here 30 columns are there if suppose 100 columns are there then it is difficult to mention each filed with collation name any shortcut

and 

i have two table table1 and table3 both having 3lack rows

why

select * from table1

except

select * from table3

show o rows

but 

select * from table1

intersect

select * from table3

showing only a few records.

intersect command should show all 3 lack rows of table if except is showing 0 rows 

actually i want to know unmatched rows from both tables.

"but here 30 columns are there if suppose 100 columns are there then it is difficult to mention each filed with collation name any shortcut"

Then you need to sort out the table definitions in the first place. I.e., make sure you don't have mixed collations. When you do have mixed collations, then you need to sort this at the query level, just as suggested. There is a reason why we preach so violently that you be careful with collations when you install SQL Server, create databases and create table - so you avoid this mess.

k fine

i have two table table1 and table3 both having 3lack rows

why

select * from table1

except

select * from table3

show o rows

but 

select * from table1

intersect

select * from table3

showing only a few records.

intersect command should show all 3 lack rows of table if except is showing 0 rows 

actually i want to know unmatched rows from both tables.

As for your collation issue, create a temp table to receive the result from the OPENQUERY thing.

As for the INTERSECT story, INTERSECT implies DISTINCT, so duplicates will be removed, I believe.

exactly erland

intersect implies distinct. thanks alot for ur answer.