database Project not running Post Deployment scripts

Question

We have a solution with three database projects. All three produced dacpacs are deployed sequentially but for some reason, one of these dacpacs does not run the post-deployment script.

We're using sqlpackage to create a diffscript, and the diffscript does correctly include the post-deployment statements.. here's a snippet

IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT N'The transacted portion of the database update succeeded.'
COMMIT TRANSACTION
END
ELSE PRINT N'The transacted portion of the database update failed.'
GO
DROP TABLE #tmpErrors
GO
/*
Post-Deployment Script Template                         
--------------------------------------------------------------------------------------
 This file contains SQL statements that will be appended to the build script.       
 Use SQLCMD syntax to include a file in the post-deployment script.         
 Example:      :r .\myfile.sql                              
 Use SQLCMD syntax to reference a variable in the post-deployment script.       
 --------------------------------------------------------------------------------------
*/
print 'SCRIPT: dbo.MEMTYPES.data.sql'

Where: a) The Comment is in the Header postdeployment.sql script, which calls other scripts using the standard syntax : :r .\dbo.MEMTYPES.data.sql , and b) The line " print 'SCRIPT: dbo.MEMTYPES.data.sql'" is the first line of the first child script defined in the postdeployment script.

However, when run, the log of the deployment ends with the line:

The transacted portion of the database update succeeded.

Which means the dacpac schema changes were applied (and they were) but presumably no attempt was made to continue in the script to run the post deployment tasks.

Any ideas?

Berriman

Can you take a look at the dacpac file generated when you build to see if the post-deploy scripts are included in there? Rename it to a .zip and check the postdeploy.sql file. You should have the full contents of all included files in the post-deploy section. You can also look at the script generated for publish to see if the contents are present from your post-deploy scripts. Also, is your post-deploy script set to a build action of "post-deploy"? – Fillian

Hi @PeterSchott thanks for the reply. The build action was fine, and I'd checked the sql statements were included in the script as I'm using the dacpac to create the script, then sqlcmd to execute the script. – Berriman

...What seems bizare is that the resolution was to remove the comments. The comments solely consisted of the default header listed in one of your blog posts, but these have caused odd problems before (the example usage for instance), and in this case removing the whole comment block altogether resolved my issue - weird as that may sound... – Berriman

Could it have been a bad closing block? I've never seen that behavior before, though did run into issues when trying to use ":On Error Ignore" which is valid SQLCMD syntax, but not supported by post-deploy scripts. What happened if you generated the script and ran the script? Could you add in some additional print/debug sections to test out whether it's getting to the post-deploy script? (running in SQLCMD mode, of course) – Fillian

I still cannot find a reason for the failure. I'd added start and end print comments within each child deployment script to get logged, and none of these were output for the script that failed to run (but were for the proceeding scripts). I'd also checked the scripts for unprintable characters in Notepad++ but couldn't see anything. Removing the comments now means the script does run but the comments aren't output. Not an ideal scenario fr supporting this.. :( – Berriman

So when you look at the script, the sections you're expecting to see are present, but don't run if there are comments? Does it make a difference if the comments are inline vs block? When the publish action generates a script, it should include every script that you told it to run. If you see the contents and it doesn't run, you may want to try just running that section of the publish script. If you don't see the contents, it's not being included properly. – Fillian

Answer 1

As it's a couple of months after I posted the question, and https://blog.stackoverflow.com/2011/07/its-ok-to-ask-and-answer-your-own-questions/ I guess I should state what the outcome was...

I couldn't find rhyme or reason for the issue other than removing the comments resolved the issue. Now that this has in effect been included in our rules to follow, we haven't come across this issue since.

Berriman

Your post deployment script has to be marked with Build Action "PostDeploy" in the properties of the script file. – Brott

Along with all other answers, Building the Database project helped in my case – Justinn

Answer 2

As user3683706 mentioned above, the Build Action of your post deployment script has to be PostDeploy (as shown in the selected file below). And there can only be one post-deployment script.

What if you want multiple script? You can reference them in one script like this

:r .\Post-Deployment_Scripts\Events.sql

Let me know if any questions! Upvotes are greatly appreciated!

Harrison

Answer 3

Have the same setting but didn't work

Doll