Deploy database to Azure SQL fails, sp_MSforeachtable not found

sql-server azure azure-sql-database azure-devops azure-pipelines-release-pipeline
dotnetmensch picture dotnetmensch · Oct 9, 2017 · Viewed 7.2k times · Source

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

Answer

Rob Reagan picture Rob Reagan · Oct 9, 2017

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.

Related questions

Optimal way to concatenate/aggregate strings

I'm finding a way to aggregate strings from different rows into a single row. I'm looking to do this in many different places, so having a function to facilitate this would be nice. I've tried solutions using COALESCE and FOR …

Read More
How to view the roles and permissions granted to any database user in Azure SQL server instance?

Could you guide me on how to view the current roles/permissions granted to any database user in Azure SQL Database or in general for a MSSQL Server instance? I have this below query: SELECT r.name role_principal_name, …

Read More
How do I create a new user in a SQL Azure database?

I am trying to use the following template: -- ================================================= -- Create User as DBO template for SQL Azure Database -- ================================================= -- For login <login_name, sysname, login_name>, create a user in the database CREATE USER <user_…

Read More