rollback

[mits]

when i give rollback in my QA it gives an error saying (something like this)
- no begin transaction specified

In oracle it works fine why?

[MAK]

1. oracle and sql are not same.
2. need more details. give examples

[Stephen]

By default, each t-sql statement is a transaction i.e. it is committed if it completes successfully. Hence, when you issue the rollback command, there is no transaction to rollback.

If you want to be able to control whether a transaction is committed or rolled back, you will need to explicitly use the "BEGIN TRANSACTION" statement and then explicitly commit or rollback the transaction

[MAK]

In query analyzer click on -Tools-options- connection properties- check "set implicit_transactiops"

then do the following

create table xyz(id int)

insert into xyz select 1
insert into xyz select 2
insert into xyz select 3
insert into xyz select 4


rollback

[Stephen]

I said "By default".

From BOL...
The Microsoft OLE DB Provider for SQL Server and the SQL Server ODBC driver automatically set IMPLICIT_TRANSACTIONS to OFF when connecting. SET IMPLICIT_TRANSACTIONS defaults to OFF for connections from DB-Library applications.

When SET ANSI_DEFAULTS is ON, SET IMPLICIT_TRANSACTIONS is enabled.

The setting of SET IMPLICIT_TRANSACTIONS is set at execute or run time and not at parse time.

[MAK]

It is just a matter of style how you code it and whats your preference are.

I prefer to use 'Begin tran' if I want to rollback something or else I want the transaction to be committed immedietly.

The nature of many application I worked on has all the error checking and validation are done in the front end before it comes to the backend.

[mits]

Thank U all