Home > IT Answers > SQL Server > SQL Server transactions TSQL question.
Fdemid
25 pts.
 SQL Server transactions TSQL question.
SQL Server administration, T/SQL, Transition Packsaction
Does a list of Transact SQL statements, that cannot be used within transactions, exist please? Is it available on the Web?

Software/Hardware used:
Windows PC, SQL Server 2005
ASKED: August 20, 2009  5:19 PM
UPDATED: August 25, 2009  2:53 PM
RELATED QUESTIONS
Answer Wiki:
Pretty much every T/SQL statement can be used within an explicit transaction. I can't think of any off the top of my head that can't be used within a transaction, as every command that you run is executed within its own implicit transaction.
Last Wiki Answer Submitted:  August 20, 2009  8:27 pm  by  Denny Cherry   64,520 pts.
All Answer Wiki Contributors:  Denny Cherry   64,520 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

How about
Error Message:
Msg 6115, Level 16, State 1, Line 1
KILL command cannot be used inside user transactions.?

Or this one:
The DROP DATABASE statement must run in autocommit mode and is not allowed in an explicit or implicit transaction.

Also
The BACKUP statement is not allowed in an explicit or implicit transaction.

Fdemid
 25 pts.
 

I found this today in BOL:

“You can use all Transact-SQL statements in an explicit transaction, except for the following statements:

ALTER DATABASE
DROP DATABASE

ALTER FULLTEXT CATALOG
DROP FULLTEXT CATALOG

ALTER FULLTEXT INDEX
DROP FULLTEXT INDEX

BACKUP
RECONFIGURE

CREATE DATABASE
RESTORE

CREATE FULLTEXT CATALOG
UPDATE STATISTICS

CREATE FULLTEXT INDEX

You also cannot use the following:

Full-text system stored procedures in an explicit transaction. For more information, see Full-Text Search Stored Procedures (Transact-SQL).

sp_dboption to set database options or use any system procedures that modify the master database inside explicit or implicit transactions.”

Ref:

SQL Server 2008 Books Online (July 2009)
Transact-SQL Statements Allowed in Transactions

carlosdl
 63,535 pts.
 

Thanks, Carlosdl.
This is the document that I was interested in.
It does not seem to be complete though:

c:1>sqlcmd -Sigorinspironsqlexpress -E
1> begin tran kill 51 rollback
2> go
Msg 6115, Level 16, State 1, Server IGORINSPIRONSQLEXPRESS, Line 1
KILL command cannot be used inside user transactions.

This a question to BOL authors, I know.
Sincerely,
–Igor

Fdemid
 25 pts.