ALTER DATABASE statement not allowed within multi-statement transaction

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

I wrote a data migration script in which I am checking if the previous data migration is in process then close all db connections this mean change the database mode which is MULTI_USER mode to RESTRICTED_USER mode by donig so my database will go on restricted mode and only autherised connection will be allowed after change the database mode I am updating my some column and after that I am restoring db to multi user mode

But when I run it gives me following error

Msg 6401, Level 16, State 1, Procedure SP_CollegeMigration, Line 234
Cannot roll back CollegeMigration. No transaction or savepoint of that name was found.

Msg 226, Level 16, State 6, Procedure SP_CollegeMigration, Line 236
ALTER DATABASE statement not allowed within multi-statement transaction.

Msg 50000, Level 16, State 6, Procedure SP_CollegeMigration, Line 260
ALTER DATABASE statement not allowed within multi-statement transaction.

This is some of my script

SQL

IF(@preMigrationStatus = 'InProcess' and @isForceFull = 'Yes')
BEGIN
/*if the previous migration is in process then inside if i am changing the
 db mode to restricted_user mode
*/
SET IMPLICIT_TRANSACTIONS OFF
ALTER DATABASE  AlAhsaan2014 SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;

UPDATE TblCollegeMigrationHistory
SET MigrationStatus ='Failed',
    Comments = Comments + 'Migration Failed at '+ Convert(nvarchar, getdate()) + ' ;'
WHERE HistoryID = @HistoryId;

ALTER DATABASE AlAhsaan2014 SET MULTI_USER WITH ROLLBACK IMMEDIATE;
SET IMPLICIT_TRANSACTIONS ON
/* Select statement goes here */

END

Posted 24-Feb-15 5:11am

khurram ali lashari

Updated 24-Feb-15 19:57pm

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2015-02-24T05:19:00

Solution 1

I take it you're running this in SSMS as a whole. If that's the case it is interpreted as a single batch and a batch has restrictions.

To overcome such situation either run the script in smaller parts or add GO[^] statements between separate operations.

Posted 24-Feb-15 5:19am

Wendelius

Comments

Sergey Alexandrovich Kryukov 24-Feb-15 11:39am

5ed. I tried to add some ideas on the background of such behavior, please see Solution 2.
—SA

Wendelius 24-Feb-15 23:26pm

Thanks SA!

Sergey Alexandrovich Kryukov · Answer 2 · 2015-02-24T05:38:00

Just an idea on this limitation of SQL:

This is a declarative language, which is the opposite to imperative. It does not provide a sequence of any instructions to be carried out. When you try to execute a whole "batch" in a multi-statement transaction, you are not really saying: "alter the database and then, when altering is complete, update the altered database". You are not saying "update the database and then alter it" either. The transaction only ensures that all the requests to the database are executed as if there were no other users working of at this database in parallel. (In fact, some other statements could be actually performed in parallel, but the transaction mechanism guarantees the behavior as if logically it wasn't the case.) It has serious implication on the object of transactional action. Altering the database has to be performed in a separate statement, not part of your multi-statement transaction.

Please see:
http://en.wikipedia.org/wiki/Declarative_programming[^],
http://en.wikipedia.org/wiki/Imperative_programming[^],
http://en.wikipedia.org/wiki/Transaction_processing[^].

—SA