>This bugged me in SQL2000 and seems to still be here in SQL2005. Here's repro
>code to run on two SQL2005 servers:
>
>Run this on the "remote server":
>    Create Table RemoteTable (id int, txtdatacolumn text)
>    Insert RemoteTable
>    Values (1, 'remote')
>
>And run this on the "local server":
>    EXEC sp_addlinkedserver 'REMOTESERVER', 'SQL Server'
>    Create Table LocalTable (id int, txtdatacolumn text)
>    Insert LocalTable
>    Values (1, 'local')
>   
>    Create Clustered Index idx_LocalTable_id ON LocalTable (id)
>   
>    Update t1
>    Set t1.txtdatacolumn = t2.txtdatacolumn
>    From LocalTable t1
>    INNER JOIN RemoteServer.databasename.dbo.RemoteTable t2 ON (t1.id = t2.id)
>
>When I run the code I get an error.  However, if I comment out the "create
>index" line, the error does not occur.  Any thoughts?
>
>Here's the error I get:
>Server: Msg 8180, Level 16, State 1, Line 1
>Statement(s) could not be prepared.
>Server: Msg 306, Level 16, State 1, Line 1
>The text, ntext, and image data types cannot be compared or sorted, except
>when using IS NULL or LIKE operator.
>
>Thanks,
>Rob