Query Error: Collation Conflict (on tables on 2 different servers)

Hi all,
I have a query that I need to run where I join two tables that bothreside on different servers. I use an INNER JOIN statement to attemptto join these tables, but for some reason I am getting the followingerror message...
"Cannot resolve collation conflict for equal to operation."
The query is as follows...
SELECT TABLE_NAME, LEFT(TABLE_NAME, CHARINDEX('_', TABLE_NAME + '_') - 1) AS Abbreviation
FROM mrcsmis.INFORMATION_SCHEMA.TABLES
INNER JOIN DEVELCAD1.Portal.dbo.dnl_db_names_log AS imp ON (imp.dnl_table_name = TABLE_NAME)
WHERE (TABLE_TYPE = 'BASE TABLE')
Thanks
Tryst

SQL JOINs are from Rene Descartes math so you can only create INNER JOIN if the two tables are equal, if they are not equal you use OUTER JOIN. The other problem the error did not show is you need LINKED Server to run your query so your tables maybe equal to qualify for INNER JOIN but SQL Server is not seeing that because the servers are not linked. Try the links below to get started, the Microsoft link have the System stored procs you need to create LINKED Server. Hope this helps.
http://www.databasejournal.com/features/mssql/article.php/3085211

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_4uuq.asp

|||Hi Caddre, thanks for the reply.
I did manage to run the sp_addlinkedserver stored procedure to create a linked server instance.
The problem may be the column types between the two tables.
I have now tried using a LEFT OUTER JOIN, but still get the same error message...
SELECT TABLE_NAME, LEFT(TABLE_NAME, CHARINDEX('_', TABLE_NAME + '_') - 1) AS Abbreviation
FROM mrcsmis.INFORMATION_SCHEMA.TABLES
LEFT OUTER JOIN DEVELCAD1.Portal.dbo.dnl_db_names_log AS imp ON (imp.dnl_table_name = TABLE_NAME)
WHERE (TABLE_TYPE = 'BASE TABLE')
Tryst
|||

It is Column collation related try these links to resolve it. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_7ory.asp

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=8818馠