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

Answer 2

Hi there, as the two former posts mention you have to use the collate attribute to every nonumeric column but have a look a the collation of the target db and use this collation (e.g. SQL_Latin_CI_AS). Be aware that a table can have it's own collation even a column can have annother collation, so have a deep look in your definitions.

Peace and good luck Ice