12

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.

Database Project Properties

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?

Dale K
  • 25,246
  • 15
  • 42
  • 71
Rob Prouse
  • 22,161
  • 4
  • 69
  • 89
  • Hi Rob. Does this help? https://medium.com/modern-stack/deploy-a-sql-dacpac-to-azure-using-vsts-d24fb473302f – Alberto Morillo Jan 10 '20 at 22:21
  • @AlbertoMorillo, unfortunately, that doesn't help. The fix in the post is to pass in a property that 'retargets' the build to Azure. Doing so causes the AT TIME ZONE error. The post is basically doing what I do in the image above, but using a property at compile time. – Rob Prouse Jan 10 '20 at 22:31
  • Would you consider updating your DacFx. https://www.nuget.org/packages/Microsoft.SqlServer.DacFx.x86 and here https://www.microsoft.com/en-us/download/details.aspx?id=40735. Then do this https://stackoverflow.com/questions/50315074/unable-to-publish-dacpac-to-azure-sql-database-using-sqlpackage-exe/50321062#50321062 ? – Alberto Morillo Jan 10 '20 at 23:44
  • Thanks for the suggestions @AlbertoMorillo but I'd already updated SQL Package. The issue isn't with publishing, but with building the project when it targets SQL Azure. – Rob Prouse Jan 11 '20 at 15:24

2 Answers2

16

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.

Option Removed in 16.4

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.

Rob Prouse
  • 22,161
  • 4
  • 69
  • 89
  • 2
    Rob on the first article I shared the author did the same https://medium.com/modern-stack/deploy-a-sql-dacpac-to-azure-using-vsts-d24fb473302f – Alberto Morillo Jan 12 '20 at 02:32
  • You can accept it as answer. This can be beneficial to other community members. Thank you. – Leon Yue Jan 13 '20 at 06:16
  • @AlbertoMorillo in the article, the author changed the build target at compile time with a property, I was trying to change it in Visual Studio, but the V12 option has been removed. I had assumed that because it had been removed, it was no longer a valid compile target which was incorrect. – Rob Prouse Jan 13 '20 at 14:45
  • Wonderful! Thank you – Alberto Morillo Jan 13 '20 at 19:26
  • 1
    If you don't see V12 option , only `Microsoft Azure SQL Database` option , select it , and check SQLPROJ file , if that is `Microsoft.Data.Tools.Schema.Sql.SqlAzureV12DatabaseSchemaProvider` , then it will be fine . – Nan Yu Jul 01 '20 at 09:35
  • @AlbertoMorillo The solution works unless you don't copy the text in the code under "Here is the MSBuild Argument for each copy\paste;".They made a mistake and wrote a wrong variable. That confused me a little. – Amir Nov 30 '20 at 13:28
  • This also works in VS2022 with the following line, which throws the same error. `Microsoft.Data.Tools.Schema.Sql.Sql130DatabaseSchemaProvider` – Sabel Oct 06 '22 at 08:38
4

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.

enter image description here

Alamakanambra
  • 5,845
  • 3
  • 36
  • 43