13

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

Improve this question
5
  • What's the detail steps and code that can reproduce this issue? Commented Oct 10, 2017 at 9:57
  • What's the result if you publish it through Visual Studio directly? Commented Oct 11, 2017 at 2:19
  • 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) Commented Oct 11, 2017 at 2:46
  • Do you solve this issue? Commented Oct 12, 2017 at 6:46
  • 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 Commented Oct 19, 2017 at 14:00

3 Answers 3

Reset to default
13

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

You can also use a PowerShell script if you wish to use a programmatic approach. learn.microsoft.com/en-us/azure/sql-database/scripts/…
13

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

Improve this answer

1 Comment

I'm curious if anyone knows anything about the forks of that gist and pros/cons of each.
0

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)"
Improve this answer

1 Comment

DBCC DBREINDEX is deprecated. Exec sp_msforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON ? REBUILD' instead

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.