Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've downloaded the AdventureWorks based In-memory sample from here, and followed all the steps described in the accompanying doc. However, when I try to run the script in SQL Server Management Studio, I get the error message "ALTER DATABASE statement not allowed within multi-statement transaction" on line 9, which is the statement

IF NOT EXISTS (SELECT * FROM sys.data_spaces WHERE type='FX')
    ALTER DATABASE CURRENT ADD FILEGROUP [AdventureWorks2012_mod] CONTAINS MEMORY_OPTIMIZED_DATA
GO

Since this is (more or less) official Microsoft documentation, I'm assuming it's something I'm doing wrong, but I can't figure out what it is.

share|improve this question
add comment

2 Answers

up vote 4 down vote accepted

No, you're not doing anything wrong. I got the same thing. I solved it by breaking the sample up into multiple scripts and running each section of the script sequentially, in its own query window, instead of as one big script. This worked in my case because I'm always running these samples in an isolated VM (not on a production server!) and transaction handling is unnecessary since I'm the only one here.

Looking at the script again today more closely, there is no transaction handling defined explicitly, but perhaps you pasted the script into a query window that already had an active transaction, or created a new query window that automatically added BEGIN TRANSACTION; / COMMIT TRANSACTION; statements.

I also pointed out a couple of other potential gotchas in this blog post.

share|improve this answer
    
"perhaps you pasted the script into a query window that already had an active transaction" That seems to have been the problem, because when I ran the whole query in a new window, it worked. –  Petter Brodin Jan 12 at 13:18
add comment

I agree with @AaronBertrand you aren't doing anything wrong. This wouldn't be the first time I have seen a Microsoft script with a bug in it. Realistically with as many scripts as they publish I would be surprised not to see any.

Specifically the problem is that ALTER DATABASE is not allowed in a transaction at all. You can see the BOL reference here: Transact-SQL Statements Allowed in Transactions

In fact even as simple a script as this fails with the same error.

BEGIN TRANSACTION
ALTER DATABASE AdventureWorks2012 SET READ_WRITE
COMMIT

As Aaron said, remove the transaction handling (or at least the ALTER DATABASE statement from the transaction) and you should be fine.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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