CODEWITHSUNDEEP
azureazure-synapseazure-data-lake-gen2external-tables
Imran Saeed
Imran Saeed

Reputation: 13

How can I troubleshoot my Azure Synapse Serverless SQL Pool external table query throwing a 'location does not exist' error?

When I create and query an external table in Azure Synapse Server less SQL Pool it throws the following error:

External table 'EMP_DATA' is not accessible because location does not exist or it is used by another process.

The external table gets created and Its visible in the interface. But select statement on this table fails.

I used the following code to create the external table and query it with select statement:

CREATE MASTER KEY ENCRYPTION BY PASSWORD='sdfuhfhfdshfshfidhfeyrt23r593u4-3rhnfhhfhfg';

CREATE DATABASE SCOPED CREDENTIAL MyCredentials
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
 SECRET = 'QLYMgmSXMklt%2FI1U6DcVrQixnlU5Sgbtk1qDRakUBGs%3D';

IF NOT EXISTS (SELECT * FROM sys.external_file_formats WHERE name = 'SynapseDelimitedTextFormat')
CREATE EXTERNAL FILE FORMAT [SynapseDelimitedTextFormat]
  WITH (
    FORMAT_TYPE = DELIMITEDTEXT ,
    FORMAT_OPTIONS ( FIELD_TERMINATOR = ',', USE_TYPE_DEFAULT = FALSE )
  )
GO

IF NOT EXISTS (SELECT * FROM sys.external_data_sources WHERE name = 'nycsynapseefs_nycsynapseedl_dfs') 
  CREATE EXTERNAL DATA SOURCE [nycsynapseefs_nycsynapseedl_dfs] 
    WITH ( 
      LOCATION = 'abfss://[email protected]', 
      CREDENTIAL = [MyCredentials]
    )
GO

CREATE EXTERNAL TABLE [dbo].[EMP_DATA](
  [EmployeeID] [varchar](10) NULL,
  [LastName] [varchar](20) NULL,
  [FirstName] [varchar](20) NULL)
WITH (
  LOCATION = 'emp_data.csv',
  DATA_SOURCE = [MyDataSource],
  FILE_FORMAT = [SynapseDelimitedTextFormat]
)
GO

SELECT * FROM [dbo].[EMP_DATA]
GO

I tried to create the credentials with Managed Identity but its also not working.

Upvotes: 1

Views: 855

Answers (1)

czechmoose
czechmoose

Reputation: 36

Are you able to query the underlying file without using an external table? The error code you got usually is what I receive when I have not granted "Storage Blob Data Contributor" or "Storage Blob Data Reader" access to an individual on the storage account.

Upvotes: 0

Related Questions