External Table from Oracle to SQL 2019

user_1025932 0
Viewed 419

Im trying to create an external on SQL 2019, the table is located in a Oracle Database. (12C)

First Created the Master Key on SQL DB

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'reallycomplicatePassword@1';

Next I created the Oracle DB credential.

CREATE DATABASE SCOPED CREDENTIAL [OracleCredential]
WITH IDENTITY = 'oracleUserNameHere', SECRET = 'oraclePasswordHere';

Next I created the Data Source

CREATE EXTERNAL DATA SOURCE [OracleSalesSrvr]
WITH (LOCATION = 'oracle://myoracleserver',CREDENTIAL = [OracleCredential]);

and Finally I try to create the Table

  CREATE EXTERNAL TABLE Recharge(

    ORIGINTRANSACTIONID            VARCHAR(20) COLLATE Latin1_General_BIN NOT NULL,
    ORIGINOPERATORID               VARCHAR(255) COLLATE Latin1_General_BIN NOT NULL

   )
   WITH (
    LOCATION='.SomeOracleSchema.Recharge',
    DATA_SOURCE= OracleSalesSrvr
   );

but received the following error :

Msg 105082, Level 16, State 1, Line 1 105082;Generic ODBC error: [Microsoft][ODBC Oracle Wire Protocol driver]Insufficient information to connect to the data source. Additional error <2>: ErrorMsg: [Microsoft][ODBC Oracle Wire Protocol driver]Insufficient information to connect to the data source., SqlState: HY000, NativeError: 0

My guess i that Im Not specifiying the Services Name or SID while creating the EXTERNAL DATA SOURCE but Im not sure if that is possible or how to do it, (if it is the case).

oracle sql-server
1 Answers

Solution found by my dba : WITH( LOCATION='[ServiceName].[SchemaName].[Table]',

user_17647880
Related