Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Monday, December 3, 2012 6:19 AM
I want to insert data to Oracle 11g table from SQL Server 2008 R2. I have installed Oracle 11g client in server machine.
I have created a linked server and the connection was successful. I have also enabled Allow in process for OraOLeDb.Oracle in the providers.
If I try to retrieve the data using select query as:
SELECT * FROM TEST_AMT..PS_I2L_FAR_AMT_DET
Here TEST_AMT - linked server name, PS_I2L_FAR_AMT_DET - Oracle table name
it returns an error.
But if I use OPENQUERY it is fine. SELECT * FROM OPENQUERY("TEST_AMT", SELECT * FROM PS_I2L_FAR_AMT_DET)
But now if I'm facing a problem while inserting data to oracle table.
INSERT OPENQUERY (TEST_AMT, 'SELECT ITEMID FROM PS_I2L_FAR_AMT_DET') VALUES('1002');
Here: ITEMID- column name of oracle table
OR
INSERT OPENQUERY(TEST_AMT,'SELECT ITEMID FROM PS_I2L_FAR_AMT_DET') SELECT ItemID FROM Item;
Here: ItemID - SQL column name, Item - SQL table name
The error is:
OLE DB provider "OraOLEDB.Oracle" for linked server "TEST_AMT" returned message "".
OLE DB provider "OraOLEDB.Oracle" for linked server "TEST_AMT" returned message "ROW-00054: Cannot load the library
O/S-Error: (OS 126) The specified module could not be found.".
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "SELECT ITEMID FROM PS_I2L_FAR_AMT_DET" against OLE DB provider "OraOLEDB.Oracle" for linked server "TEST_AMT".
Is the error due to some permission rights from Oracle side or some permissions from SQL side? So is there any other way to insert data to oracle table? Please help.
Wednesday, December 5, 2012 5:19 AM ✅Answered
Can you look at these links?
they all look to be similar
http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/478eaf75-8b1a-4e07-a38c-30184374ff6d
http://www.ideaexcursion.com/2009/01/05/connecting-to-oracle-from-sql-server/
Regards
Satheesh
Monday, December 3, 2012 6:35 AM
Hello Sanketh ,
Not exactly sure about this error. But could be one of the below reason.
1. If there are more columns in the PS_I2L_FAR_AMT_DET table and if they have some contraint on them , error may occur.
2. You shoud have neccesary permissions to do this operation. Meaning your login or your login configured in the linked server should have proper permissions.
3. May be you can consider enabling RPC in and Out option in the linked server.
4. Make sure to have Microsoft Distributor Coorodinator service up and running.
Hope this helps.
Best Regards Sorna
Monday, December 3, 2012 7:27 AM
Hi,
First know link server creation between two database and then use openquery() to fetch data from Oracle database.
http://msdn.microsoft.com/en-us/library/ms188279(v=sql.105).aspx
Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/
Monday, December 3, 2012 7:32 AM
Can you try with the fully qualified name, that could be a problem
SELECT * FROM TEST_AMT.<<Schema>>.PS_I2L_FAR_AMT_DET
Regards
Satheesh
Monday, December 3, 2012 11:12 AM
Hi,
You can do this(oracle to SQL server or SQL to oracle) easily in integration Services (SSIS).
Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
Monday, December 3, 2012 12:23 PM | 1 vote
Hi Sanketh,
You should be better of using SSIS for this task as Loading data using LinkServer is slower as compaired to SSIS.
Regards,
Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)
Please remember to click
"Mark as Answer" on the post that helps you, and to click
"Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
Tuesday, December 4, 2012 5:00 AM
Thanks for the reply Sorna.
I have considered all the points you mentioned and still I'm facing the same error. There were constraints like all the fields in the oracle table cannot be left blank, values need to be provided. And i have all the permissions for the Oracle table. I've set RPC option to true. There are 6 columns in oracle table.
I tried another query by which i was successful in inserting the data to oracle table.
EXEC('INSERT INTO PS_I2L_FAR_AMT_DET(OraColName1, OraColName2, OraColName3, OraColName4, OraColName5, OraColName6)
** VALUES (?,?,?,?,?,?)','0123','123','123','123','0123','12345') AT TEST_AMT**
But if i modify the above query and use a select statement as below, then it returns an error.
EXEC('INSERT INTO PS_I2L_FAR_AMT_DET(OraColName1, OraColName2, OraColName3, OraColName4, OraColName5, OraColName6)
(SELECT SqlColName1, SqlColName2, SqlColName3, SqlColName4, SqlColName5, SqlColName6 FROM SqlTable)') AT TEST_AMT
Error:
OLE DB provider "OraOLEDB.Oracle" for linked server "TEST_AMT" returned message "ORA-00942: table or view does not exist".
Msg 7215, Level 17, State 1, Line 1
Could not execute statement on remote server 'TEST_AMT'.
So what may be the problem for this error??
Regards,
Sanketh
Tuesday, December 4, 2012 5:24 AM
I assume Test_AMT is the Oracle Server. in that case when you run the statement on Oracle server, it wont find the SqlTable, I believe.
Can you try running this directly on the sql server?
INSERT INTO <<LinkedServer.Database.Schema.>>PS_I2L_FAR_AMT_DET
(OraColName1, OraColName2, OraColName3, OraColName4, OraColName5, OraColName6)
(SELECT
SqlColName1, SqlColName2, SqlColName3, SqlColName4, SqlColName5, SqlColName6 FROM SqlTable)
Regards
Satheesh
Tuesday, December 4, 2012 7:46 AM
Do you have this "sqlTable" table on your oracle server?
Wednesday, December 5, 2012 5:04 AM
Hi Satheesh,
The query want you have given works in SQL Server 2005, but not in 2008 R2. Because in SQL Server 2005, there is MSDAORA providers, in the list of providers. But in SQL Server 2008, this provider will not appear.
I also tried to export data to oracle using Export Wizard.
If i try to export data using Export wizard in 2005, and click Test Connection, connection is fine, since i choose my destination as Oracle provider for OLEDB. But when i try to do this in SQL Server 2008 R2, i don't have this provider in my list. Hence i cannot create the connection
In SQL Server 2005 direct Select query will work. There is no need for Openquery.
So what may be the solution to my problem?
I'm running the query from SQL server.