Error while creating External File Format in Azure SQL Database

Question

Getting the error while creating External File Format in Azure SQL DB

Incorrect syntax near 'EXTERNAL'.

I am using the following commands (Used the T-SQL syntax from Microsoft Docs Link - https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-file-format-transact-sql?view=sql-server-ver15&tabs=delimited) but still getting the syntax error:

--Example 1
CREATE EXTERNAL FILE FORMAT textdelimited1 
WITH ( FORMAT_TYPE = DELIMITEDTEXT
, FORMAT_OPTIONS ( FIELD_TERMINATOR = '|')
GO

--Example 2
CREATE EXTERNAL FILE FORMAT skipHeader_CSV
WITH (FORMAT_TYPE = DELIMITEDTEXT,
      FORMAT_OPTIONS(
          FIELD_TERMINATOR = ',',
          STRING_DELIMITER = '"',
          FIRST_ROW = 2,
          USE_TYPE_DEFAULT = True)
)

Azure SQL DB supports CREATE EXTERNAL DATA SOURCE not FILE FORMAT, if that's what you're working on. Azure Synapse Analytics and Managed Instance do support DATA SOURCE. — wBob
– wBob, Commented Feb 9, 2021 at 17:29
@wBob the output of the SQL Server version : "Microsoft SQL Azure (RTM) - 12.0.2000.8 Oct 1 2020 18:48:35 Copyright (C) 2019 Microsoft Corporation " — Mayank Sharma
– Mayank Sharma, Commented Feb 9, 2021 at 18:05
@wBob Also I am running the same query on Azure SQL Managed Instance but there it is also showing the same error. — Mayank Sharma
– Mayank Sharma, Commented Feb 9, 2021 at 18:07
So as mentioned that statement won't work in Azure SQL DB which that version suggests. You also get this if you are in the master database. Switch to the appropriate database. The @@version response for Synapse is currently Microsoft Azure SQL Data Warehouse - 10.0.15554.0 Dec 10 2020 03:11:10 Copyright (c) Microsoft Corporation — wBob
– wBob, Commented Feb 9, 2021 at 19:59

Joseph Xu · Accepted Answer · 2021-02-11 01:42:52Z

0

As @wBob mentioned, since External file format is not supported on Azure SQL DB and MI. We can use EXTERNAL DATA SOURCE. There are many reasons for this problem (Cannot bulk load because the ... could not be opened).

Check whether the SAS key has expired. And please check the Allowed permissions.
Did you delete the question mark when you create the SECRET?

CREATE DATABASE SCOPED CREDENTIAL UploadInvoices
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=2019-12-12******2FspTCY%3D'

I've tried the following test, it works well.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '***';
go

CREATE DATABASE SCOPED CREDENTIAL UploadInvoices
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=2019-12-12&ss=bfqt&srt=sco&sp******%2FspTCY%3D'; -- dl


CREATE EXTERNAL DATA SOURCE MyAzureInvoices
    WITH (
        TYPE = BLOB_STORAGE,
        LOCATION = 'https://***.blob.core.windows.net/<container_name>',
        CREDENTIAL = UploadInvoices
    );

BULK INSERT production.customer
FROM 'bs140513_032310-demo.csv'
WITH
    (
        DATA_SOURCE = 'MyAzureInvoices',
        FORMAT = 'CSV',
        FIRSTROW = 2
    )
GO

answered Feb 11, 2021 at 1:42

Joseph Xu

6,1432 gold badges7 silver badges17 bronze badges

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

2 Comments

Julaayi Over a year ago

This method isn't working with parquet format. I am getting an error when doing BULK INSERT.

Muhammad Waheed Over a year ago

I am getting this error Parse error at line: 5, column: 2: Incorrect syntax near 'BULK'.

Collectives™ on Stack Overflow

Error while creating External File Format in Azure SQL Database

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