ODBC Driver Error Querying External Table

Question

I've set up an external table in Azure SQL in order to execute an elastic query according to the docs here https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-query-overview?view=azuresql

I can see the external table in Azure Data Studio, but when I select data I get the following error:

Msg 46833, Level 16, State 2, Line 1
An error occurred while executing query on remote server: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name

Answer 1

I get this error:

Msg 46833, Level 16, State 2, Line 1
An error occurred while executing query on remote server:
[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name

Make sure your connection is proper if you connected to any other database, you would get

Invalid object name

kind of error, because the object you want is not present in that database.

We reproduced same thing in our environment, and we can select data from external table.

Sample code:

create master key

CREATE MASTER  KEY;

Create scoped credentials

CREATE DATABASE SCOPED CREDENTIAL ElasqurDb12
WITH IDENTITY = 'username', SECRET = 'password';

Create external data source

CREATE EXTERNAL DATA SOURCE MyElASqurDC12 WITH
(
    TYPE = RDBMS,
    LOCATION = 'servername.database.windows.net',
    DATABASE_NAME = 'demo2',
    CREDENTIAL = ElasqurDb12,
);

Create external table

CREATE EXTERNAL TABLE Persons12 
(
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
) 
WITH 
(
    DATA_SOURCE = MyElASqurDC12,
    SCHEMA_NAME = 'dbo',
    OBJECT_NAME = 'Persons'
);

Select from external table

SELECT *  
FROM Persons12;

Output: