There is a question when i work on Linkedserver~Pls kindly help me:)

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.

b)     [OLE/DB provider returned message:Error occured when
multi-operate.If possible, please check each OLE DB status value.No
work had been completed.]

c)         OLE DB Error trace[OLE/DB Provider 'SQLOLEDB'
 IOpenRowset::OpenRow
set returned 0x80040e21:
& #91;PROPID=DBPROP_CO
MMANDTIMEOUT VALUE=600  STATUS=DBPROPSTATUS_
OK],
[PROPID=Unknown PropertyID VALUE=True  STATUS=DBPROPSTATUS_
OK],
& #91;PROPID=DBPROP_IR
owsetIndex VALUE=True
 STATUS=DBPROPSTATUS_
NOTSUPPORTED]].

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

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.

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 wit
h
> 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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

EXEC xp_regwrite  'HKEY_LOCAL_MACHINE'
,
 'Software\Microsoft\
 MSSQLServer\Provider
s\SQLOLEDB',
 'DisallowAdhocAccess
', 'REG_DWORD', 0

I ran the script above,the problem solved,Thank you very much.

xchong.zhou@gmail.com (xchong.zhou@gmail.com)  writes:
> EXEC xp_regwrite  'HKEY_LOCAL_MACHINE'
,
>        'Software\Microsoft\
 MSSQLServer\Provider
s\SQLOLEDB',
>        'DisallowAdhocAccess
', 'REG_DWORD', 0
>
> I ran the script above,the problem solved,Thank you very much.

Anything goes, as they say! Thanks for reporting back!


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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx