SQL Server transactions TSQL question.

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

Answer Wiki

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

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Fdemid
    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
    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
    69,605 pointsBadges:
    report
  • Fdemid
    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

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following