External Table is Azure Synpase Returning empty dataset

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://ffs@datagen2db.dfs.core.windows.net',
    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.

Solution

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:

enter image description here