External Table is Azure Synpase Returning empty dataset

Question

Good Morning,

I attempted to create an external table using data from my Azure Gen 2 datalake. However, I encountered an error when attempting to run the query:

SELECT * FROM Uksponsor.Sponsors

Below, I have posted the code that I wrote to create a database, credential, external data source, external file format, and external table. Although my code runs, it returns an empty dataset without any data.

CREATE DATABASE LicensedSponsors
 COLLATE Latin1_General_100_BIN2_UTF8;
GO

USE LicensedSponsors;
GO

/* Create a password for credential */
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'tales'; 

CREATE DATABASE SCOPED CREDENTIAL Manageid
WITH
    IDENTITY='Managed Identity'; 
GO

/* CREATE EXTERNAL DATASOURCE */
CREATE EXTERNAL DATA SOURCE sponsor_Data 
WITH (
    LOCATION = 'abfss://[email protected]',
    CREDENTIAL = Manageid
);
GO

/* CREATE AN EXTERNAL FILE FORMAT */
CREATE EXTERNAL FILE FORMAT CsvFormat
    WITH (
        FORMAT_TYPE = DELIMITEDTEXT,
        FORMAT_OPTIONS (
            FIELD_TERMINATOR = ',',
            STRING_DELIMITER = '"'
        )
    );
GO

/* CREATE A SCHEMA */
CREATE SCHEMA Uksponsor;

/* CREATE AN EXTERNAL TABLE */
CREATE EXTERNAL TABLE Uksponsor.Sponsors -- Specifying schema
    (
    Organization VARCHAR(100),
    City VARCHAR(100),
    County VARCHAR(100),
    SponsorshipType VARCHAR(100),
    VisaRoute VARCHAR(100)
    )
WITH (
    LOCATION = 'Register of licensedsponsors/**',
    DATA_SOURCE = sponsor_Data ,
    FILE_FORMAT = CsvFormat
);
GO

Below i have posted the image of what was returned. enter image description here

I have tried to search online and look at other forums, such as this one: External table Azure Synapse does't returning data, but I haven't had any luck. I also attempted to change the data types when creating the external table, but I am still encountering the same problem. I even tried to create it automatically by using the "create external table" option, but it still returns an empty dataset. I suspect that there may be something wrong with the file or my code that is causing the issue.

Answer 1

External Table is Azure Synpase Returning empty dataset

The issue is the size of datatype it might exceeding beyond the limit what you given varchar(1000) try with varchar(max)

sample code:-

CREATE  EXTERNAL  TABLE [Uksponsor].[tablename] (
[Organisation Name] varchar(MAX),
[Town/City] varchar(MAX),
[County] varchar(MAX),
[Type & Rating] varchar(MAX),
[Route] varchar(MAX)
)
WITH (
LOCATION = 'Register of licensedsponsors/**',
DATA_SOURCE = [datasource],
FILE_FORMAT = [fileformat]
)
GO

Output: