Hi All,
Is it required to provide BEGIN TRANSACTION...END TRANSACTION block inside
stored procedure, if a client has already started a transaction? The reason,
I am asking this question is that, though an error occured in a stored
procedure which was called within a transaction (on the client side), the
rest of the statements in the stored procedure were executed, which was not
desired.
I am have opened a connection and started a transaction in vb.net and called
a stored procedure. The stored procedure calls several other stored
procedures and adds/updates records in linked tables. It so happened that,
one temporary table did not exist and that particular statement failed when
I
tried to select records from the temporary table. But the following
statements were executed. Why was the transacton not being honoured? Ideally
,
when a statement failed, the transaction should be aborted; this is what I
understand.
Any suggestions?
Thanks
kdThen you have to check for error on your own with the variable @.@.Error.
BEGIN TRANSACTION
<Dosomething>
IF @.@.Error = 0
COMMIT TRANSACTION
ELSE ROLLBACK TRANSACTION
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"kd" <kd@.discussions.microsoft.com> schrieb im Newsbeitrag
news:0056D0F3-9A2D-46D5-9667-C01FD7D671B2@.microsoft.com...
> Hi All,
> Is it required to provide BEGIN TRANSACTION...END TRANSACTION block inside
> stored procedure, if a client has already started a transaction? The
> reason,
> I am asking this question is that, though an error occured in a stored
> procedure which was called within a transaction (on the client side), the
> rest of the statements in the stored procedure were executed, which was
> not
> desired.
> I am have opened a connection and started a transaction in vb.net and
> called
> a stored procedure. The stored procedure calls several other stored
> procedures and adds/updates records in linked tables. It so happened that,
> one temporary table did not exist and that particular statement failed
> when I
> tried to select records from the temporary table. But the following
> statements were executed. Why was the transacton not being honoured?
> Ideally,
> when a statement failed, the transaction should be aborted; this is what I
> understand.
> Any suggestions?
> Thanks
> kd
>|||The piece you're missing, is that not all errors cause a Stored Proc to
terminate and return... Most, in fact, just set the value of @.@.Error and
continue processing the next statement. If you want the stored Proc t ostop
and return upon encountering an error from a specific statement, you have to
code that yourself, for each statement, by testing the value of @.@.error
immediately after teh statement executes...
Declare @.Err Integer -- At Beginning of SP
/* ******
Other stuff
*********/
<Statement>
Set @.Err = @.@.Error
If @.Err <> 0 Begin
If @.@.TranCount > 0 Rollback
Raiserror('This is message', 16,1)
Return (@.Err)
End
<rest of Stored Proc>
"kd" wrote:
> Hi All,
> Is it required to provide BEGIN TRANSACTION...END TRANSACTION block inside
> stored procedure, if a client has already started a transaction? The reaso
n,
> I am asking this question is that, though an error occured in a stored
> procedure which was called within a transaction (on the client side), the
> rest of the statements in the stored procedure were executed, which was no
t
> desired.
> I am have opened a connection and started a transaction in vb.net and call
ed
> a stored procedure. The stored procedure calls several other stored
> procedures and adds/updates records in linked tables. It so happened that,
> one temporary table did not exist and that particular statement failed whe
n I
> tried to select records from the temporary table. But the following
> statements were executed. Why was the transacton not being honoured? Ideal
ly,
> when a statement failed, the transaction should be aborted; this is what I
> understand.
> Any suggestions?
> Thanks
> kd
>|||Hi,
I don't have BEGIN TRANSACTION..END TRANSACTION block in the stored
procedure. I have begun a transcation in the client side in vb.net.
try
connection.Open()
trans = connection.BeginTransaction()
... 'called stored procedure
trans.Commit()
command.Connection.Close()
Catch ex As Exception
trans.Rollback()
End Try
My first question is this. Does the stored procedure and all the other
stored procedures, UDFs, etc called by the stored procedure be part of the
transaction started on the client side?
My second question is, what are the errors that cause a stored procedure to
terminate and return? I could may be dummy code for the errors and check
whether the transaction started at the client is honoured at the server end.
Regards,
kd
"CBretana" wrote:
> The piece you're missing, is that not all errors cause a Stored Proc to
> terminate and return... Most, in fact, just set the value of @.@.Error and
> continue processing the next statement. If you want the stored Proc t ost
op
> and return upon encountering an error from a specific statement, you have
to
> code that yourself, for each statement, by testing the value of @.@.error
> immediately after teh statement executes...
> Declare @.Err Integer -- At Beginning of SP
> /* ******
> Other stuff
> *********/
> <Statement>
> Set @.Err = @.@.Error
> If @.Err <> 0 Begin
> If @.@.TranCount > 0 Rollback
> Raiserror('This is message', 16,1)
> Return (@.Err)
> End
> <rest of Stored Proc>
> "kd" wrote:
>
