Different Schemas produce Error retrieving data from one or more shards. The underlying error message received was: Invalid object name

Question

I created an EXTERNAL DATA SOURCE in Azure as an alternative to the inability to created LINKED SERVERS on the Azure platform.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'secretpassword';
CREATE DATABASE SCOPED CREDENTIAL LinkedServerCredential  
WITH IDENTITY = 'login_name', SECRET = 'login_password-here';

CREATE EXTERNAL DATA SOURCE LinkedProductionDb
WITH
(
   TYPE=RDBMS,
   LOCATION='azure.database.windows.net',
   DATABASE_NAME='ProductionDb',
   CREDENTIAL= LinkedServerCredential
);

Everything goes great, no errors and the statements executed successfully. So now I create an EXTERNAL TABLE having the identical table structure as the table that resides in the underlying database defined in the EXTERNAL DATASOURCE; but I want it assigned under a different SCHEMA to be able to distinguish it as a linked table in the targeted database. So I attempt this:

CREATE EXTERNAL TABLE [LNK].[Transactions](
    TransactionId BIGINT NOT NULL,
    CustomerId BIGINT NOT NULL,
    SubscriptionId BIGINT NOT NULL,
    ProductId BIGINT NOT NULL,
    TransType VARCHAR(100),
    TransKind VARCHAR(100),
    Success BIT,
    Amount MONEY,
    GatewayUsed VARCHAR(100),
    RecordImportDate DATETIMEOFFSET,
)
WITH
(
   DATA_SOURCE = LinkedProductionDb
)
GO

Now surprisingly, I am able to create this EXTERNAL TABLE without any issue; except for when I attempt to access the data via a simply query. I receive the error: "Error retrieving data from one or more shards. The underlying error message received was: 'Invalid object name 'LNK.Transactions'.'."

It only took me a few minutes to think about what I'd done and why I received the error. Obviously, since the table in the original database isn't created under the LNK schema; it is an invalid object.

So is there any way or methodical practice that I can use to distinguish my EXTERNAL TABLES from my physical tables within my database?

Obviously, among other things; this is undoubtedly one of the advantages of having separately defined objects as LINKED SERVERS within SQL.

I know I cannot be the only person who see's the benefit of segregating the objects to clearly distinguish them if they both need to reside in the same database.

Answer 1

It is possible to create the external table with a different schema from the one used in the external table by using the WITH options SCHEMA_NAME and OBJECT_NAME.

From Microsoft:

CREATE EXTERNAL TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name  
( { <column_definition> } [ ,...n ])     
{ WITH ( <rdbms_external_table_options> ) } 
)[;] 

<rdbms_external_table_options> ::= 
  DATA_SOURCE = <External_Data_Source>, 
  [ SCHEMA_NAME = N'nonescaped_schema_name',] -- This is what we want
  [ OBJECT_NAME = N'nonescaped_object_name',] -- ^

In the WITH, if you specify the SCHEMA_NAME and OBJECT_NAME, you can name the EXTERNAL TABLE something other that how the database with the table names it.

Example:

CREATE EXTERNAL TABLE [LNK].[Transactions](
    TransactionId BIGINT NOT NULL,
    CustomerId BIGINT NOT NULL,
    SubscriptionId BIGINT NOT NULL,
    ProductId BIGINT NOT NULL,
    TransType VARCHAR(100),
    TransKind VARCHAR(100),
    Success BIT,
    Amount MONEY,
    GatewayUsed VARCHAR(100),
    RecordImportDate DATETIMEOFFSET,
)
WITH
(
   DATA_SOURCE = LinkedProductionDb,
   SCHEMA_NAME = 'dbo', -- This is the name of the schema on the host database
   OBJECT_NAME = 'Transactions' -- Name of the table on the host database
)
GO

Now you can use this table as LNK.Transations (assuming the schema LNK is valid).