Linked Server

I have a linked as400 and have successfully executed queries against it.

e.g select HDRID,HDRDSC from openquery(SERVER,'select hdrid,hdrdesc from MMTTHOF.table')

I have a problem however when I try to compare a column in my local database with one on the linked server.

The error received is:
"Cannot resolve collation conflict for equal to operation."

I have played around with the Remote Collation and Collation Name settings in SQL Server but to no avail. I then used DTS to bring the table from the AS400 in to the local database and tried running a query against it linking a Char(3) field to its counterpart in the other table. The same error occured. I solved this by changing the collation on the fields in both tables to Latin_General_CI_AS (Although I understood the default dB collation to be SQL_LAtin_General_CP1_CI_AS, so I thought this would have worked).

Anyhow I'm still unable to run the query against the original AS400 table.

Any thoughts would be much appreciated guy's n gals

JBrute force is sometimes appropriate. When using a linked server, it is sometimes simplest to pick an arbitrary collation that works well, the force both of the columns to use that collation!

-PatP