CODEWITHSUNDEEP
sql-serverazurepolybasesql-server-2019
Scott Simmons
Scott Simmons

Reputation: 19

CREATE EXTERNAL DATA SOURCE from SS2019 CTP2.2 not working

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!!

Upvotes: 1

Views: 2336

Answers (2)

Scott Simmons
Scott Simmons

Reputation: 19

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

Upvotes: 0

Leon Yue
Leon Yue

Reputation: 16431

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.

Upvotes: 1

Related Questions