Categories: Azure Devops

Database Build Error – Incorrect syntax near DISTINCT

I wrote a piece of SQL that had some new T-SQL syntax in it: IS DISTINCT FROM. This predicate checks if two expressions are equal taking NULL into account. I successfully ran the code on an Azure SQL Database. However, when I tried to deploy the code using an Azure Devops pipeline, it returned the following error:

Error SQL46010: Incorrect syntax near DISTINCT.

That’s weird, it didn’t error out in SSMS or in Visual Studio (SQL Server Data Tools). I opened SSDT and rebuild the project. No build errors.

I went to the pipeline in Azure Devops and triggered it again. It completed successfully. That’s even more weird. After some trial and error, I found the culprit. When you trigger a pipeline manually, you get the following dialog:

The agent specification had windows-2019 (it’s been a while since I created the pipeline) and I had changed it to windows-latest.

When taking the latest version of Windows (and probably a more recent version of MSBuild), the error doesn’t occur. Probably the 2019 version doesn’t know about IS DISTINCT FROM, as it was released in 2022.

Conclusion: make sure to use either the latest version, or a recent stable version.


------------------------------------------------
Do you like this blog post? You can thank me by buying me a beer 🙂
Koen Verbeeck

Koen Verbeeck is a Microsoft Business Intelligence consultant at AE, helping clients to get insight in their data. Koen has a comprehensive knowledge of the SQL Server BI stack, with a particular love for Integration Services. He's also a speaker at various conferences.

Recent Posts

Cool Stuff in Snowflake – Part 14: Asynchronous Execution of SQL Statements

I’m doing a little series on some of the nice features/capabilities in Snowflake (the cloud data warehouse).…

2 weeks ago

How I passed the DP-700 Exam

I recently took and passed the DP-700 exam, which is required for the Microsoft Certified:…

4 weeks ago

Take over Ownership in Microsoft Fabric

When you create an item in Microsoft Fabric (a notebook, a lakehouse, a warehouse, a…

2 months ago

Book Review – Agile Data Warehouse Design

I recently read the book Agile Data Warehouse Design - Collaborative Dimensional Modeling, from Whiteboard…

3 months ago

Cloudbrew 2024 – Slides

You can find the slides for the session Building the €100 data warehouse with the…

4 months ago

Book Review – Microsoft Power BI Performance Best Practices

I was asked to do a review of the book Microsoft Power BI Performance Best…

4 months ago