I want to insert records into an Oracle 8.03 database from MS SQL 2000. I have created a link and have used OPENQUERY to successfully query my Oracle tables. See example, DEV is the LINK name. I need to insert and update records from MS SQL to Oracle and also I need update MS SQL from
Oracle. Can you please give me a working example of insert and update?
example that works:
select *
from OPENQUERY(DEV, 'SELECT *
FROM USER.ORDERS_ALL')
It makes sense that this update would work, but it got WORSE after running this:
update
OPENQUERY(DEV, 'SELECT *
FROM USER.ORDERS_ALL')
set last_updated_by = 3
where orders_id = 1
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 53 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL
Server is terminating this process.
Connection Broken
From now on, I cannot get the link to work AT ALL! It is so strange. I have rebooted the machine with SQL Server and the Link on it. I added a new link. (Both show up as valid links.) I have gone into ODBC and tested the connection successfully. The Oracle server I am linked to is up and running and I can select from the table.
The only help I can find on Microsoft that is similar says I need SQL Server 2000 service pack 2, which I already have installed.
Now after getting this error, in SQL Server when I try to display the tables for the linked server in the Enterprise Mgr or run the simple select query (the first above) I get no response at all. The query goes off and 30 minutes later I have to break out of the SQL Query Analyzer or Enterprise Mgr because NOTHING happened except the logs show me as having timed out. I left the machine for hours to see if there were queries that needed to complete or something?? No luck -- it does not give results from the query.
:rolleyes:if you want to do that in the easy way, create view out of table of oracle database in sql server and then insert the view, but take care when you insert into the view you should take in the consideration all fields even if its null values
:)|||I don't think you can UPDATE using the OPENQUERY, however have you tried using sp_addlinkedserver and then doing the UPDATE
USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver
@.server = 'MyOracle',
@.srvproduct = 'Oracle',
@.provider = 'MSDAORA',
@.datasrc = 'MyServer'
GO
UPDATE MyOracle...ORDERS_ALL
SET last_updated_by = 3
WHERE orders_id = 1|||Yes, I used sp_addlinkedserver to create the link. Openquery is supposed to work for insert and update. The update statement you suggested doesn't work and that is why Openquery is needed.|||Have you tried rewriting the query by bring the WHERE clause into the OPENQUERY
UPDATE
FROM OPENQUERY(DEV, 'SELECT * FROM USER.ORDERS_ALL WHERE orders_id = 1')
SET last_updated_by = 3
There are 3 articles on technet that may help or are just a wild goose chase.
PRB: Installing DA SDK Causes SQL Distributed Queries to Fail (Q196292) (http://support.microsoft.com/default.aspx?scid=kb;en-us;Q196292)
After installing the Microsoft Data Access 2.0 SDK, the following errors may occur when trying to perform a SQL Server 7.0 distributed query:
FIX: Cannot Use Dynamic SQL Statements Within OPENQUERY (Q291376) (http://support.microsoft.com/default.aspx?scid=kb;en-us;Q291376)
An Access Violation (AV) may occur if you use the OPENQUERY function to execute a stored procedure that has these properties:
FIX: MDX Queries from Query Analyzer to a Linked Analysis Server Result in Fatal Exception (Q316295) (http://support.microsoft.com/default.aspx?scid=kb;en-us;Q316295)
When you execute a Multidimensional Expressions (MDX) query against a SQL Server Linked Analysis Server configured with the|||Originally posted by achorozy
[B]Have you tried rewriting the query by bring the WHERE clause into the OPENQUERY
UPDATE
FROM OPENQUERY(DEV, 'SELECT * FROM USER.ORDERS_ALL WHERE orders_id = 1')
SET last_updated_by = 3
Good idea, but no luck. Thank you!
:D|||Here is a solution using T-sql four part name convention - the previous solution listed like this was missing the Oracle Schema name:
UPDATE DEV..USER.ORDERS_ALL
SET last_updated_by = 3
WHERE orders_id = 1
Alternately, this should also work with OPENQUERY (note that Microsoft recommends the 'where 1 = 2' clause to prevent rows from being returned, which would add overhead to the query, and most likely cause the query to fail):
UPDATE OPENQUERY(DEV, 'Select * from USER.ORDERS_ALL where 1 = 2)
SET last_updated_by = 3
WHERE orders_id = 1
I have used both syntaxes successfully, but not until my dba set up our Ole DB provider to handle Heterogeneous updates/inserts (required a registry change). Hope this helps.|||Originally posted by zokrc
I have used both syntaxes successfully, but not until my dba set up our Ole DB provider to handle Heterogeneous updates/inserts (required a registry change). Hope this helps.
I will try the syntax you suggested early next week (the server crashed and needs new drives).
What do you mean by the above? Is that on the MS SQL Server box? How do you do it and which Ole DB provider?|||I should preface my response by saying that this only concerns you if you are trying to perform changes on the Oracle side as a part of a Sql Server distributed transaction (e.g. a transaction which can be rolled back). If you just want to make updates to the Oracle side, the syntax I have provided should stand on its own.
In my instance, I needed to include my updates/deletes/inserts to Oracle as a part of a Sql Server stored procedure which contained a Distributed Transaction. That way, if anything went wrong either side of the procedure (Oracle or SS), I would be able to roll back transactions in both databases.
SQL Server generally uses the MSDAORA Ole DB provider located on the SQL Server box to talk to Oracle. DTC (Distributed Transaction Coordinator) is the Sql Server component which actually manages the transaction and implements the appropriate Ole DB provider for executing heterogeneous queries.
Each ole db provider has certain properties which can be set that describe what functionality the provider will and won't support. To get distributed transactions to work with the MSDAORA, the ITransactionJoin(see books online for more info on this) property should be set accordingly. I believe this property can be set for the linked server through Enterprise Manager.
FINALLY - what I made reference to in my previous post was a problem we ran into where our MDAC registry settings were not set properly (a lot of things have to be in sync for Distributed Transactions to work). Here is the link on Microsoft's support site on how to do this (very complete!): http://search.support.microsoft.com/search/viewDoc.aspx?docID=KC.Q280106&dialogID=16829074&iterationID=1&sessionID=anonymous|15672521&url=kb;en-us;Q280106
Again, though, if your transactions aren't a part of a distributed transaction, you probably won't have to worry about this part. Let me know if you have any other questions.
