What is wrong with this query?

[habneh]

I want to update a table tbl on serer svr1 and database db1

update tmp
set tmp.col = case when tmp.col1 = 1 then tmp.col1 + 1
else tmp.col1 + 2
end
from OPENROWSET('SQLOLEDB','svr1';'uid';'pwd','select * from db1.dbo.tbl') tmp

but it is erroring

the error is

Msg 208, Level 16, State 1, Line 1
Invalid object name 'tmp'.

a linkedserver is all setup

what would be the reason

Thanks

 

[AlexCuse]

I may be wrong here, but if you have a linked server can't you run something like

select * from server.database.owner.table

without using openrowset?

I think the problem is you are trying to update a query, not a table.

Hope this helps,

Alex


Ignorance of certain subjects is a great part of wisdom

 

[habneh]

Thanks, alex
how can we do update via linked server

 

[SQLDenis]

use 4 part notation and also SET XACT_ABORT to ON

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[habneh]

you mean

update svr1.db1.dbo.tmp
set col1 = 'a'?

I tried this and generated this error

[OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset returned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT VALUE=600 STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID VALUE=True STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate VALUE=True STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_IRowsetChange VA...
Msg 7306, Level 16, State 2, Line 1
Could not open table '"svr1"."dbo"."tmp"' 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.


Thanks,