Re: SQL SERVER Rollback Problems

From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 10/09/04 

Date: Sat, 9 Oct 2004 09:55:12 -0500

If you have not SET XACT_ABORT ON, you will have an open transaction if you
abort the query. It is up to the client application to execute an explicit
ROLLBACK if an error is encountered (e.g. IF @@TRANCOUNT > 0 ROLLBACK).

You can automatically ROLLBACK by setting XACT_ABORT ON. Below is an
example.

CREATE TABLE MyTable
(
Col1 int
 CONSTRAINT PK_MyTable PRIMARY KEY
)
GO
CREATE PROC MyProc AS
INSERT INTO MyTable VALUES(1)
INSERT INTO MyTable VALUES(1)
GO

SET XACT_ABORT OFF
BEGIN TRAN
EXEC MyProc
GO
SELECT @@TRANCOUNT --open tran
ROLLBACK
GO

SET XACT_ABORT ON
BEGIN TRAN
EXEC MyProc
GO
SELECT @@TRANCOUNT --no open tran
GO 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"Sajan Rajagopal.M" <samy_mss@rediffmail.com> wrote in message 
news:1d9001c4adf8$6931c2e0$a301280a@phx.gbl...
>I have included a query (procedure) inside a begin and
> rollback. The Query got Hanged and i stopped the
> execution. Upon Stopping the query didnt get rollbacked
> instead it got executed and i had lost few data as there
> was a delete scripts inside my query.After that i executed
> the ROLLBACK alone and it got executed.
> Kindly tell me if this is a limitation in SQL server or is
> there any patch to be run in order to prevent this. This
> is a show stopper in my organisation as all the Back End
> Operations are totally dependant on SQL server and if this
> case keeps continuing i may have to take precautionary
> measures before executing even inside a BEGIN and ROLLBACK
> transaction.
> Thanking You
>
> Saminathan.S
> (Cognizant Technologies Solutions)

Relevant Pages

  • Re: Finally which ORM tool?
    ... manipulate the linq query IF you're executing it at that moment. ... simply because the declaration construction was with 'CHOPS'. ... implement IEnumerablebut had an Execute() method which gave back ...
    (microsoft.public.dotnet.languages.csharp)
  • RE: SQL stored procedure executing twice
    ... I wasn't aware that DLookupwould execute the "domain" more than once. ... caused the stored procedure to execute twice. ... Dim stDocName As String ... My pass-thru query properties ...
    (microsoft.public.access.modulesdaovba)
  • Re: Clarification on DBI module
    ... You get the same results for the second execute because the record buffer in the statment handle is not cleaned out because no execute took place. ... The sql query can have bind variables or they may not have. ... Ahhh...imagining that irresistible "new car" smell? ...
    (perl.dbi.users)
  • RE: Running action function from SELECT query
    ... Yes ADO can execute queries saved in an Access database. ... I do not know how ADO.NET works, but the developer tells me he is able to ... At any rate, I can call just a function from a query, like this: ...
    (microsoft.public.access.modulesdaovba)
  • Re: UPDATE query in Access 2003 raising error
    ... Runtime error 3066 Query must have at least one destination field. ... The SQL works fine if I use it in the QBF Design mode. ... Set qdfTemp = db.CreateQueryDef ... Elsewhere in the code I use the same technique to execute an SQL ...
    (microsoft.public.access.formscoding)
Loading