Question on MTS and SQL Server

Hi
I have a stored procedure which gets executed from ASP code, the ASP in turn
uses MTS . the problem is, when the stored procedure encounters a rollback
statement, instead of just sending the error message and then rolling back
the transaction, the application aborts with a SQL Server error message and
the transaction gets rolled back. It does not send the user defined error
message
to the application. if i remove the rollback statement from the stored
procedure and then run the application, the proper error message is displaye
d
. Is there any ways i can skip the SQL Server error messages or any idea o
n
how MTS handles the rollbacks and transactions ?
Thanks
RodgerWhat are the error messages? Can you post the procedure?
ML
http://milambda.blogspot.com/|||These are sql server error messages. when the same stored proc is executed
from query analyser the proc works fine and even the rollback is ok.
8519 Current MSDTC transaction must be committed by remote client.
8525 Distributed transaction completed. Either enlist this session in a new
transaction or the NULL transaction.
her's the code which i execute and the error message which i get. Remember
i get the error message on my front end which is ASP.NET. The stored
procedure gets executed from the ASp code
Let me know if you have any questions
First Step :
If i execute the first piece of code, the table nfi_stage always has a
value, so the commit tran is sucessfull. Now if i change the code and put a
rollback tran (second piece of code ) instead of a commit tran
the application aborts giving the following error
Error : 6401
Cannot roll back %.*ls. No transaction or savepoint of that name was found.
First Code
begin distributed tran tran_outer
if (select count(*) from nfi_stage ) > 0
begin
delete nfi_stage
if @.@.trancount = 2
begin
commit tran tran_outer
--set @.retcode = 10087
return 10087
end
end
Change code with rollback
begin distributed tran tran_outer
if (select count(*) from nfi_stage ) > 0
begin
delete nfi_stage
if @.@.trancount = 2
begin
rollback tran tran_outer
--set @.retcode = 10087
return 10087
end
end
"ML" wrote:

> What are the error messages? Can you post the procedure?
>
> ML
> --
> http://milambda.blogspot.com/|||Nested transactions can be a pain. Look at the example in Books Online on ho
w
to "append" transactions to existing transactions:
http://msdn2.microsoft.com/en-us/library/ms188378.aspx
A few pointers:
1) check in each procedure where you intend to use explicit transactions,
whether an outer transaction has already begun;
2) if already in transaction create a save point (SAVE TRAN <save point
name> );
3) on errors rollback to the save point (ROLLBACK TRAN <save point name> );
4) only commit a transaction if it was started in the current procedure.
ML
http://milambda.blogspot.com/|||Hi
I tried using the code for transaction existence, and it gives me a error
message no
627 which means i cannot save a distributed transaction. the fact is i do
not have a distributed transaction started. maybe MTS treats the transactio
n
as a distributed transaction. any other alternate to saving a transaction
"ML" wrote:

> Nested transactions can be a pain. Look at the example in Books Online on
how
> to "append" transactions to existing transactions:
> http://msdn2.microsoft.com/en-us/library/ms188378.aspx
> A few pointers:
> 1) check in each procedure where you intend to use explicit transactions,
> whether an outer transaction has already begun;
> 2) if already in transaction create a save point (SAVE TRAN <save point
> name> );
> 3) on errors rollback to the save point (ROLLBACK TRAN <save point name> );
> 4) only commit a transaction if it was started in the current procedure.
>
> ML
> --
> http://milambda.blogspot.com/|||In your example you've started a distributed transaction explicitly.
Maybe you should explain a bit more why you think you need nested
transactions.
ML
http://milambda.blogspot.com/|||Here's the problem
I execute a proc from my VB code, the app is a web base application using
VB, IIS and MTS. when the proc is executed from app and if the first
sstatement in the proc is
if @.@.trancount > 0
save transaction firsttran
the @.@.trancount value = 1
and it gives a sql server error message 627
if i replace the save transaction with a commit transaction
it gives a sql error message 8519
so the problem here is the moment i execute a stored proc there is already a
transaction present which is a distributed transaction, started by MTS. so
the counter is always 1. now if i start my transactions and give a rollback
the entire proc fails and just comes out. its a bit complicated to explain
but can send the stored proc its a big proc.
thanks
"ML" wrote:

> In your example you've started a distributed transaction explicitly.
> Maybe you should explain a bit more why you think you need nested
> transactions.
>
> ML
> --
> http://milambda.blogspot.com/|||"Rodger" <Rodger@.discussions.microsoft.com> wrote in message
news:7D6214FF-DBFE-4EE3-B5A9-CBA834A2376B@.microsoft.com...
> Here's the problem
> I execute a proc from my VB code, the app is a web base application using
> VB, IIS and MTS. when the proc is executed from app and if the first
> sstatement in the proc is
> if @.@.trancount > 0
> save transaction firsttran
> the @.@.trancount value = 1
> and it gives a sql server error message 627
> if i replace the save transaction with a commit transaction
> it gives a sql error message 8519
> so the problem here is the moment i execute a stored proc there is already
> a
> transaction present which is a distributed transaction, started by MTS. so
> the counter is always 1. now if i start my transactions and give a
> rollback
> the entire proc fails and just comes out. its a bit complicated to
> explain
> but can send the stored proc its a big proc.
> thanks
>
You do realise that ROLLBACK is not nestable, and ROLLBACK will roll back
all pending nested transactions?
David|||Can you just turn off the MTS transaction and control the transaction in
your stored procedure or do you have a need to coordinate two different
components in the same distributed transaction with MTS.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23812F0LZGHA.4144@.TK2MSFTNGP04.phx.gbl...
> "Rodger" <Rodger@.discussions.microsoft.com> wrote in message
> news:7D6214FF-DBFE-4EE3-B5A9-CBA834A2376B@.microsoft.com...
> You do realise that ROLLBACK is not nestable, and ROLLBACK will roll back
> all pending nested transactions?
> David
>