将数据库部署到Azure SQL失败，找不到sp_MSforeachtable [英] Deploy database to Azure SQL fails, sp_MSforeachtable not found

问题描述

我正在尝试使用.dacpac发布SQL Server数据库并将配置文件发布到Azure SQL数据库.我们已经在没有问题的前提下使用内部SQL Server了相当长的时间，现在我们正在评估Azure和AWS，以了解哪种最适合我们的需求.

I am trying to publish a SQL Server database using a .dacpac and publish profile to an Azure SQL database. We have been using on premises SQL Server with no problems for quite some time, and now we are evaluating Azure and AWS to see which is best suited for our needs.

我们有一个SQL Server数据库项目，希望将其部署到Azure SQL数据库，但是它无法执行SSDT生成的脚本.这是因为生成的脚本包含对存储过程sp_MSforeachtable的调用，该调用在Azure SQL中不存在.

We have a SQL Server database project and want to deploy it to Azure SQL database, however it fails to execute the script generated by SSDT. This is because the generated script contains a call to the stored procedure sp_MSforeachtable which does not exist in Azure SQL.

我还将目标平台从SQL Server 2016更改为Azure SQL V12.我正在尝试从Visual Studio 2017的发布配置文件以及使用Azure SQL数据库部署任务的VSTS发布管理中尝试此操作.当然，可以在任务中提供必要的.dacpac和publish.xml文件.

I also have changed the target platform from SQL Server 2016 to Azure SQL V12. I am trying this from Visual Studio 2017's publish profile and from VSTS Release management using Azure SQL database deployment task. An of course by providing the necessary .dacpac and publish.xml files in the task.

我知道我可以手动添加存储过程，但是这些发布/部署方法似乎并没有达到这个目的.有人有什么想法吗?

I know I can manually add the stored procedure, however it doesn't seem that is what is intended by these publishing/deployment methods. Does anyone have any ideas?

谢谢

推荐答案

sp_MSforeachtable是Master数据库中未记录的存储过程.显然，这还没有移植到Azure SQL.我相信您需要将其手动添加到Master数据库中.完成后，您的DACPAC在您自己的Azure SQL数据库上应该可以正常工作.我看不到手动添加sp_MSforeachtable的问题. DACPAC旨在使您的 数据库保持同步，而不是Master数据库保持同步.

sp_MSforeachtable is an undocumented stored procedure in the Master database. This apparently hasn't been ported over to Azure SQL. I believe you'll need to add it manually to the Master database. Once that is done, your DACPAC should work just fine on your own Azure SQL database. I don't see a problem with manually adding sp_MSforeachtable. DACPACs are meant to keep your database in sync, not the Master database.

这篇关于将数据库部署到Azure SQL失败，找不到sp_MSforeachtable的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！