SQL Transactions

11 Nov 2005 6:34 PM

Jay

I have the stored procedure below that I am using from asp.net. To test
things out I changed a table column name so it would fail. I got the error
below. Furthermore, it hung my database so I had to stop SQL Server and
restart in order to login to the website. Of course I expected it to fail
but I do not want the error message to be displayed on my web page and there
is the problem of SQL Server hanging.

Suggestions would be much appreciated.

Jay

Error Msg
----------

Invalid column name 'SponsorName'. Transaction count after EXECUTE indicates
that a COMMIT or ROLLBACK TRANSACTION

statement is missing. Previous count = 0, current count = 1

Stored Procedure
------------------

CREATE PROCEDURE Foo

As

BEGIN TRAN

-- update statement here

IF @@ERROR <> 0
   BEGIN
ROLLBACK TRAN
return 10
   END

-- insert statement here

IF @@ERROR <> 0
   BEGIN
ROLLBACK TRAN
return 11
   END

-- update statement here

IF @@ERROR <> 0
   BEGIN
ROLLBACK TRAN
return 12
   END

--delete statement here

IF @@ERROR <> 0
   BEGIN
ROLLBACK TRAN
return 13
   END

COMMIT TRAN

return 0
GO

11 Nov 2005 6:49 PM

Andrew J. Kelly

Jay,

If the error is severe enough (and this sounds like it was) the code in that
batch actually stops executing at that point. So the Rollbacks don't
happen. You need to either use SET XACT_ABORT ON or test at the client and
issue a ROLLBACK if needed. And never issue a ROLLBACK or COMMIT without
testing to see that @@TRANCOUNT > 0 or that will error. Chances are this
did not "hang" SQL Server. More than likely the open transaction prevented
others from completing and things just piled up.

--
Andrew J. Kelly SQL MVP

Show quote

"Jay" <some***@anywhere.com> wrote in message
news:ubf5H6u5FHA.1184@TK2MSFTNGP12.phx.gbl...
>I have the stored procedure below that I am using from asp.net. To test
> things out I changed a table column name so it would fail. I got the
> error
> below. Furthermore, it hung my database so I had to stop SQL Server and
> restart in order to login to the website. Of course I expected it to fail
> but I do not want the error message to be displayed on my web page and
> there
> is the problem of SQL Server hanging.
>
> Suggestions would be much appreciated.
>
> Jay
>
>
> Error Msg
> ----------
>
> Invalid column name 'SponsorName'. Transaction count after EXECUTE
> indicates
> that a COMMIT or ROLLBACK TRANSACTION
>
> statement is missing. Previous count = 0, current count = 1
>
> Stored Procedure
> ------------------
>
> CREATE PROCEDURE Foo
>
> As
>
> BEGIN TRAN
>
> -- update statement here
>
> IF @@ERROR <> 0
>   BEGIN
> ROLLBACK TRAN
> return 10
>   END
>
> -- insert statement here
>
> IF @@ERROR <> 0
>   BEGIN
> ROLLBACK TRAN
> return 11
>   END
>
> -- update statement here
>
> IF @@ERROR <> 0
>   BEGIN
> ROLLBACK TRAN
> return 12
>   END
>
> --delete statement here
>
> IF @@ERROR <> 0
>   BEGIN
> ROLLBACK TRAN
> return 13
>   END
>
> COMMIT TRAN
>
> return 0
> GO
>
>
>
>
>

11 Nov 2005 7:23 PM

Jay

Thanks very much.

Is it possible to give me a simple example.
Would it be better to use the transactions in ado.net?
Bottom line is that I do not want an uncontrolled error msg displayed. I
want to put up my own msg.

By the way the transaction did rollback or no statements were committed. I
caused the error to happen mid-point after some changes were made. They
were not made in my little test.

Jay

Show quote

"Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message
news:#fQNnCv5FHA.2192@TK2MSFTNGP14.phx.gbl...
> Jay,
>
> If the error is severe enough (and this sounds like it was) the code in
that
> batch actually stops executing at that point. So the Rollbacks don't
> happen. You need to either use SET XACT_ABORT ON or test at the client
and
> issue a ROLLBACK if needed. And never issue a ROLLBACK or COMMIT without
> testing to see that @@TRANCOUNT > 0 or that will error. Chances are this
> did not "hang" SQL Server. More than likely the open transaction
prevented
> others from completing and things just piled up.
>
> --
> Andrew J. Kelly SQL MVP
>
>
> "Jay" <some***@anywhere.com> wrote in message
> news:ubf5H6u5FHA.1184@TK2MSFTNGP12.phx.gbl...
> >I have the stored procedure below that I am using from asp.net. To test
> > things out I changed a table column name so it would fail. I got the
> > error
> > below. Furthermore, it hung my database so I had to stop SQL Server and
> > restart in order to login to the website. Of course I expected it to
fail
> > but I do not want the error message to be displayed on my web page and
> > there
> > is the problem of SQL Server hanging.
> >
> > Suggestions would be much appreciated.
> >
> > Jay
> >
> >
> > Error Msg
> > ----------
> >
> > Invalid column name 'SponsorName'. Transaction count after EXECUTE
> > indicates
> > that a COMMIT or ROLLBACK TRANSACTION
> >
> > statement is missing. Previous count = 0, current count = 1
> >
> > Stored Procedure
> > ------------------
> >
> > CREATE PROCEDURE Foo
> >
> > As
> >
> > BEGIN TRAN
> >
> > -- update statement here
> >
> > IF @@ERROR <> 0
> >   BEGIN
> > ROLLBACK TRAN
> > return 10
> >   END
> >
> > -- insert statement here
> >
> > IF @@ERROR <> 0
> >   BEGIN
> > ROLLBACK TRAN
> > return 11
> >   END
> >
> > -- update statement here
> >
> > IF @@ERROR <> 0
> >   BEGIN
> > ROLLBACK TRAN
> > return 12
> >   END
> >
> > --delete statement here
> >
> > IF @@ERROR <> 0
> >   BEGIN
> > ROLLBACK TRAN
> > return 13
> >   END
> >
> > COMMIT TRAN
> >
> > return 0
> > GO
> >
> >
> >
> >
> >
>
>

11 Nov 2005 10:27 PM

ML

Erland Sommarskog wrote two in-depth articles on the subject of
error-handling. They're available here:
http://www.sommarskog.se/

Plus several other very useful articles.

ML