Geek Answers Handbook

T-SQL / DDL definition that cannot be performed in a transaction

There are various operators and system procedures that cannot be executed in a transaction with an error message "not allowed ..." (for example, create database ) or "unable to execute ..." (for example, exec sp_addrole ).

Is there a rule or set of rules that indicate whether a given operation / instruction is allowed in a transaction? Are there any listings?

+4
source share
1 answer

Not an official Microsoft resource, but the most complete list I have found so far ...

  • ALTER DATABASE
  • BACKUP LOG
  • BACKUP DATABASE
  • RESTORE LOG
  • RESTORE DATABASE
  • RECONFIGURE
  • UPDATE STATISTICS
  • LOAD DATABASE
  • LOAD TRANSACTION
  • CREATE DATABASE
  • DISK INIT
  • DROP DATABASE
  • System stored procedures that modify the MASTER database

The final element is a fairly broad category ... but you can reasonably predict which types of system stored procedures will modify the MASTER database ... essentially everything that works at the server level, not at the database level.

0
source

More articles:


All Articles