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

[xchong.zhou]

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

The SQLScript is below:

Update [Hp-server].Newexec.dbo.Cu stomers_CoypTes t
set Unitname=b.Unit name
from [Hp-server].Newexec.dbo.Cu stomers_CoypTes t a join
Newexec.dbo.Cus tomers_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_Co ypTest"'
(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::Op enRowset returned 0x80040e21:
[PROPID=DBPROP_C OMMANDTIMEOUT VALUE=600 STATUS=DBPROPST ATUS_OK],
[PROPID=Unknown PropertyID VALUE=True STATUS=DBPROPST ATUS_OK],
[PROPID=DBPROP_I RowsetIndex VALUE=True
STATUS=DBPROPST ATUS_NOTSUPPORT ED]].

[Erland Sommarskog]

xc*********@gma il.com (xc*********@gm ail.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_CoypT est

The SQLScript is below:

Update [Hp-server].Newexec.dbo.Cu stomers_CoypTes t
set Unitname=b.Unit name
from [Hp-server].Newexec.dbo.Cu stomers_CoypTes t a join
Newexec.dbo.Cus tomers_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_Co ypTest"'
(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.Cu stomers_CoypTes t
set Unitname= (SELECT b.Unitname
FROM Newexec.dbo.Cus tomers_CoypTest b
WHERE a.Cid=b.Cid and b.Cid='Tony')
from [Hp-server].Newexec.dbo.Cu stomers_CoypTes t a join

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.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

[xchong.zhou]

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.Cu stomers_CoypTes t
set Unitname=b.Unit name
from [Wlrcserver].Newexec.dbo.Cu stomers_CoypTes t a join
Newexec.dbo.Cus tomers_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.

[Erland Sommarskog]

xc*********@gma il.com (xc*********@gm ail.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.Cu stomers_CoypTes t
set Unitname=b.Unit name
from [Wlrcserver].Newexec.dbo.Cu stomers_CoypTes t a join
Newexec.dbo.Cus tomers_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_addlinkedser ver for an example of setting
up a linked server with MSDASQL.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.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

[xchong.zhou]

EXEC xp_regwrite 'HKEY_LOCAL_MAC HINE',
'Software\Micro soft\MSSQLServe r\Providers\SQL OLEDB',
'DisallowAdhocA ccess', 'REG_DWORD', 0

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

[Erland Sommarskog]

xc*********@gma il.com (xc*********@gm ail.com) writes:

EXEC xp_regwrite 'HKEY_LOCAL_MAC HINE',
'Software\Micro soft\MSSQLServe r\Providers\SQL OLEDB',
'DisallowAdhocA ccess', '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****@sommarsk og.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