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
May 28th, 2015 2:06am