Thursday, February 16, 2012

Can a DROP TABLE statement be rolled back?

I'm interested in finding out if its possible to rollback a DROP TABLE
statement if it is inside a transaction that fails.
For some reason, I dont think it is but would like have someone confirm.
Thanks!
JohnnyNevermind my question. The answer is YES, it can be rolled back provided the
transaction is not comitted. My bad.
"Johnny" wrote:

> I'm interested in finding out if its possible to rollback a DROP TABLE
> statement if it is inside a transaction that fails.
> For some reason, I dont think it is but would like have someone confirm.
> Thanks!
> Johnny|||HI,Johnny,
U cannot rollback the drop table or delete command except if u execute
it if u use transaction number or savepoint.
As u execute the drop table a checkpoint occurs and the transaction is
commited by default.
U can aslo recover it if u have backup.
for my information on rollback
read books on line
hope this helps u
from
killer|||What you are saying is not true with Explicit transactions, which is the typ
e
of transaction I am referring to.
You can rollback a transaction (using BEGIN TRANSACTION & explicitly ended
with a COMMIT or ROLLBACK statement) as long as the transaction is not
committed. That is the whole point of transactions. Deletes, inserts, etc.
can all be rolled back provided the transaction has not been comitted.
Read the "BEGIN TRANSACTION (Transact-SQL) " topic in books online to learn
more.
"doller" wrote:

> HI,Johnny,
> U cannot rollback the drop table or delete command except if u execute
> it if u use transaction number or savepoint.
> As u execute the drop table a checkpoint occurs and the transaction is
> commited by default.
> U can aslo recover it if u have backup.
> for my information on rollback
> read books on line
> hope this helps u
> from
> killer
>|||You are right Johnny
The drop command is allowed inside a transaction only if the ddl in tran
option to sp_dboption is set to true
To set ddl in tran to true, enter:
sp_dboption database_name,"ddl in tran", true
you can found more information about that reviewing the next url:
*http://manuals.sybase.com/onlineboo...r />
iew/53001
regards
"Johnny" wrote:
[vbcol=seagreen]
> What you are saying is not true with Explicit transactions, which is the t
ype
> of transaction I am referring to.
> You can rollback a transaction (using BEGIN TRANSACTION & explicitly ended
> with a COMMIT or ROLLBACK statement) as long as the transaction is not
> committed. That is the whole point of transactions. Deletes, inserts, etc.
> can all be rolled back provided the transaction has not been comitted.
> Read the "BEGIN TRANSACTION (Transact-SQL) " topic in books online to lear
n
> more.
> "doller" wrote:
>|||There is no 'ddl in tran' database option in Microsoft SQL Server. Since
Johnny posted his question to a Microsoft SQL Server forum, chances are that
he is using MSSQL instead of Sybase.
DDL is always allowed within a transaction in Microsoft SQL Server. An
explicit or implicit transaction must be started in order to issue a COMMIT
or ROLLBACK. DDL can't be explicitly rolled back in autocommit mode.
Autocommit, explicit and implicit transactions are described in the Bools
Online <tsqlref.chm::/ts_ta-tz_2x2y.htm>.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Hernandez" <DanHernandez@.discussions.microsoft.com> wrote in message
news:DAD308E2-D898-4C40-B811-AAE8C8CA74F1@.microsoft.com...[vbcol=seagreen]
> You are right Johnny
> The drop command is allowed inside a transaction only if the ddl in tran
> option to sp_dboption is set to true
>
> To set ddl in tran to true, enter:
> sp_dboption database_name,"ddl in tran", true
> you can found more information about that reviewing the next url:
> *http://manuals.sybase.com/onlineboo.../>
tView/53001
>
> regards
>
> "Johnny" wrote:
>

No comments:

Post a Comment