Microsoft SQL Server forum - There is a question when i work on Linkedserver~Pls kindly help me:)

Author

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

xchong.zhou@gmail.com

2005-11-16, 3:23 am

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

Erland Sommarskog

2005-11-16, 8:23 pm

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

xchong.zhou@gmail.com

2005-11-16, 8:23 pm

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.

Erland Sommarskog

2005-11-18, 8:24 pm

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

xchong.zhou@gmail.com

2005-11-22, 8:23 pm

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.

Erland Sommarskog

2005-11-23, 7:23 am

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

Sponsored Links