Deploy database to Azure SQL fails, sp_MSforeachtable not found

10
votes

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.

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.

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?

Thanks

3
sql-serverazureazure-sql-databaseazure-devopsazure-pipelines-release-pipeline
What's the detail steps and code that can reproduce this issue? – starian chen-MSFT
What's the result if you publish it through Visual Studio directly? – starian chen-MSFT
I can't reproduce this issue with a simple database project (a table and a store procedure). Can you share the detail build and release log on the OneDrive? (Set/add system.debug variable to true) – starian chen-MSFT
Do you solve this issue? – starian chen-MSFT
So after digging a little further, I didn't realize that my team, some time ago, had added a pre-deployment script that used this stored procedure. So I added the creation of it to the predeployment script and we are good to go. Thanks – dotnetmensch

3 Answers

11
votes

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.

10
votes

This feature is not implemented in SQL Database in Azure.

You can find a copy of it at this location: https://gist.github.com/metaskills/893599

0
votes

Correct, sp_MSforeachtable stored procedure is still missing in Azure SQL, but you can easily create it manually. It is very helpful when you have to rebuild indexes for all tables at once:

exec sp_MSforeachtable @command1="DBCC DBREINDEX ('?', '', 80)"