SQL Server 2019 CT 2.2 and External Tables

[architectscott1]

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).  Additionally -- I can use SQL Server Management Studio on the instance and connect to both the local instance and the remote instance.  Now I want to create an External Table instance in the local instance with the table residing in the remote instance.  Using Azure Data Studio (with the 2019 Extensions activated) -- I attempt to create the External Table -- when I indicate the server and database and credential information for the remote server - I get the error "TCP Provider: No connection could be made because the target machine actively refused it" I have attached a screen shot. -- I can ping the instance -- I can telnet to it -- I can connect to the remote instance via Azure Data Studio and SSMS ....... BTW documentation in this area is pretty ugly -- and inconsistent.  In any case -- any ideas?  I do NOT have Polybase installed on the remote instance. 

[#2]

OK -- so now I made it a bit further -- the documentation is awful!!

I am now getting the following error:

105075;The user specified schema column count is 78, which varies from the external table schema column count of 81. The detected external table schema is: ([name] NVARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AS, [database_id] INT NOT NULL, [source_database_id] INT, [owner_sid] VARBINARY(85), [create_date] DATETIME2(3) NOT NULL, [compatibility_level] TINYINT NOT NULL, [collation_name] NVARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AS, [user_access] TINYINT, [user_access_desc] NVARCHAR(60) COLLATE SQL_Latin1_General_CP1_CI_AS, [is_read_only] BIT, [is_auto_close_on] BIT NOT NULL, [is_auto_shrink_on] BIT, [state] TINYINT, [state_desc] NVARCHAR(60) COLLATE SQL_Latin1_General_CP1_CI_AS, [is_in_standby] BIT, [is_cleanly_shutdown] BIT, [is_supplemental_logging_enabled] BIT, [snapshot_isolation_state] TINYINT, [snapshot_isolation_state_desc] NVARCHAR(60) COLLATE SQL_Latin1_General_CP1_CI_AS, [is_read_committed_snapshot_on] BIT, [recovery_model] TINYINT, [recovery_model_desc] NVARCHAR(60) COLLATE SQL_Latin1_General_CP1_CI_AS, [page_verify_option] TINYINT, [page_verify_option_desc] NVARCHAR(60) COLLATE SQL_Latin1_General_CP1_CI_AS, [is_auto_create_stats_on] BIT, [is_auto_create_stats_incremental_on] BIT, [is_auto_update_stats_on] BIT, [is_auto_update_stats_async_on] BIT, [is_ansi_null_default_on] BIT, [is_ansi_nulls_on] BIT, [is_ansi_padding_on] BIT, [is_ansi_warnings_on] BIT, [is_arithabort_on] BIT, [is_concat_null_yields_null_on] BIT, [is_numeric_roundabort_on] BIT, [is_quoted_identifier_on] BIT, [is_recursive_triggers_on] BIT, [is_cursor_close_on_commit_on] BIT, [is_local_cursor_default] BIT, [is_fulltext_enabled] BIT, [is_trustworthy_on] BIT, [is_db_chaining_on] BIT, [is_parameterization_forced] BIT, [is_master_key_encrypted_by_server] BIT NOT NULL, [is_query_store_on] BIT, [is_published] BIT NOT NULL, [is_subscribed] BIT NOT NULL, [is_merge_published] BIT NOT NULL, [is_distributor] BIT NOT NULL, [is_sync_with_backup] BIT NOT NULL, [service_broker_guid] UNIQUEIDENTIFIER NOT NULL, [is_broker_enabled] BIT NOT NUL...

Why do you guys make this such a painful process?????

scott

[#3]

Just playing about with it now Polybase doesn't seem to cope well with not having the same number of fields in each document within a collection. (Which is kinda the point of a NoSQL database)

Commenting here as would like to know if this is something that will be resolved before RTM.

[#4]

@architectscott1   

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.

[#5]

@Mayil_V_Chandran 

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