Cannot update linked Server in SQl Server

I am attempting to update a table in an Oracle database via a SQL Server linked Server.  I need to update a table in Oracle named warehouse_level with criteria that matches a table within the QUANTUM_LIVE SQL Server database named LOKAD_MIN_MAX. I have included the SQL statement I am using below. I am able to select and update the Oracle table fine from SQL server via the linked Server but it does not work when I incorporate a SQL Server table in the WHERE clause. Need to update the warehouse_level table located in [QUANTUM]..[QCTL] linked Server comparing the PNM_AUTO _KEY in the [QUANTUM_LIVE].[dbo].[LOKAD_MIN_MAX] SQL Server table.  

UPDATE [QUANTUM]..[QCTL].[WAREHOUSE_LEVEL]
   SET QTY_MIN =  [QUANTUM_LIVE].[dbo].[LOKAD_MIN_MAX].QTY_MIN , QTY_MAX =  [QUANTUM_LIVE].[dbo].[LOKAD_MIN_MAX].QTY_MAX  
  WHERE PNM_AUTO_KEY =  [QUANTUM_LIVE].[dbo].[LOKAD_MIN_MAX].PNM_AUTO_KEY 

Open in new window

Capture.PNG
Capture2.PNG
Capture3.PNG
maximus1974Asked:
Who is Participating?
 
Eugene ZConnect With a Mentor Commented:
try to use openquery
see Solution
Guy Hengel [angelIII / a3] solution  -example from https://www.experts-exchange.com/questions/26362872/update-query-against-Oracle-from-SQL-Server-using-OpenQuery.html
update query against Oracle from SQL Server using OpenQuery
0
 
maximus1974Connect With a Mentor Author Commented:
Thank you but I am receiving an error when using openquery.
Capture.PNG
0
 
Shaun KlineLead Software EngineerCommented:
You are attempting to update a table using a second table not named in your query. This will not work. To use a second table, you need to include the FROM clause in your UPDATE statement.

Link to Update syntax on Microsoft's website: https://docs.microsoft.com/en-us/sql/t-sql/queries/update-transact-sql
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Eugene ZConnect With a Mentor Commented:
try this one:

UPDATE OPENQUERY (QUANTUM, 'SELECT QTY_MIN, PNM_AUTO_KEY FROM [QCTL].[WAREHOUSE_LEVEL] ')  
 SET QTY_MIN =  [QUANTUM_LIVE].[dbo].[LOKAD_MIN_MAX].QTY_MIN , QTY_MAX =  [QUANTUM_LIVE].[dbo].[LOKAD_MIN_MAX].QTY_MAX  
  WHERE PNM_AUTO_KEY =  [QUANTUM_LIVE].[dbo].[LOKAD_MIN_MAX].PNM_AUTO_KEY 

Open in new window

0
 
maximus1974Author Commented:
LVL 43, I am receiving an error when I use that statement.
Capture.PNG
0
 
Eugene ZConnect With a Mentor Commented:
error said that you do not have such table in the Oracle DB
[QCTL].[WAREHOUSE_LEVEL]
can you check if you have right table name and  "linked" Oracle user login has proper access to the oracle schema?

is it your oracle valid select  "SELECT QTY_MIN, PNM_AUTO_KEY FROM QCTL.WAREHOUSE_LEVEL"?
can you run it from Oracle Dev or Toad  tools?



after all checks and adjustments -- try to run from sql server

select * from OPENQUERY (QUANTUM, 'SELECT QTY_MIN, PNM_AUTO_KEY FROM QCTL.WAREHOUSE_LEVEL')
0
 
maximus1974Author Commented:
I ran the statement above and returned results fine. results attached. The table seems valid.

select * from OPENQUERY (QUANTUM, 'SELECT QTY_MIN, PNM_AUTO_KEY FROM QCTL.WAREHOUSE_LEVEL') 

Open in new window

Capture.PNG
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.