Posted by Erland Sommarskog on 11/17/05 01:10

xchong.zhou@gmail.com (xchong.zhou@gmail.com) writes:
> I have a question when I work on Linkedserver
>
> The Linkedserver name is [Hp-server],the Datebase name is
> Newexec,the Table name is Customers_CoypTest
>
> The SQLScript is below:
>
> Update [Hp-server].Newexec.dbo.Customers_CoypTest
> set Unitname=b.Unitname
> from [Hp-server].Newexec.dbo.Customers_CoypTest a join
> Newexec.dbo.Customers_CoypTest b
> on a.Cid=b.Cid and b.Cid='Tony'
>
> The server returns ERROR like this:
>
> a) can not open this table '"Newexec"."dbo"."Customers_CoypTest"'
> (come from OLE DB provide server 'SQLOLEDB'). provide server do not
> support index scan on the data source.

Interesting error. :-)

Which version of SQL Server do you have, including service pack (on
both sides)? Also, since the MDAC be involved, which OS versions do
you have?

Also try this syntax:

Update [Hp-server].Newexec.dbo.Customers_CoypTest
set Unitname= (SELECT b.Unitname
FROM Newexec.dbo.Customers_CoypTest b
WHERE a.Cid=b.Cid and b.Cid='Tony')
from [Hp-server].Newexec.dbo.Customers_CoypTest a join

--
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

• Next in forum: Re: Indexes being improperly used when selecting data through a view
• Prev in forum: Re: sql query update
• Thread view: Re: There is a question when i work on Linkedserver~Pls kindly help me:)

[Reply to this message]