Posted by Erland Sommarskog on 11/18/05 23:15

xchong.zhou@gmail.com (xchong.zhou@gmail.com) writes:
> Thanks a lot for your reply.
>
> I try your script this morning, returned the same ERROR above.
> But at the other side,it works when i run the script below:
>
> Update [Wlrcserver].Newexec.dbo.Customers_CoypTest
> set Unitname=b.Unitname
> from [Wlrcserver].Newexec.dbo.Customers_CoypTest a join
> Newexec.dbo.Customers_CoypTest b
> on a.Cid=b.Cid and b.Cid='Tony'
> (1 row(s) affected)
>
> I use SQL Server 2000 with SP4 at [Wlrcserver],and SQL Server 2000 with
> SP3 at [HP-server].
> Both sides the OS Version are Windows advanced Server 2000.
> And I installed MDAC 2.7 yesterday,it didn't work.

To clarify: when you run from SP3 to SP4 it works, but when you run
from SP4 to SP3 it fails? Since the remote server is not doing anything
here; the error comes from OLE DB provider, SP4 is a but suspect here.
(But SP3 -> SP4 could work because SQL Server chooses another query plan.)

If you need this to work, I would suggest that you try to set up a linked
server with the MSDASQL provider. MSDASQL is OLE DB over ODBC, and usually
inferior, but sometimes it performs things differently than SQLOLEDB.
Look in Books Online under sp_addlinkedserver for an example of setting
up a linked server with MSDASQL.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]