In MS SQL SERVER, the collation can be set in column level. When compared 2 different collation column in the query, this error comes up. Following article gives you details on fixing the collation problems.
http://blog.sqlauthority.com/2007/06/11/sql-server-cannot-resolve-collation-conflict-for-equal-to-operation/
In addition select statement can also be a cause for the above problem
E.g.
SELECT P.ID,P.PASSENGER_NAME +' '+ISNULL(P.LAST_NAME,'') [PASSENGER_NAME],P.BAG_COUNT
FROM am.PASSENGER P
In the above table both PASSENGER_NAME and LAST_NAME columns has datatype of nvarchar(50). When I ran the above query the collation conflict error occured and following way I was able to fix it.
SELECT P.ID,
P.PASSENGER_NAME +' '+ISNULL(P.LAST_NAME,'') COLLATE DATABASE_DEFAULT [PASSENGER_NAME],
P.BAG_COUNT
FROM am.PASSENGER P
cheers
Happy coding
samitha (MCTS)
Hi,
ReplyDeleteI have the following query –
CREATE VIEW MappedObjects
AS
SELECT
EM.EntityID AS ID,
EM.EntityType AS EntityType,
ParentEntityID AS ParentID,
EM.EntityGUID AS GUID, EM.[Name] ,
ES.StoreID AS StoreID
FROM EntityMaster AS EM WITH (NOLOCK) LEFT JOIN EntityStore AS ES WITH (NOLOCK) ON ES.EntityID = EM.EntityID AND ES.EntityType COLLATE DATABASE_DEFAULT = EM.EntityType COLLATE DATABASE_DEFAULT
UNION ALL
SELECT TP.TopicID AS ID, ‘Topic’ AS EntityType,0 AS ParentID, TP.TopicGUID AS GUID, TP.[Name], TS.StoreID AS StoreID
FROM Topic AS TP WITH (NOLOCK) LEFT JOIN StoreMappingView AS TS WITH (NOLOCK)
ON TS.EntityID = TP.TopicID AND TS.EntityType =’Topic’
UNION ALL
SELECT NW.NewsID AS ID,’News’ AS EntityType,0 AS ParentID, NW.NewsGUID AS GUID, NW.Headline AS [Name], NS.StoreID AS StoreID
FROM News AS NW LEFT JOIN StoreMappingView AS NS WITH (NOLOCK)
ON NS.EntityID = NW.NewsID AND NS.EntityType =’News’
UNION ALL
SELECT PR.ProductID AS ID, ‘Product’ AS EntityType,0 AS ParentID, PR.ProductGUID AS GUID, PR.[Name], PS.StoreID AS StoreID
FROM Product AS PR LEFT JOIN StoreMappingView AS PS WITH (NOLOCK)
ON PR.ProductID = PS.EntityID AND PS.EntityType =’Product’
UNION ALL
SELECT CP.CouponID AS ID, ‘Coupon’ AS EntityType,0 AS ParentID, CP.CouponGUID AS GUID, CP.[CouponCode] AS [Name], PS.StoreID AS StoreID
FROM Coupon AS CP LEFT JOIN StoreMappingView AS PS WITH (NOLOCK)
ON CP.CouponID = PS.EntityID AND PS.EntityType =’Coupon’
UNION ALL
SELECT OO.OrderOptionID AS ID, ‘OrderOption’ AS EntityType,0 AS ParentID, OO.OrderOptionGUID AS GUID, OO.[Name], PS.StoreID AS StoreID
FROM OrderOption AS OO LEFT JOIN StoreMappingView AS PS WITH (NOLOCK)
ON OO.OrderOptionID = PS.EntityID AND PS.EntityType = ‘OrderOption’
UNION ALL
SELECT GC.GiftCardID AS ID, ‘GiftCard’ AS EntityType,0 AS ParentID, GC.GiftCardGUID AS GUID, GC.SerialNumber AS [Name], PS.StoreID AS StoreID
FROM GiftCard AS GC LEFT JOIN StoreMappingView AS PS WITH (NOLOCK)
ON GC.GiftCardID = PS.EntityID AND PS.EntityType = ‘GiftCard’
It gives me an error as -
Msg 451, Level 16, State 1, Procedure MappedObjects, Line 4
Cannot resolve collation conflict for column 5 in SELECT statement.
Please advise!!
Hi
ReplyDeleteCant give exact answer from the query itself.
Try executing one query at a step and figure out when the error raises. Try removing some columns and see whether the query works. You have to specify the collation whenever it fails to execute when you put any column in the select list. Hope this will give you some starting point...
Cheers