Error trying MS sample script

Question

Hi,

I'm learning Azure Synapse Analytics. I selected a sample script 'Query CSV files' from the Browse gallery and I'm trying to run a script to find a population of Luxembourg but instead of the result I'm getting the following error:

Potential conversion error while reading VARCHAR column 'country_code' from UTF8 encoded text. Change database collation to a UTF8 collation or specify explicit column schema in WITH clause and assign UTF8 collation to VARCHAR columns. Potential conversion error while reading VARCHAR column 'country_name' from UTF8 encoded text. Change database collation to a UTF8 collation or specify explicit column schema in WITH clause and assign UTF8 collation to VARCHAR columns. File 'https://sqlondemandstorage.blob.core.windows.net/public-csv/population/population.csv' cannot be opened because it does not exist or it is used by another process.

This is the query I'm running:

SELECT *
FROM OPENROWSET(
        BULK 'https://sqlondemandstorage.blob.core.windows.net/public-csv/population/population.csv',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIELDTERMINATOR =',',
        ROWTERMINATOR = '
'
    )
WITH (
    [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
    [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
    [year] smallint,
    [population] bigint
) AS [r]
WHERE
    country_name = 'Luxembourg'
    AND year = 2017;

I think I tried changing collation of a database but I cannot do that on master database. What can I do to fix the error?

Thanks in advance.

Answer 1

It says:

or specify explicit column schema in WITH clause and assign UTF8 collation to VARCHAR columns.

And you have

    [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
    [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,

What about

    [country_code] VARCHAR (5) COLLATE Latin1_General_100_CI_AS_SC_UTF8,
    [country_name] VARCHAR (100) COLLATE Latin1_General_100_CI_SC_UTF8,