由WITH子句引起的Azure SQL Server查询错误 [英] Azure SQL Server Query error caused by WITH clause

问题描述

Microsoft Azure官方团队的

解决方案

发现了问题，当我不更改池名时遇到了同样的错误.该命令在"SQL on-demand"中不起作用.因此我们应该将池更改为在本教程的第1步中创建的新池，然后它就可以使用.

我用名称创建-测试，在教程中，它是'SQLDB1'.选择它并尝试运行.

我将提高PR以便在MS文档中添加此行.

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.

解决方案

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.

这篇关于由WITH子句引起的Azure SQL Server查询错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！