update using linked server - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

update using linked server

Author

Topic

nizmaylo
Constraint Violating Yak Guru

258 Posts

Posted - 2003-02-23 : 21:28:18

While executing this query

UPDATE OPENQUERY(LINKEDSERVER1, 'SELECT * FROM mydatabase..product') SET long_description='my test'
FROM OPENQUERY(LINKEDSERVER1, 'SELECT * FROM mydatabase..product') a
INNER JOIN product b ON a.product_id=b.product_id
WHERE b.product_id='0201'

I get the following error:
"Could not fetch a row using a bookmark from OLE DB provider 'MSDASQL'.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetLocate::GetRowsByBookmark returned 0x80040e21: ]."

What seems to be the problem? If use SELECT instead of UPDATE there will be no errors.

Simple UPDATE (without making a joint on openquery) works as well.

helena

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-02-24 : 14:25:06

the OPENQUERY() function returns a bunch of records, not a connection to a database.

- Jeff

nizmaylo
Constraint Violating Yak Guru

258 Posts

Posted - 2003-02-25 : 13:49:59

Thanks for your input, but I believe it's a little of the topic.

Here is the answer for anyone who's interested.

First, let me quote BOL
"Executes the specified pass-through query on the given linked server, which is an OLE DB data source. The OPENQUERY function can be referenced in the FROM clause of a query as though it is a table name. The OPENQUERY function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENQUERY returns only the first one."

So, from the first glance there should be nothing wrong with the query in question. However, there is. OPENQUERY should either be referenced in the FROM clause OR be a target of UPDATE/INSERT/DELETE.
In case both are needed, the second one will require another linked server or instead of performing a joint in the FROM clause, a cursor could be created to extract data from the other table.

At any rate, I figured it out by reading the forum for the keyword OPENQUERY. Thanks to everyone for sharing the experience.

helena

philh
Starting Member

18 Posts

Posted - 2003-03-07 : 15:10:46

My statement fulfills all the requirements listed above, and still returns a 7333.

In this case, I'm updating a Visual FoxPro table through the linked server with information in a SQL table. So, the only reference to OPENQUERY is as the target of the UPDATE statement:

UPDATE OPENQUERY(FOXDBC,'select oh_pk as ohpk,oh_fk from mytable')
set oh_fk = mySQLtable.oh_stfk
from mySQLtable
where mySQLTable.oh_pk = ohpk

Any ideas?

Phil Hegedusich
Senior Web Developer
IIMAK
http://www.iimak.com
My views are not necessarily my own.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources