SQL Server 2000 DTS - Cannot resolve collation conflict for equal to operation

Question

I have a SQL Server 2000 DTS package.

One of the steps of this package has the following SQL:

SELECT *
FROM [Crocus_Limited$OrderRequestDetail]
WHERE (rep_updated > GETDATE() -2) 
AND NOT EXISTS

(SELECT OrderID 
FROM NavisionUpgrade.navision4.dbo.[WEBOrderDetails] rd 
WHERE rd.OrderID =     [Crocus_Limited$OrderRequestDetail].OrderID 
AND rd.NavisionItemNo = [Crocus_Limited$OrderRequestDetail].NavisionItemNo )

It is failing- giving me error: cannot resolve collation conflict for equal to operation.

This DTS basically moves data from one DB to another (located in different geographical locations)

how can i alter the above query to resolve this?

Answer 1

One or both of your join columns has on of the char datatypes (char,nchar,varchar,nvarchar) which is stored in incompatible collations in each database.

You can specify the collation to use in any string comparison. The easiest way to do it is to specify the default collation of the machine on which the query is running (I'm guessing that NavisionItemNo is the problem column):

...AND rd.NavisionItemNo collate database_default = [Crocus_Limited$OrderRequestDetail].NavisionItemNo collate database_default )

EDIT

Is OrderID a varchar column too? If so, try

...WHERE rd.OrderID collate database_default = [Crocus_Limited$OrderRequestDetail].OrderID collate database_default
AND rd.NavisionItemNo collate database_default = [Crocus_Limited$OrderRequestDetail].NavisionItemNo ) collate database_default