By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
460,023 Members | 1,297 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 460,023 IT Pros & Developers. It's quick & easy.

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

P: n/a
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::OpenRowset returned 0x80040e21:
[PROPID=DBPROP_COMMANDTIMEOUT VALUE=600 STATUS=DBPROPSTATUS_OK],
[PROPID=Unknown PropertyID VALUE=True STATUS=DBPROPSTATUS_OK],
[PROPID=DBPROP_IRowsetIndex VALUE=True
STATUS=DBPROPSTATUS_NOTSUPPORTED]].

Nov 23 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
xc*********@gmail.com (xc*********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Nov 23 '05 #2

P: n/a
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.

Nov 23 '05 #3

P: n/a
xc*********@gmail.com (xc*********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Nov 23 '05 #4

P: n/a
EXEC xp_regwrite 'HKEY_LOCAL_MACHINE',
'Software\Microsoft\MSSQLServer\Providers\SQLOLEDB ',
'DisallowAdhocAccess', 'REG_DWORD', 0

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

Nov 23 '05 #5

P: n/a
xc*********@gmail.com (xc*********@gmail.com) writes:
EXEC xp_regwrite 'HKEY_LOCAL_MACHINE',
'Software\Microsoft\MSSQLServer\Providers\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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Nov 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.