Error: Msg 105070, The ODBC data source name was no specified or the driver is m

Question

Post reply

Error: Msg 105070, The ODBC data source name was no specified or the driver is m

Sara22

Old Hand

Points: 328
More actions
December 24, 2020 at 12:01 pm

#3823780
Hello everybody,
I am trying to use external table with PolyBase for import data from Oracle to Sql Server 2019.
I have an issue about creating an external table.
I created external data source OracleExternalData.
CREATE EXTERNAL DATA SOURCE OracleExternalData
WITH
( LOCATION = 'oracle://145.145.145.145:1521',
CONNECTION_OPTION = 'DSN=CData Oracle Sys'
CREDENTIAL = OracleProxyAccount
) ;
I couldn't create external table. I think that part in the locatio isn't good.
1. I try with script:
CREATE EXTERNAL TABLE payment5(
payment_id INT NOT NULL,
customer_id SMALLINT NOT NULL,
staff_id SMALLINT NOT NULL,
rental nvarchar(50) NOT NULL,
amount DECIMAL(5, 2) NOT NULL,
payment_date DATETIME2(6) NOT NULL
)
WITH (
LOCATION=’.schema.payment5’,
DATA_SOURCE=OracleExternalData
);
I got an error:
Msg 105070, The ODBC data source name was no specified or the driver is missing.
2. I put in location schema.table:
CREATE EXTERNAL TABLE payment5(
payment_id INT NOT NULL,
customer_id SMALLINT NOT NULL,
staff_id SMALLINT NOT NULL,
rental nvarchar(50) NOT NULL,
amount DECIMAL(5, 2) NOT NULL,
payment_date DATETIME2(6) NOT NULL
)
WITH (
LOCATION=’schema.payment5’,
DATA_SOURCE=OracleExternalData
);
I got error:
Msg 105077, The specific location string ’schema.payment5’ could not be parsed. A 2-part inedentifier was found. Excepted 3.
3. I put in location database.schema.table:
CREATE EXTERNAL TABLE payment5(
payment_id INT NOT NULL,
customer_id SMALLINT NOT NULL,
staff_id SMALLINT NOT NULL,
rental nvarchar(50) NOT NULL,
amount DECIMAL(5, 2) NOT NULL,
payment_date DATETIME2(6) NOT NULL
)
WITH (
LOCATION=’service1.schema.payment5’,
DATA_SOURCE=OracleExternalData
);
I got error:
Msg 105076, The specific location string ’service1.schema.payment5’ could not be parsed.
4. I created synonym for schema.table@database in Oracle database. And I put synonym in location:
CREATE EXTERNAL TABLE payment5(
payment_id INT NOT NULL,
customer_id SMALLINT NOT NULL,
staff_id SMALLINT NOT NULL,
rental nvarchar(50) NOT NULL,
amount DECIMAL(5, 2) NOT NULL,
payment_date DATETIME2(6) NOT NULL
)
WITH (
LOCATION=’synonym’,
DATA_SOURCE=OracleExternalData
);
I got error:
Msg 105077, The specific location string ’’ could not be parsed. A 1-part inedentifier was found. Excepted 3.
When I connect with SQL developer studio to Oracle I use
Hostname: 145.145.145.145,
Port: 1521
Service name: service1
I think that part in LOCATION in my script isn't god. Could anybody help me?
Thank you in advance for help me.
- This topic was modified 3 years, 4 months ago by Sara22.
- This topic was modified 3 years, 4 months ago by Sara22.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80320 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Scott Ohar Hall of Fame Points: 3607 More actions · Answer 2

Are you using a stored procedure or SSIS? Why not have Oracle just create a file to import into SQL? Are you using Oracle Instant Client ODBC Installation? I know a lot of people who have had issues just because if not using right Oracle Client.

Sara22 Old Hand Points: 328 More actions · Answer 3

I used Oracle Client and I imported data from Oracle using SSIS.

But I want to see how work new feature in SQL 2019 with External Oracle table? Is it faster or not?