Can't CREATE EXTERNAL DATA SOURCE in SQL

Question

I'm trying to create an external data source to access Azure Blob Storage. However, I'm having issues with creating the actual data source.

I've followed the instructions located here: Examples of bulk access to data in azure blob storage and Create external data source - transact sql. I'm using SQL Server 2016 on a VM accessing via SSMS on a client machine using Windows Authentication with no issues. Instructions say creating this external data source works for SQL Server 2016 and Azure Blob Storage.

I have created the Master Key:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = <password>

and, the database scoped credential

CREATE DATABASE SCOPED CREDENTIAL UploadCountries  
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = <key>;

I have verified both of these exist in the database by querying sys.symmetric_keys and sys.database_scoped_credentials.

However, when I try executing the following code it says 'Incorrect syntax near 'EXTERNAL'

CREATE EXTERNAL DATA SOURCE BlobCountries
WITH  (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://<somewhere>.table.core.windows.net/<somewhere>', 
    CREDENTIAL = UploadCountries  
);

Your thoughts and help are appreciated!

Steve.

Fei Han · Accepted Answer · 2017-10-16 08:49:30Z

1

In “Examples of Bulk Access to Data in Azure Blob Storage”, we can find:

Bulk access to Azure blob storage from SQL Server, requires at least SQL Server 2017 CTP 1.1.

And in Arguments section of “CREATE EXTERNAL DATA SOURCE (Transact-SQL)”, we can find similar information:

Use BLOB_STORAGE when performing bulk operations using BULK INSERT or OPENROWSET with SQL Server 2017

You are using SQL Server 2016, so you get Incorrect syntax near 'EXTERNAL' error when you create external data source for Azure Blob storage.

answered Oct 16, 2017 at 8:49

Fei Han

28k1 gold badge35 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Steven Kanberg Over a year ago

Well, it’s wonderfully helpful that it says it applies to SQL Server 2016 but I suppose there can always be caveats. Thank you for noting those. I obviously glossed over them trusting too heavily on the opening statement.

Kris Coleman Over a year ago

How do you even set up an azure sql database where you can select the version to be 2017? I've created a couple databases on Azure, but don't see the ability anywhere to change this. When I run SELECT @@VERSION they all return v12, but 2017 is v13.

Collectives™ on Stack Overflow

Can't CREATE EXTERNAL DATA SOURCE in SQL

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related