使用AT TIME ZONE时无法建立针对Azure SQL数据库的Visual Studio数据库项目 [英] Not able to build Visual Studio Database Project targeting Azure SQL Database when using AT TIME ZONE

问题描述

我们有一个

我已直接连接到Azure中的数据库，可以使用 AT TIME ZONE ，但是无法从我的项目中创建或发布 DACPAC 文件.

中删除

在以后的Visual Studio更新中解决此问题之前，解决方法是手动编辑 SQLPROJ 文件并更改该行；

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

添加 V12 以使其成功；

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

保存并重新打开解决方案.现在可以编译了，我可以部署到Azure了.

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?

解决方案

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.

这篇关于使用AT TIME ZONE时无法建立针对Azure SQL数据库的Visual Studio数据库项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！