Sql-server – Connect to Oracle from SQL Server

oraclesql servert-sql

We are migrating an application from Oracle to SQL Server. The vendor is responsible for the majority of the migration efforts, but there is one piece that was developed in-house that I'm having trouble with.

The old application on Oracle has a procedure that collects specific data and then another procedure inserts that data into a staging table in our revenue system (a different Oracle server). This is an Oracle procedure via an Oracle Public Database Link.

In SQL Server, I'm trying to rewrite this process using SQL Server's Linked Servers. I've created the Linked Server connection to the revenue system and it can connect via that link.

From SQL*Plus, I can issue this statement as that user:

select * from rs.rs_myapp;

And I get results. (rs is the revenue system user, not the user I connect as. rs_MyApp table is the staging table for our data in their system.)

But if I attempt to do this in SQL Server:

select * from [rstest-Link]..rs.rs_myapp

I get this message:

Msg 7314, Level 16, State 1, Line 50 The OLE DB provider
"OraOLEDB.Oracle" for linked server "rstest-Link" does not contain
the table ""rs"."rs_myapp"". The table either does not exist or
the current user does not have permissions on that table.

I can do this, however:

select * from openquery([rstest-Link], 'select count(*) from rs.rs_myapp')

And that gets results back, showing that the link does work.

However, I cannot complete the SQL Server procedure, because of that 7314 error. The procedure is really basic, it has exactly one line of code in it:

Insert Into [rstest-Link]..rs.rs_myapp (ACCOUNT_NUMBER, FROM_DATE, TO_DATE, 
        Data1, Data2, SAMPLE_DATE, Content1, Content2) 
    select ACCOUNT_NUMBER, FROM_DATE, TO_DATE, MyData1, MyData2, SAMPLE_DATE, MyContent1, MyContent2 
    FROM dbo.RS_INTERFACE;

But without resolving the 7314 error, there's no way to create the procedure.

So how do I do this? Am I going to have to use a cursor and issue some sort of code for each row, like:

INSERT OPENQUERY ([rstest-Link], 'SELECT ... FROM ...')  
VALUES ('...');

Best Answer

Oracle uses a case-sensitive catalog, and hides that ugly fact from you by silently converting non-quoted identifiers to all caps, both in DDL and DML.

The name of your table is actually RS.RS_MYAPP, and SQL Server may be sending the identifier to Oracle as "rs"."rs_myapp", which would fail at Oracle.

So try

select * from [rstest-Link]..RS.RS_MYAPP

Related Question