SQL 7 Linked Server OLEDB Update Statement . . .

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 Administration (2000)

SQL 7 Linked Server OLEDB Update Statement . . .

Author

Topic

Jay99

468 Posts

Posted - 2002-03-08 : 14:19:20

I have a linked server setup from ServerA to ServerB (via the OLEDB provider for SQL Server) such that when ServerA logs on to ServerB it impersonates as SA.

now from QA, connected to ServerA . . . .


select * from serverb.mydb.dbo.mytable
delete serverb.mydb.dbo.mytable where myfield = 1
insert serverb.mydb.dbo.mytable(myfield, myvalue) values (1,'Hello')

... all works just fine, but


update serverb.mydb.dbo.mytable
set myvalue = 'Hello'
where myfield = 1

...returns this error...

quote:

Server: Msg 7306, Level 16, State 2, Line 1
Could not open table '"mydb"."dbo"."mytable"' 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.
[OLE/DB provider returned message: Errors occurred]

Now, I have a feeling that it has something to do with the fact that mytable in mydb is a heap (just based on the 'row lookup' verbage). I have worked around by implementing . . .


declare @sql nvarchar(500)
set @sql = N'update mydb.dbo.mytable set myvalue=''hello'' where myfield = 1'
exec serverb.master.dbo.sp_executesql @sql

...so I am not looking for solutions, rather I would like to know why this is. A quick search of www.microsoft.com and of BOL didn't turn anything up for me.

Thanks

Jay

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-03-08 : 14:28:54

sorry,
try [url]http://support.microsoft.com/search/preview.aspx?scid=kb;en-us;Q270119[/url]

look under "unique index requirement"

Edited by - setbasedisthetruepath on 03/08/2002 14:29:31

Jay99

468 Posts

Posted - 2002-03-08 : 14:33:05

quote:

sorry,
try [url]http://support.microsoft.com/search/preview.aspx?scid=kb;en-us;Q270119[/url]

look under "unique index requirement"

Edited by - setbasedisthetruepath on 03/08/2002 14:29:31

So for update statements on a linked server, the target table has to have a unique index. That is strange. The workaround is exactly my solution . . .

Thanks SBITTP

Jay

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources