SSDT transactional publish?

Question

Using msbuild with SSDT, I have tried IncludeTransactionalScripts, but it seems to only put each statement in an individual transaction. Is it possible to publish multiple database projects as a transaction? If not, can I at least make sure that each Project is published within a transaction?

Answer 1

For anybody else who found this question, but didn't know where to look:

There is a publish setting, "Include transactional scripts", that begins a transaction at the start of the publish script. It then wraps each schema change in the script in an error check. On errors, the transaction is rolled back and an error log is incremented, but then the transaction is started from scratch. If there is no error, the transaction continues uncommitted

BEGIN TRANSACTION

...alter object here

IF @@ERROR <> 0  @@TRANCOUNT > 0
BEGIN
    ROLLBACK;
END

IF @@TRANCOUNT = 0
BEGIN
    INSERT  INTO #tmpErrors (Error)
    VALUES                 (1);
    BEGIN TRANSACTION;
END

At the end, if errors have been registered, the whole transaction is rolled back:

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

---

Note (as in the comments above) that this doesn't include your pre-/post-deployment scripts, so you should be careful to wrap these separately

---

The link below is to Peter Schott's blog - it's same guy who commented above I think - thanks :)

http://schottsql.blogspot.co.uk/2012/11/ssdt-publishing-your-project.html

He explains nicely how to configure these settings when using SSMS

---

Using Visual Studio 2015, I found that the "Include transactional scripts" setting was not set by default. You have to select this when defining the Publish settings ([Build]>>[Publish {project name} ...]) under the [Advanced...] tab: