多语句事务中不允许使用ALTER DATABASE语句 [英] ALTER DATABASE statement not allowed within multi-statement transaction
问题描述
我编写了一个数据迁移脚本，我在其中检查先前的数据迁移是否正在进行，然后关闭所有数据库连接，这意味着更改数据库模式 MULTI_USER 模式到 RESTRICTED_USER 模式由donig所以我的数据库将进入限制模式，只有在更改数据库模式后才允许autherised连接我正在更新我的一些列，之后我将db恢复到多用户模式
但是当我运行它时会给我以下错误
Msg 6401，Level 16，State 1，Procedure SP_CollegeMigration，Line 234
无法回滚CollegeMigration。没有找到该名称的交易或保存点。
Msg 226，Level 16，State 6，Procedure SP_CollegeMigration，Line 236
多语句交易中不允许ALTER DATABASE语句。
Msg 50000，Level 16，State 6，程序SP_CollegeMigration，第260行
多语句交易中不允许ALTER DATABASE语句。
这是一些我的脚本
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
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
推荐答案
我认为你在整个SSMS中运行它。如果是这种情况，则将其解释为单个批次，批次具有限制。
要克服这种情况，请以较小的部分运行脚本或添加GO [ ^ ]单独操作之间的语句。
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.
关于SQL限制的一个想法：
这是一种声明性语言，与命令式相反。它没有提供任何要执行的指令序列。当您尝试在多语句事务中执行整个批处理时，您并没有真正说：更改数据库，然后在更改完成时更新已更改的数据库。你不是说更新数据库然后改变它。该事务仅确保对数据库的所有请求都执行，好像没有其他用户并行处理此数据库。 （实际上，其他一些语句实际上可以实际并行执行，但事务机制保证行为就好像逻辑上并非如此。）它具有严重的含义关于交易行为的对象。更改数据库必须在单独的声明中执行，而不是多语句事务的一部分。
请参阅：
http://en.wikipedia.org/wiki/Declarative_programming [ ^ ]，
http://en.wikipedia.org/wiki/Imperative_programming [ ^ ]，
http://en.wikipedia.org/wiki/Transaction_processing [ ^ ]。
-SA
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
这篇关于多语句事务中不允许使用ALTER DATABASE语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
