2

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

enter image description here

enter image description here

Thom A
  • 88,727
  • 11
  • 45
  • 75
Mayank Sharma
  • 49
  • 1
  • 3
  • Can you run `select @@version` and post the results please? – wBob Feb 09 '21 at 17:00
  • 2
    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 Feb 09 '21 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 Feb 09 '21 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 Feb 09 '21 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 Feb 09 '21 at 19:59
  • Unfortunately I do not have a Managed Instance to test on but the documentation does confirm that the [skipping header row](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-file-format-transact-sql?view=sql-server-ver15&tabs=delimited#e-create-a-delimited-text-file-skipping-header-row-azure-synapse-analytics-only) syntax is for Azure Synapse Analytics only. Shall I write these comments up as the answer? – wBob Feb 09 '21 at 20:02
  • @wBob So in that case External file format is not supported on Azure SQL DB and MI. Can you please suggest any alternative to read tables from external BLOB on Azure SQL DB and MI if External file format is not working. – Mayank Sharma Feb 10 '21 at 07:23
  • As mentioned [EXTERNAL DATA SOURCE](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-data-source-transact-sql?view=sql-server-ver15&tabs=dedicated) is supported. Also see [this answer](https://stackoverflow.com/a/43707437/1527504). – wBob Feb 10 '21 at 13:57
  • @wBob Also tried with External Data source. It is successfully created from Azure BLOB but I am not able to read files from the BLOB container. It is showing the error "Cannot bulk load because the file "sample.txt" could not be opened. Operating system error code (null)." – Mayank Sharma Feb 10 '21 at 18:24

1 Answers1

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).

  1. Check whether the SAS key has expired. And please check the Allowed permissions. enter image description here

  2. 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
Joseph Xu
  • 5,607
  • 2
  • 5
  • 15