2

I am using DbUp to maintain and upgrade my SQL Server 2016 databases. Typical SQL scripts that I have executed so far included tasks like creating tables, creating or altering stored procedures, etc.

I am going to start using Memory Optimised Tables, and need to run ALTER DATABASE commands to create the necessary filegroups in the existing databases. I tried running a dynamic SQL script that builds and runs ALTER DATABASE commands containing the database name that the script is currently running for, but since DbUp uses transactional execution, it seems that ALTER statements are not allowed and I am getting this error:

Script block number: 2; Block line 1; Message:
System.Data.SqlClient.SqlException (0x80131904): ALTER DATABASE statement not allowed within multi-statement transaction.
ALTER DATABASE statement not allowed within multi-statement transaction.
ALTER DATABASE statement not allowed within multi-statement transaction
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

Any ideas how I can run ALTER DATABASE commands via DbUp, or is there an alternative way how to handle this case?

Thanks in advance.

  • I believe the actual problem is because you are running several ALTER statements in a single transaction. Try running each ALTER in its own transaction. – RLF Feb 14 '17 at 13:46
  • Another option 'might' be to use xp_cmdshell (in a script file) to kick off SQLCMD to run your ALTER - exec xp_cmdshell 'sqlcmd -S . -Q "ALTER DATABASE [Test] SET RECOVERY SIMPLE WITH NO_WAIT" -E' – Scott Hodgin Feb 14 '17 at 13:58
  • Or just change your scripts so that the database alters are outside of the transaction for tables/stored procedures. If you add a filegroup to a database to support In-Memory OLTP, then add physical files to the hard disk, and then some procedure doesn't get created because of a syntax error, do you really want to roll back the file/filegroup changes? Do you think that's even possible? – Aaron Bertrand Feb 14 '17 at 14:00
  • @RLF - just tried that, but it didn't work. Executing only one ALTER statement in a script produced the same error. – ChrisS Feb 14 '17 at 14:14
  • @ScottHodgin - that's a possibility, i'll try some prototypes and will see if it works. Thanks. – ChrisS Feb 14 '17 at 14:17
1

This is DbUp's default behavior; nothing you can do on the SQL Server side. There's a similar discussion on DbUp's github issues section with workaround that may work for you so do check it out. I don't think there's a DbUp tag on SE so if those workarounds don't work for you, try posting on that page or their discussion group.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.