sql-server - 不同的架构会在从一个或多个分片检索数据时产生错误。收到的底层错误消息是 : Invalid object name

Question

我在 Azure 中创建了外部数据源，作为无法在 Azure 平台上创建链接服务器的替代方案。

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
);

一切都很顺利，没有错误，语句执行成功。因此，现在我创建一个 EXTERNAL TABLE，其表结构与驻留在 EXTERNAL DATASOURCE 中定义的基础数据库中的表相同；但我希望它分配在不同的模式下，以便能够将其区分为目标数据库中的链接表。所以我尝试这样做:

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

现在令人惊讶的是，我能够毫无问题地创建这个外部表；除非我尝试通过简单的查询访问数据。我收到错误:“从一个或多个分片检索数据时出错。收到的基本错误消息是:‘无效的对象名称‘LNK.Transactions’。’。”

我只花了几分钟时间就思考了我做了什么以及为什么收到错误。显然，由于原数据库中的表不是在LNK模式下创建的；它是一个无效的对象。

那么有没有什么方法或有条理的实践可以用来区分我的外部表和数据库中的物理表？

显然，除其他外；这无疑是在 SQL 中将对象单独定义为链接服务器的优点之一。

我知道我不是唯一一个看到隔离对象以清楚区分它们(如果它们都需要驻留在同一个数据库中)的好处的人。

Answer 1

可以使用 WITH 创建具有与外部表中使用的架构不同的架构的外部表。选项 SCHEMA_NAME和OBJECT_NAME .

来自微软:

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',] -- ^

在 WITH ，如果您指定 SCHEMA_NAME和OBJECT_NAME ，您可以将其命名为 EXTERNAL TABLE数据库与表的命名方式不同。

示例:

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

现在您可以将此表用作 LNK.Transations (假设架构 LNK 有效)。