How to exclude stored procedure from dacpac deployment to Azure SQL DB?

Question

I have an Azure SQL database project. I'm building and deploying the .dacpac via Azure Pipelines. But there is one stored procedure in the SQL project which we don't want to deploy on Azure SQL database, but wanted to keep it in the SQL project.

I couldn't find the way to exclude the stored procedure either at build time (during creation of .dacpac) or at deployment time (during deployment of the .dacpac). I tried some workarounds but it didn't work.

So is there any way to exclude a stored procedure during deployment of .dacpac to an Azure SQL database?

Answer 1

Try the following steps, as explained here.

• Unzip the *.bacpac file
• Open the model.xml file
• Find the offending stored procedure, comment the body of the procedure, and add a return statement
• Save and close model.xml Run the following PowerShell command:
• Get-FileHash fullpathto\model.xml | Format-List
• Copy the new checksum value
• Open Origin.xml
• Find the checksum (towards the bottom of the file) and replace it with the new checksum
• Save and close Origin.xml
• Zip all of the content into a new *.zip
• Rename the new *.zip into a *.bacpac (e.g. newfile2.bacpac)
• Import the new *.bacpac (using SQL Server Management Studio)

The dacchksum.exe works perfectly on Windows 10 but fails on Windows 11.

Answer 2

If this SP has no incoming references then you can modify your sqlproj file and change <Build Include="your_sp.sql" /> to <None Include="your_sp.sql" />. Thus it will not be added to dacpac file.