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

3 Answers 3

up vote 5 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
1  
"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

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

Use "Go" to separate the transactions. That will solve the issue. ( Its easy than run one by one.) Also can change the Isolation level ( Not tested )

SET TRANSACTION ISOLATION LEVEL SERIALISABLE

Begin tran

---Statements goes here

commit tran

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
share|improve this answer
    
You may want to test your code before posting when others have already identified that ALTER DATABASE cannot be performed within a transaction. Setting the isolation level to SERIALIZABLE has no effect on this. –  Max Vernon Jul 30 at 4:09
    
"GO" isn't a SQL statement. It's an instruction to SSMS to submit the preceeding statement(s) to SQL Server as a batch. You can change this if you're feeling brave: Tools -> Options -> Query Execution -> SQL Server. Many batches can be submitted within a single transaction. –  Michael Green Jul 30 at 10:52

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.