Nested Stored Procs w/Transactions

I have some nested stored procedures where one sp calls another, etc. I nee
d
this wrapped in a transaction so that if an error occurs on any one sp
(either the calling sp or the one that is called) it will fail.
I'm continuall getting this error: Transaction count after EXECUTE
indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing.
Previous count = 2, current count = 3." So I've been playing around with
where to put the Begin Tran, Committ,
Rollback, etc.
Here's some pseudo code:
CREATE PROCEDURE [dbo].[spFILE_PROCESS]
AS
--Perform some queries, etc, then:
Exec spInsert_Customer
Exec spInsert_Trans
----
Where do I place Begin Tran/ committ, etc? I want both spInsert_Customer
and spInsert_Trans to be their own transaction as I call these sps by
themselves
elsewhere in my application.Hi,
have you tried this:
create procedure dbo.spFile_Process
as
set implicit_transactions off
--do something here without transaction
begin tran OuterTran
exec spInsert_Customer
exec spInsert_Trans
commit OuterTran
both nested procedures should have the same construction:
create procedure spInsert_Customer
as
set implicit_transactions off
begin tran TranA
...
commit TranA
create procedure spInsert_Trans
as
set implicit_transactions off
begin tran TranB
...
commit TranB
in case of error you should rollback the transation you are within
(decreasing @.@.trancount) and possible outer transactions. you have to
specify name of the transaction when rolling back, otherwise you will roll
back all transactions you're in.
HTH
Peter|||Alternately you could check for the existance of a transaction at the
begining of each procedure and only open a new transaction within the
procedure if one does not exist:
IF @.@.Trancount != 0
set @.Dotran = 0
...
If @.Dotran = 1
begin tran
...
-- on error
if @.dotran = 1
rollback transaction
return @.error
-- on success
if @.dotran = 1
comit tran
return 0
"Rogas69" wrote:

> Hi,
> have you tried this:
> create procedure dbo.spFile_Process
> as
> set implicit_transactions off
> --do something here without transaction
> begin tran OuterTran
> exec spInsert_Customer
> exec spInsert_Trans
> commit OuterTran
> both nested procedures should have the same construction:
> create procedure spInsert_Customer
> as
> set implicit_transactions off
> begin tran TranA
> ...
> commit TranA
> create procedure spInsert_Trans
> as
> set implicit_transactions off
> begin tran TranB
> ...
> commit TranB
> in case of error you should rollback the transation you are within
> (decreasing @.@.trancount) and possible outer transactions. you have to
> specify name of the transaction when rolling back, otherwise you will roll
> back all transactions you're in.
> HTH
> Peter
>
>|||Aren't you forgetting :
if @.@.Trancount = 0
set@.DoTran = 1
Anyway...
I've gone ahead and added the following whenever the committ tran, begin
tran, or rollback tran appear in my stored procs:
IF @.@.TRANCOUNT > 0 AND @.@.ERROR <> 0 BEGIN
ROLLBACK TRAN
RETURN
END
IF @.@.TRANCOUNT > 0 AND @.@.ERROR = 0 BEGIN
COMMIT TRAN
END
Now I'm longer getting the error message, but the Transaction is not being
rolled back either.
"Tony Sellars" wrote:
> Alternately you could check for the existance of a transaction at the
> begining of each procedure and only open a new transaction within the
> procedure if one does not exist:
> IF @.@.Trancount != 0
> set @.Dotran = 0
> ...
> If @.Dotran = 1
> begin tran
> ...
> -- on error
> if @.dotran = 1
> rollback transaction
> return @.error
> -- on success
> if @.dotran = 1
> comit tran
> return 0
>
> "Rogas69" wrote:
>

Need to understand writing to the Eventlog.

SQL2K
SP4
Howdy all. Im trying to have info written to the Eventlog when a deadlock
occurs, and growing frustrated trying to understand. My original
understanding was that I should RClick the server/ all tasks/ Manage
messages/ 1205 in the Error number/ Find/ Edit/ check Always write to
Eventlog/ OK/ OK.
I took those actions and purposely generated some deadlocks to no avail.
Then I found out what I really need to do is start SQL server by doing
"sqlservr -c -T1204" as instructed in
http://support.microsoft.com/default.aspx?scid=kb;en-us;169960.
Is this the only way? I really dont want to have to bounce all my production
boxes to accomplish this task.
TIA, ChrisR.You don't need to use -T1204 or run sqlsrvr -c -T1204 in order to log the
event in the app eventlog. The trace flag is to log a deadlock graph.
To log the deadlock in the app eventlog, it's sufficient to change the
logging behavior of error 1205 to 'Always write to eventlog'. This is the
same as executing the following:
EXEC sp_altermessage 1205, 'with_log', 'true'
But my experience is that after you have made that change, you need to
recycle the SQL Server instance. I have never had luck not recycling the SQL
instance, and I don't know whether this is a bug or a feature by design.
Linchi
"ChrisR" wrote:
> SQL2K
> SP4
> Howdy all. Im trying to have info written to the Eventlog when a deadlock
> occurs, and growing frustrated trying to understand. My original
> understanding was that I should RClick the server/ all tasks/ Manage
> messages/ 1205 in the Error number/ Find/ Edit/ check Always write to
> Eventlog/ OK/ OK.
> I took those actions and purposely generated some deadlocks to no avail.
> Then I found out what I really need to do is start SQL server by doing
> "sqlservr -c -T1204" as instructed in
> http://support.microsoft.com/default.aspx?scid=kb;en-us;169960.
> Is this the only way? I really dont want to have to bounce all my production
> boxes to accomplish this task.
> TIA, ChrisR.
>|||Perfect, thanks.
"Linchi Shea" wrote:
> You don't need to use -T1204 or run sqlsrvr -c -T1204 in order to log the
> event in the app eventlog. The trace flag is to log a deadlock graph.
> To log the deadlock in the app eventlog, it's sufficient to change the
> logging behavior of error 1205 to 'Always write to eventlog'. This is the
> same as executing the following:
> EXEC sp_altermessage 1205, 'with_log', 'true'
> But my experience is that after you have made that change, you need to
> recycle the SQL Server instance. I have never had luck not recycling the SQL
> instance, and I don't know whether this is a bug or a feature by design.
> Linchi
> "ChrisR" wrote:
> > SQL2K
> > SP4
> >
> > Howdy all. Im trying to have info written to the Eventlog when a deadlock
> > occurs, and growing frustrated trying to understand. My original
> > understanding was that I should RClick the server/ all tasks/ Manage
> > messages/ 1205 in the Error number/ Find/ Edit/ check Always write to
> > Eventlog/ OK/ OK.
> > I took those actions and purposely generated some deadlocks to no avail.
> > Then I found out what I really need to do is start SQL server by doing
> > "sqlservr -c -T1204" as instructed in
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;169960.
> >
> > Is this the only way? I really dont want to have to bounce all my production
> > boxes to accomplish this task.
> >
> > TIA, ChrisR.
> >