Azure SQL Server Query error caused by WITH clause

Question

The following SQL from an Official Microsoft Azure team's tutorial on Azure Synapse Analytics is giving the following error related to WITH clause in common table expression (CTE):

Query in Azure Portal:

CREATE TABLE [dbo].[Trip]
(
    [DateID] int NOT NULL,
    [MedallionID] int NOT NULL,
    [HackneyLicenseID] int NOT NULL,
    [PickupTimeID] int NOT NULL,
    [DropoffTimeID] int NOT NULL,
    [PickupGeographyID] int NULL,
    [DropoffGeographyID] int NULL,
    [PickupLatitude] float NULL,
    [PickupLongitude] float NULL,
    [PickupLatLong] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [DropoffLatitude] float NULL,
    [DropoffLongitude] float NULL,
    [DropoffLatLong] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [PassengerCount] int NULL,
    [TripDurationSeconds] int NULL,
    [TripDistanceMiles] float NULL,
    [PaymentType] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [FareAmount] money NULL,
    [SurchargeAmount] money NULL,
    [TaxAmount] money NULL,
    [TipAmount] money NULL,
    [TollsAmount] money NULL,
    [TotalAmount] money NULL
)
WITH
(
    DISTRIBUTION = ROUND_ROBIN,
    CLUSTERED COLUMNSTORE INDEX
);

COPY INTO [dbo].[Trip]
FROM 'https://nytaxiblob.blob.core.windows.net/2013/Trip2013/QID6392_20171107_05910_0.txt.gz'
WITH
(
    FILE_TYPE = 'CSV',
    FIELDTERMINATOR = '|',
    FIELDQUOTE = '',
    ROWTERMINATOR='0X0A',
    COMPRESSION = 'GZIP'
)
OPTION (LABEL = 'COPY : Load [dbo].[Trip] - Taxi dataset');

Error before I replaced WITH clause with ;WITH:

Failed to execute query. Error: Incorrect syntax near 'DISTRIBUTION'. Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

But after following this response from user @KM on a similar error, when I replace WITH with ;WITH, I get the following error. The error in Azure Portal does not show the exact location of the error:

Error AFTER I replaced WITH clause with ;WITH:

Failed to execute query. Error: Incorrect syntax near '('.

Question: What may be missing in the Microsoft tutorial's query and how can we resolve the error(s)?

Remark: The following screenshot of the above sql in Azure Portal shows some red lines that seems to indicate the above sql has some syntax errors. But unlike SSMS, the intellisense in Azure Portal is not displaying the cause of those syntax errors. Maybe, someone with a better understanding of the above query may help.

Odele

See if this helps... learn.microsoft.com/en-us/sql/t-sql/statements/…. – Viki

Are you running this on SQL Server or Azure Synapse Analytics (SQL Data Warehouse)? SQL Server doesn't support the table options and will return the error you received ("Incorrect syntax near 'DISTRIBUTION'."). Ref: Create Table for SQL Server. – Voyage

@Voyage Not on SQL Server. I'm on the second step of this official tutorial where they are doing these steps in Azure Synapse Analytics - Synapse Studio. And hence I'm doing the same. Any suggestions? – Odele

Answer 1

Found the issue, I faced same error when I did not change the pool name. This command wont work in "SQL on-demand" so we should change the pool to new one created in step 1 of this tutorial and then it works.

I created with name - test, in tutorial, it is 'SQLDB1'. Select this and try running.

I will raise PR to add this line in MS Documentation.

Bolinger

The error message is misleading. You may want to raise the issue to your Azure team asking them to correct the error message. – Odele