SQL Server transactions TSQL question.

Fdemid

25 pts.

Tags:

SQL Server administration

T/SQL

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 Last updated: August 25, 2009 2:53 PM

Answer Wiki

Last updated: August 20, 2009 8:27 PM GMT

Denny Cherry68,390 pts.
History
Contributors
- Ordered by most recent
- Denny Cherry68,390 pts.

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 3 Replies

Fdemid Aug 20, 2009 9:42 PM GMT

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.

25 pointsBadges:

report
carlosdl Aug 25, 2009 2:30 PM GMT

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

84,495 pointsBadges:

report
Fdemid Aug 25, 2009 2:53 PM GMT
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
25 pointsBadges:
report