CREATE EXTERNAL DATA SOURCE from SS2019 CTP2.2 not working

Question

So ... I have 2 SQL Server 2019 instances (CTP2.2) and I have one installed with Polybase in single node config (reference this as SS-A). I have created MASTER KEY in the master of SS-A, and created a DATABASE SCOPED CREDENTIAL in a database on SS-A. When I try to do the following:

CREATE EXTERNAL DATA SOURCE acmeAzureDB WITH
   (TYPE = RDBMS,
    LOCATION = 'ss2019azure.database.windows.net',
    DATABASE_NAME = 'dbAcmeAzure',  
    CREDENTIAL = acmeAzureCred 
     );

I get an error

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'RDBMS'

I have tried to work with MS SQL Server SMEs without any luck (been working on this for many weeks to no avail). Any ideas here -- plus a message to Microsoft -- your docs on this are AWFUL!!

Do you mean the [CREATE EXTERNAL DATA SOURCE](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-data-source-transact-sql?view=sql-server-2017) documentation? — Jeroen Heier, Feb 27 '19 at 20:23
Yes -- unfortunately the syntax is slightly different with 2019 CTP (not 2017) -- and that is part of the problem -- the technology is in flight -- started with the early CTP release - did not work -- then went to CTP 2.2 in Dec -- and still does not work -- and MS SMEs seem to be unable to help ... nothing like being on the bleeding edge I guess — Scott Simmons, Feb 27 '19 at 22:20
Issue is that there is lots of conflicting information -- would be nice to just see an example of doing this for either SS2019 CTP to SS2019 CTP OR even SS2019 CTP to Azure SQL .... and the Polybase documentation is even worse .... — Scott Simmons, Feb 27 '19 at 22:22

score 2 · Answer 1 · answered Feb 28 '19 at 06:39

2

You have 2 SQL Server 2019 instances (CTP2.2). But they are not Azure SQL Database instance.

RDBMS External Data Sources are currently only supported on Azure SQL Database.

-- Elastic Database query only: a remote database on Azure SQL Database as data source   
-- (only on Azure SQL Database)  
CREATE EXTERNAL DATA SOURCE data_source_name  
    WITH (   
        TYPE = RDBMS,  
        LOCATION = '<server_name>.database.windows.net',  
        DATABASE_NAME = '<Remote_Database_Name>',  
        CREDENTIAL = <SQL_Credential>  
    )

Another way, you can create a linked server for your SQL Server 2019 instance to Azure SQL Database. Then you can query data from the Azure SQL DB as EXTERNAL DATA SOURCE.

To see this official tutorial: How to Create a Linked Server.

Reference blob:Incorrect syntax near 'RDBMS'. When I try to create external data source, Anyone having the same issue?

Hope this helps.

answered Feb 28 '19 at 06:39

Leon Yue

15,693
1
11
23

This is the issue with the documentation then -- "supposedly" SS2019 supports External Tables TO Azure SQL -- at least that is what I have heard from MS -- here is the link from the SS2019 CTP documentation ... https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-data-source-transact-sql?view=sql-server-ver15 -- it does not specifically state that SS2019 cannot do this (and MS told me that you can) -- however I have come to the same conclusion that it is not possible – Scott Simmons Feb 28 '19 at 13:33
I have seen this documentation. According it, I think you need to know that Azure SQL DB is remote database and supports EXTERNAL DATA SOURCE RDBMS type, SQL Server 2019 instance is local database doesn't support RDBMS type. It's the different feature between Azure SQL database and SQL Server instance. All of them support EXTERNAL DATA SOURCE. – Leon Yue Mar 01 '19 at 01:31
Leon -- Yes -- with SS2019 I can support "other" external data sources e.g Hadoop - BUT not RDBMS -- it seems that RDBMS is only supported as an external source in Azure SQL - but that it different than the MS SMEs have been saying for the last 3.5 months. Argh! – Scott Simmons Mar 01 '19 at 13:16

score 1 · Answer 2 · answered Mar 01 '19 at 23:22

SO - worked with MS today - and success -- you can do a CREATE EXTERNAL DATA SOURCE in SS2019 and point to AZURE SQL -- here is the TSQL I used:

(MASTER KEY ALREADY CREATED)

CREATE DATABASE SCOPED CREDENTIAL acmeCred WITH IDENTITY = 'remoteAdmin', SECRET ='XXXXXXXXX';
go
CREATE EXTERNAL DATA SOURCE AzureDB
WITH (   
    LOCATION = 'sqlserver://ss2019azure.database.windows.net',  
    CREDENTIAL = acmeCred
    ); 
go
CREATE EXTERNAL TABLE [dbo].[tblAcmeDataAzure]
(
ID varchar(10)
) 
WITH (
 LOCATION='dbAcmeAzure.dbo.tblAcmeDataAzure',
 DATA_SOURCE=AzureDB
);
go

CREATE EXTERNAL DATA SOURCE from SS2019 CTP2.2 not working

2 Answers2