Linked Servers and updates.

I am trying to update some informaiton on a linked server based on information on the local server, and I get the following error:

Could not open table '"infoimg"."dbo"."queues2& #34;' from OLE DB provider 'SQLOLEDB'. The provider could not support a row lookup position. The provider indicates that conflicts occurred with other properties or requirements.

The table does exeist, and linked-server selects on it work just fine. It's when I try to run this statement:

update imgtr02.infoimg.dbo.queues2 set o_resource = null, lock_time = 0 from
imgtr02.infoimg.dbo.queues2 a, sysid_stuck b
where a.id = b.id

...that I have problems.

Any clues?

From what I remember, updating a table on a linked server requires there to be a unique index defined on the table to be updated.

Is there a unique index on your table?


------------
JG at 1/18/01 9:19:11 AM

Did you ever get anywhere with this? I'm having exactly the same problem and am having to do some crazy stuff to get around it.

Yup. Thanks for the suggestion, but there is a unique index on the table I'm trying to udpate. Any other suggestions?

I have experienced a similar issue and as well found documentation stating that the table requires a unique index, which I added and to no avail. The behavior I found was that without the index, it will return the error but with the index the query would just run forever. What I actually did as a workaround (not my ideal scenario) but it works for now was to put an sp on the server with the table being update and pass it the sql statement. Then I call the sp from the remote server and it worked!!. Go figure. Maybe this can help in the interim.




------------
jg at 1/19/01 12:41:43 PM

Yup. Thanks for the suggestion, but there is a unique index on the table I'm trying to udpate. Any other suggestions?

What are the databases you are trying to link?
Versions and SP, please?



------------
crazyjoe at 11/21/00 3:04:15 PM

I am trying to update some informaiton on a linked server based on information on the local server, and I get the following error:

Could not open table '"infoimg"."dbo"."queues2& #34;' from OLE DB provider 'SQLOLEDB'. The provider could not support a row lookup position. The provider indicates that conflicts occurred with other properties or requirements.

The table does exeist, and linked-server selects on it work just fine. It's when I try to run this statement:

update imgtr02.infoimg.dbo.queues2 set o_resource = null, lock_time = 0 from
imgtr02.infoimg.dbo.queues2 a, sysid_stuck b
where a.id = b.id

...that I have problems.

Any clues?

What are the databases you are trying to link?
Versions and SP, please?



------------
crazyjoe at 11/21/00 3:04:15 PM

I am trying to update some informaiton on a linked server based on information on the local server, and I get the following error:

Could not open table '"infoimg"."dbo"."queues2& #34;' from OLE DB provider 'SQLOLEDB'. The provider could not support a row lookup position. The provider indicates that conflicts occurred with other properties or requirements.

The table does exeist, and linked-server selects on it work just fine. It's when I try to run this statement:

update imgtr02.infoimg.dbo.queues2 set o_resource = null, lock_time = 0 from
imgtr02.infoimg.dbo.queues2 a, sysid_stuck b
where a.id = b.id

...that I have problems.

Any clues?