CODEWITHSUNDEEP
sql-serverazure-sql-database
ernest
ernest

Reputation: 1724

I am unable to create an Azure external table with the DATA_SOURCE option

I am trying to do cross database queries and have setup an external data source via:

CREATE EXTERNAL DATA SOURCE ExampleDataSource
WITH ( 
    TYPE = RDBMS,
    LOCATION = 'example.database.windows.net',
    DATABASE_NAME = 'Database2',
    CREDENTIAL = "credentials"
);

After that, I created an elastic database pool and added my databases to it: enter image description here

From what I've been reading, that's all I should have to do. But it isn't working. Here's the external table on trying to create on the first database:

CREATE EXTERNAL TABLE [dbo].[tblEmployee](
    [EmployeeId] [UNIQUEIDENTIFIER] NOT NULL,
    [SecurityIdentifier] [NVARCHAR](255) NOT NULL,
    [BatchId] [UNIQUEIDENTIFIER] NULL,
    [FirstName] [NVARCHAR](50) NULL,
    [LastName] [NVARCHAR](50) NULL,
    [EmailAddress] [NVARCHAR](100) NULL,
    [PhoneNumber] [NVARCHAR](50) NULL,
    [NTAccount] [NVARCHAR](50) NULL,
    [DistinguishedName] [NVARCHAR](1024) NULL,
    [Title] [NVARCHAR](100) NULL,
    [Department] [NVARCHAR](50) NULL,
    [Company] [NVARCHAR](50) NULL,
    [ManagerDistinguishedName] [NVARCHAR](1024) NULL,
    [JpegPhoto] [VARBINARY](MAX) NULL,
    [Office] [NVARCHAR](50) NULL,
    [CountryCode] [INT] NULL,
    [DomainName] [NVARCHAR](50) NULL,
    [LocaleId] [UNIQUEIDENTIFIER] NULL,
    [Deleted] [BIT] NOT NULL,
 CONSTRAINT [PK_tblEmployee] PRIMARY KEY NONCLUSTERED 
(
    [EmployeeId] ASC
)
WITH 
(
    DATA_SOURCE = ExampleDataSource
) ON [PRIMARY]
) 

ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

And the error:

Msg 155, Level 15, State 1, Line 34
'DATA_SOURCE' is not a recognized  option.
Msg 102, Level 15, State 1, Line 34
Incorrect syntax near 'ExampleDataSource'.

Any ideas on what I'm doing wrong?

Upvotes: 2

Views: 1911

Answers (1)

ernest
ernest

Reputation: 1724

The problem was due to either the PK or just all the extra stuff on the CREATE EXTERNAL TABLE. I knew that I needed the EXTERNAL TABLE to be the same schema as the original table, so I just created a schema script from the original table and added the EXTERNAL and WITH stuff. So removing all the extra stuff solved the problem.

CREATE EXTERNAL TABLE [dbo].[tblEmployee](
    [EmployeeId] [UNIQUEIDENTIFIER] NOT NULL,
    [SecurityIdentifier] [NVARCHAR](255) NOT NULL,
    [BatchId] [UNIQUEIDENTIFIER] NULL,
    [FirstName] [NVARCHAR](50) NULL,
    [LastName] [NVARCHAR](50) NULL,
    [EmailAddress] [NVARCHAR](100) NULL,
    [PhoneNumber] [NVARCHAR](50) NULL,
    [NTAccount] [NVARCHAR](50) NULL,
    [DistinguishedName] [NVARCHAR](1024) NULL,
    [Title] [NVARCHAR](100) NULL,
    [Department] [NVARCHAR](50) NULL,
    [Company] [NVARCHAR](50) NULL,
    [ManagerDistinguishedName] [NVARCHAR](1024) NULL,
    [JpegPhoto] [VARBINARY](MAX) NULL,
    [Office] [NVARCHAR](50) NULL,
    [CountryCode] [INT] NULL,
    [DomainName] [NVARCHAR](50) NULL,
    [LocaleId] [UNIQUEIDENTIFIER] NULL,
    [Deleted] [BIT] NOT NULL)
WITH 
(
    DATA_SOURCE = ExampleDataSource
)

Upvotes: 4

Related Questions