Not able to build Visual Studio Database Project targeting Azure SQL Database when using AT TIME ZONE

Question

We have a Visual Studio Database Project that we are trying to move to Azure SQL Database. Some of our views use AT TIME ZONE which is supported in Azure SQL Database, but when I switch the database Target Platform to Microsoft Azure SQL Database in the project properties, it fails to build with error:

Error: SQL46010: Incorrect syntax near TIME.

I have connected directly to the database in Azure and I can use AT TIME ZONE, but I cannot create or publish a DACPAC file from my project.

This SO answer suggests switching the target to SQL Server 2016, but then you cannot deploy the DACPAC to Azure. It will give the error

A project which specifies SQL Server 2016 as the target platform cannot be published to Microsoft Azure SQL Database v12.

when publishing from Visual Studio or the command line with sqlpackage.exe. (Same with all targets).

This seems like a long-standing bug in Visual Studio Data Tools so others must have run into it. Does anyone have a workaround or suggestions?

Answer 1

I found the answer to this through another channel. This is a bug in Visual Studio 16.4 and the current preview of 16.5.

The Visual Studio 16.4 release removed support for Microsoft Azure SQL Database V11. In doing so, they removed the Project target for Microsoft Azure SQL Database V12, making the Microsoft Azure SQL Database the only option. The intention was for this new option to switch to targeting V12, but there is a bug and it still targets V11.

Until this is fixed in a future update of Visual Studio, the workaround is to manually edit the SQLPROJ file and change the line;

<DSP>Microsoft.Data.Tools.Schema.Sql.SqlAzureDatabaseSchemaProvider</DSP>

Adding in V12 to make it;

<DSP>Microsoft.Data.Tools.Schema.Sql.SqlAzureV12DatabaseSchemaProvider</DSP>

Save and reopen the solution. It now compiles and I can deploy to Azure.

Answer 2

This is the answer just to the second part of the question.

I had the same error while publishing .dacpac from free azure sql database to the one in basic plan.

A project which specifies SQL Server 2016 as the target platform cannot be published to Microsoft Azure SQL Database v12.

I figured that out by checking Allow incompatible platform in Advance setting of Publish Data-tier Application.