Forum Discussion
architectscott1
Feb 06, 2019Copper Contributor
SQL Server 2019 CT 2.2 and External Tables
I have set up two instances of SQL Server 2019 CTP 2.2 -- I have installed Polybase in standalone mode on a local instance and activated it (I can see it running as part of SQL Services). Additional...
Mayil_V_Chandran
Apr 10, 2019Copper Contributor
Coul you please explain me how you fixed the TCP error.I am having the same issue.Not able to create external table for sql server or Oracle.Your help will be much apprecuated.Thanks.
architectscott1
Apr 12, 2019Copper Contributor
The syntax that was causing me issues was in the CREATE EXTERNAL TABLE -- LOCATION:
LOCATION = 'sqlserver://ss2019azure.database.windows.net',
Here is a more complete script -- execute the following on your SS2019 instance (based on the table you have in Azure SQL)
First you need to create MASTER CREDENTIAL
--(CREATE MASTER CREDENTIAL not shown)
CREATE DATABASE SCOPED CREDENTIAL acmeCred WITH IDENTITY = 'remoteAdmin', SECRET = 'pty1234!';
go
CREATE EXTERNAL DATA SOURCE AzureDB
WITH (
LOCATION = 'sqlserver://ss2019azure.database.windows.net',
CREDENTIAL = acmeCred
);
go
CREATE EXTERNAL TABLE [dbo].[tblAcmeDataAzure]
(
ID varchar(10),
FName varchar(40),
LName varchar(40),
Address varchar(40),
City varchar(40),
Region varchar(40),
Country varchar(40),
PCode varchar(15),
Phone varchar(15),
Email varchar(64),
CCN varchar(20),
NIN varchar(15)
)
WITH (
LOCATION='dbAcmeAzure.dbo.tblAcmeDataAzure',
DATA_SOURCE=AzureDB
);
go