How make an UPDATE/INSERT query on Linked Server?

Question

I have a linked server to which I have to update data or insert new data coming from my DB. So, I have to define a query in SQL in order to do that I already search on the net, but any solution I found work. Can somebody help me??

Suppose that the name of my linkedServer is AAA and the table of linked server that I have to update is BBB, I tried with

UPDATE AAA.BBB AS I
SET
   I.X = @MYVALUEX
   I.Y = @MYVALUEY
WHERE
  ...

but it doesn't work.

Thanks in advance for any suggestions,

Deborah

Anytime you query a linked server you need to use the 4-partname (Server, database, schema, tablename). You also need to ensure a linked server is actually set-up. — HLGEM
– HLGEM, Commented Jul 11, 2013 at 19:13
The linked server is set-up. I check the RPC and RPC-Out properties and they are set to True. I don't understand why, using a select with the openquery function, everything goes right, but when I define the update query, it tell me that I'm using an Invalid object name. The DB linked throught linked server (and in which i Want apply changes) is an Oracle DB, while I'm working with SQL. May this could be the problem?? — Deby
– Deby, Commented Jul 12, 2013 at 7:51

Deby · Accepted Answer · 2013-07-12 08:41:10Z

3

I solve my problem using the EXEC command:

exec('update BBB
     set X = ''aaaaaa''
     WHERE Y = ''bbbbbb''') AT [AAA]

what do you think about this solution?

answered Jul 12, 2013 at 8:41

Deby

1751 gold badge3 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David Airapetyan Over a year ago

For this to work, one needs to make sure that the RPC and RPC Out options are set to True in Linked Server's properties (Server Options page)

Keith Smiley · Accepted Answer · 2013-07-11 19:06:36Z

2

update [linked-server].dbname.dbo.tablename
...
where
...

Don't change the dbo.

edited Jul 11, 2013 at 19:06

Keith Smiley

64.5k12 gold badges100 silver badges115 bronze badges

answered Jul 11, 2013 at 18:43

RoMEoMusTDiE

4,8341 gold badge19 silver badges26 bronze badges

1 Comment

Deby Over a year ago

I tried to use your suggestion, but it doesn't work! I get the follow error: "Invalid use of schema or catalog for OLE DB provider "OraOLEDB.Oracle" for linked server "AAA". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema." How can I solve??

frauneworld · Accepted Answer · 2013-07-11 17:50:40Z

0

delete the "AS" part, 'as' can be used in select statement, not in update, it's an alias, there's no reason to give one since you don't have a resultset, also change I.X to X and I.Y to Y

answered Jul 11, 2013 at 17:50

frauneworld

721 gold badge4 silver badges11 bronze badges

Collectives™ on Stack Overflow

How make an UPDATE/INSERT query on Linked Server?

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related