I have 2 Stored Procedures, "Parent" and "Child". The Parent SP calls the
Child SP, but also the Child may be called directly.
If the Child returns an error (which occurs whenever it is passed a value of
2), I want all updates to be rolled out.
I have a couple of working version of these 2 SPs, but what I am looking for
is "What is a the best (or a good way) of doing this?"
This is what I understand about transactions:
1) Performing a BEGIN TRAN increments @.@.TRANCOUNT
2) Performing an END TtRAN decrements @.@.TRANCOUNT
3) A ROLLBACK TRAN returns @.@.TRANCOUNT to 0
4) If @.@.TRANCOUNT is = X in Parent when CHild is called, it must be = X
immediately after returning from the CHILD call.
I've played around with SAVE POINTs within a transaction, but I do not have
a sample here.
I am looking for the simpilist, most intuative sane and hopefully common
approach to take here, I'm not sure I like what I've done-It's seems counter
intuitive.
Please alter my example as you would code it. Many thanks.
--****************EXAMPLE 1
--Setup: Create a table
CREATE TABLE [dbo].[Table1] (
[col1] [int] NULL ,
[col2] [int] NULL
) ON [PRIMARY]
GO
--Throw a rec into it
INSERT INTO table1 (col1,col2) values (1,1)
--Create the Parent SP
CREATE procedure dbo.parent
as
begin
declare @.res int
begin transaction
update table1 set col1 = 1
update table1 set col1 = 2
exec @.res = child 2
if @.Res = -1
begin
rollback transaction
return @.res
end
commit transaction
return 0
end
--CREATE CHILD SP -
CREATE procedure Child
@.col2 int
as
begin
begin transaction
update table1 set col1 = @.col2
update table1 set col2 = @.col2
if @.col2 = 2 --DONT PASS 2!! It's an error!
begin
IF @.@.Trancount = 1
Rollback TRANSACTION
else
Commit transaction --needs to be the same value as when we entered this
SP
PRINT 'TRANCOUNT IN CHILD ' + CAST(@.@.TRANCOUNT AS VARCHAR(10))
return -1
end
commit transaction
return 0
end
----
--
--****************EXAMPLE 2
ALTER procedure dbo.parent
as
begin
declare @.res int
begin transaction
update table1 set col1 = 1
update table1 set col1 = 2
exec @.res = child 2
if @.Res = -1
begin
rollback transaction
return @.res
end
commit transaction
return 0
end
ALTER procedure Child
@.col2 int
as
begin
begin transaction
update table1 set col1 = @.col2
update table1 set col2 = @.col2
if @.col2 = 2 --DONT PASS 2!! It's an error!
begin
if @.@.trancount > 1 --outer SP started the transaction
commit transaction --leave it to the parent to rollback outer trans
else
rollback transaction
return -1
end
commit transaction
return 0
endThere's a few things you should know:
First: always check for errors after each DML statement or SP call within a
transaction, because it is possible for an early DML statement to fail, and
later ones to pass which causes an insidious data consistency bug that is
extremely difficult to find. Here's what I do:
DECLARE @._ERROR INT
BEGIN TRANSACTION
UPDATE t1 set col1 = @.col1
SELECT @._ERROR = @.@.ERROR
IF @._ERROR != 0 GOTO ERROR
UPDATE t2 set col2 = @.col2
SELECT @._ERROR = @.@.ERROR
IF @._ERROR != 0 GOTO ERROR
COMMIT TRANSACTION
RETURN 0
ERROR:
IF @.@.TRANCOUNT > 0 ROLLBACK TRANSACTION
RETURN @._ERROR
This approach should make your inquiry moot, since @.@.ERROR is set on exit
from a procedure if @.@.TRANCOUNT is less than what it was upon entry.
I only use save points if I want to roll back only part of a transaction,
here's what I do:
DECLARE @._TRANCOUNT INT SET @._TRANCOUNT = @.@.TRANCOUNT
DECLARE @._ERROR INT
IF @._TRANCOUNT = 0
BEGIN TRANSACTION savePoint
ELSE
SAVE TRANSACTION savePoint
UPDATE t1 set col1 = @.col1
SELECT @._ERROR = @.@.ERROR
IF @._ERROR != 0 GOTO ERROR
UPDATE t2 set col2 = @.col2
SELECT @._ERROR = @.@.ERROR
IF @._ERROR != 0 GOTO ERROR
IF @._TRANCOUNT = 0
COMMIT TRANSACTION savePoint
RETURN 0
ERROR:
IF @.@.TRANCOUNT > @._TRANCOUNT
ROLLBACK TRANSACTION savePoint
RETURN @._ERROR
"Chad" wrote:
> I have 2 Stored Procedures, "Parent" and "Child". The Parent SP calls the
> Child SP, but also the Child may be called directly.
> If the Child returns an error (which occurs whenever it is passed a value
of
> 2), I want all updates to be rolled out.
> I have a couple of working version of these 2 SPs, but what I am looking f
or
> is "What is a the best (or a good way) of doing this?"
> This is what I understand about transactions:
> 1) Performing a BEGIN TRAN increments @.@.TRANCOUNT
> 2) Performing an END TtRAN decrements @.@.TRANCOUNT
> 3) A ROLLBACK TRAN returns @.@.TRANCOUNT to 0
> 4) If @.@.TRANCOUNT is = X in Parent when CHild is called, it must be = X
> immediately after returning from the CHILD call.
> I've played around with SAVE POINTs within a transaction, but I do not hav
e
> a sample here.
> I am looking for the simpilist, most intuative sane and hopefully common
> approach to take here, I'm not sure I like what I've done-It's seems count
er
> intuitive.
> Please alter my example as you would code it. Many thanks.
> --****************EXAMPLE 1
> --Setup: Create a table
> CREATE TABLE [dbo].[Table1] (
> [col1] [int] NULL ,
> [col2] [int] NULL
> ) ON [PRIMARY]
> GO
> --Throw a rec into it
> INSERT INTO table1 (col1,col2) values (1,1)
> --Create the Parent SP
> CREATE procedure dbo.parent
> as
> begin
> declare @.res int
> begin transaction
> update table1 set col1 = 1
> update table1 set col1 = 2
> exec @.res = child 2
> if @.Res = -1
> begin
> rollback transaction
> return @.res
> end
> commit transaction
> return 0
> end
> --CREATE CHILD SP -
> CREATE procedure Child
> @.col2 int
> as
> begin
> begin transaction
> update table1 set col1 = @.col2
> update table1 set col2 = @.col2
> if @.col2 = 2 --DONT PASS 2!! It's an error!
> begin
> IF @.@.Trancount = 1
> Rollback TRANSACTION
> else
> Commit transaction --needs to be the same value as when we entered this
> SP
> PRINT 'TRANCOUNT IN CHILD ' + CAST(@.@.TRANCOUNT AS VARCHAR(10))
> return -1
> end
>
> commit transaction
> return 0
> end
> ----
--
> --****************EXAMPLE 2
> ALTER procedure dbo.parent
> as
> begin
> declare @.res int
> begin transaction
> update table1 set col1 = 1
> update table1 set col1 = 2
> exec @.res = child 2
> if @.Res = -1
> begin
> rollback transaction
> return @.res
> end
> commit transaction
> return 0
> end
>
> ALTER procedure Child
> @.col2 int
> as
> begin
> begin transaction
> update table1 set col1 = @.col2
> update table1 set col2 = @.col2
> if @.col2 = 2 --DONT PASS 2!! It's an error!
> begin
> if @.@.trancount > 1 --outer SP started the transaction
> commit transaction --leave it to the parent to rollback outer tra
ns
> else
> rollback transaction
> return -1
> end
>
> commit transaction
> return 0
> end
>
>|||Thank you for the response. I don't fully understand.
In my example, I wanted to be able to call a ChildSP directly, or call a
ParentSP which calls the ChildSP, and if an error occurs in the child,
everything gets rolled back. Your exaple only included one stored proc, so I
was a little unclear.
I tried to create a 2 SP example using your style. In your error handler,
you check to see if @.@.TranCount > 0. If so, you know that there was an error
above. Howver, if we take this approach in the ChildSP, performing a
Rollback would cause the @.@.TranCount to be set to zero, and when you return
to the ParentSP, we find that @.@.TranCount is now 0, but it was 1 perform we
called ChildSP, and so we ge the error:
Server: Msg 50000, Level 16, State 1, Procedure ChildSP, Line 10
an error was raised
Server: Msg 266, Level 16, State 2, Procedure ChildSP, Line 26
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
TRANSACTION statement is missing. Previous count = 1, current count = 0.
Try running the code below.
I would be very much indebted if you could take the 2 SP example and modify
it to a approach that works and is sane.
CREATE TABLE [dbo].[Table1] (
[col1] [int] NULL ,
[col2] [int] NULL
) ON [PRIMARY]
CREATE procedure ParentSP
as
begin
DECLARE @._ERROR INT
BEGIN TRANSACTION
exec @._ERROR = ChildSP 1
SELECT @._ERROR = @.@.ERROR
IF @._ERROR != 0 GOTO ERROR
COMMIT TRANSACTION
RETURN 0
ERROR:
IF @.@.TRANCOUNT > 0 ROLLBACK TRANSACTION
RETURN @._ERROR
end
CREATE procedure ChildSP
(@.RaiseError bit)
as
begin
DECLARE @._ERROR INT
BEGIN TRANSACTION
if (@.RaiseError = 1)
RAISERROR ('an error was raised', 16, 1)
ELSE
UPDATE table1 set col1 = 1
SELECT @._ERROR = @.@.ERROR
IF @._ERROR != 0 GOTO ERROR
UPDATE table1 set col2 = 2
SELECT @._ERROR = @.@.ERROR
IF @._ERROR != 0 GOTO ERROR
COMMIT TRANSACTION
RETURN 0
ERROR:
IF @.@.TRANCOUNT > 0 ROLLBACK TRANSACTION
RETURN @._ERROR
end
"Brian Selzer" <BrianSelzer@.discussions.microsoft.com> wrote in message
news:97330484-71EA-4142-9B25-DAE902EA8836@.microsoft.com...
> There's a few things you should know:
> First: always check for errors after each DML statement or SP call within
> a
> transaction, because it is possible for an early DML statement to fail,
> and
> later ones to pass which causes an insidious data consistency bug that is
> extremely difficult to find. Here's what I do:
> DECLARE @._ERROR INT
> BEGIN TRANSACTION
> UPDATE t1 set col1 = @.col1
> SELECT @._ERROR = @.@.ERROR
> IF @._ERROR != 0 GOTO ERROR
> UPDATE t2 set col2 = @.col2
> SELECT @._ERROR = @.@.ERROR
> IF @._ERROR != 0 GOTO ERROR
> COMMIT TRANSACTION
> RETURN 0
> ERROR:
> IF @.@.TRANCOUNT > 0 ROLLBACK TRANSACTION
> RETURN @._ERROR
> This approach should make your inquiry moot, since @.@.ERROR is set on exit
> from a procedure if @.@.TRANCOUNT is less than what it was upon entry.
>
> I only use save points if I want to roll back only part of a transaction,
> here's what I do:
> DECLARE @._TRANCOUNT INT SET @._TRANCOUNT = @.@.TRANCOUNT
> DECLARE @._ERROR INT
> IF @._TRANCOUNT = 0
> BEGIN TRANSACTION savePoint
> ELSE
> SAVE TRANSACTION savePoint
> UPDATE t1 set col1 = @.col1
> SELECT @._ERROR = @.@.ERROR
> IF @._ERROR != 0 GOTO ERROR
> UPDATE t2 set col2 = @.col2
> SELECT @._ERROR = @.@.ERROR
> IF @._ERROR != 0 GOTO ERROR
> IF @._TRANCOUNT = 0
> COMMIT TRANSACTION savePoint
> RETURN 0
> ERROR:
> IF @.@.TRANCOUNT > @._TRANCOUNT
> ROLLBACK TRANSACTION savePoint
> RETURN @._ERROR
>
>
> "Chad" wrote:
>|||The code I provided will work when called directly or from another stored
procedure. Use it as a template for both the parent and the child
procedure--in fact use this mechanism in all of your procedures.
You should declare an additional variable, @.RC, in the parent procedure to
receive the return code from the stored procedure call. Otherwise you will
lose the error code that originally caused the failure, for example:
DECLARE @.RC INT, @._ERROR INT
EXEC @.RC = ChildProc
SET @._ERROR = @.@.ERROR
IF @.RC != 0 OR @._ERROR != 0 GOTO ERROR
The key to this approach is that any error, regardless of the reason
(Constraint violation, out of memory, Deadlock victim, etc.) is detected and
handled immediately after it occurs, and the error handling code rolls back
the transaction. When an error occurs in the child procedure, it rolls back
any pending transaction and returns the error code to the caller. The paren
t
procedure detects that an error occurred by examining the return code, and
transferrs control to its own error handler. Since the transaction had
already been rolled back in the child procedure, @.@.TRANCOUNT is zero and thu
s
a rollback in the parent's error handler would cause an additional error.
The condition IF @.@.TRANCOUNT > 0 prevents this. (It also prevents an
additional error in the event the procedure is chosen as a deadlock victim.)
I often extend this mechanism to detect concurrency problems. For example:
DECLARE @._ERROR INT, @._ROWCOUNT INT
BEGIN TRANSACTION
UPDATE t1 SET col1 = @.col1 where key1 = @.Key and ver1 = @.version
SELECT @._ERROR = @.@.ERROR, @._ROWCOUNT = @.@.ROWCOUNT
IF @._ERROR != 0 OR @._ROWCOUNT = 0 GOTO ERROR
COMMIT TRANSACTION
RETURN 0
ERROR:
IF @.@.TRANCOUNT > 0 ROLLBACK TRANSACTION
IF @._ERROR = 0 AND @._ROWCOUNT = 0
RETURN -1 -- indicate that a record was changed by another
user
ELSE
RETURN @._ERROR
END
ver1 is a rowversion (timestamp) column, which is changed any time a record
is changed. If another user changes the record after the time it was read,
then ver1 will be different than @.version, the update statement will not
affect any rows, and consequently @.@.ROWCOUNT will be zero.
"Chad" wrote:
> Thank you for the response. I don't fully understand.
> In my example, I wanted to be able to call a ChildSP directly, or call a
> ParentSP which calls the ChildSP, and if an error occurs in the child,
> everything gets rolled back. Your exaple only included one stored proc, so
I
> was a little unclear.
> I tried to create a 2 SP example using your style. In your error handler,
> you check to see if @.@.TranCount > 0. If so, you know that there was an err
or
> above. Howver, if we take this approach in the ChildSP, performing a
> Rollback would cause the @.@.TranCount to be set to zero, and when you retur
n
> to the ParentSP, we find that @.@.TranCount is now 0, but it was 1 perform w
e
> called ChildSP, and so we ge the error:
> Server: Msg 50000, Level 16, State 1, Procedure ChildSP, Line 10
> an error was raised
> Server: Msg 266, Level 16, State 2, Procedure ChildSP, Line 26
> Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
> TRANSACTION statement is missing. Previous count = 1, current count = 0.
> Try running the code below.
> I would be very much indebted if you could take the 2 SP example and modif
y
> it to a approach that works and is sane.
>
> CREATE TABLE [dbo].[Table1] (
> [col1] [int] NULL ,
> [col2] [int] NULL
> ) ON [PRIMARY]
>
> CREATE procedure ParentSP
> as
> begin
> DECLARE @._ERROR INT
> BEGIN TRANSACTION
> exec @._ERROR = ChildSP 1
> SELECT @._ERROR = @.@.ERROR
> IF @._ERROR != 0 GOTO ERROR
> COMMIT TRANSACTION
> RETURN 0
> ERROR:
> IF @.@.TRANCOUNT > 0 ROLLBACK TRANSACTION
> RETURN @._ERROR
> end
>
> CREATE procedure ChildSP
> (@.RaiseError bit)
> as
> begin
> DECLARE @._ERROR INT
> BEGIN TRANSACTION
> if (@.RaiseError = 1)
> RAISERROR ('an error was raised', 16, 1)
> ELSE
> UPDATE table1 set col1 = 1
>
> SELECT @._ERROR = @.@.ERROR
> IF @._ERROR != 0 GOTO ERROR
> UPDATE table1 set col2 = 2
> SELECT @._ERROR = @.@.ERROR
> IF @._ERROR != 0 GOTO ERROR
> COMMIT TRANSACTION
> RETURN 0
> ERROR:
> IF @.@.TRANCOUNT > 0 ROLLBACK TRANSACTION
> RETURN @._ERROR
> end
>
> "Brian Selzer" <BrianSelzer@.discussions.microsoft.com> wrote in message
> news:97330484-71EA-4142-9B25-DAE902EA8836@.microsoft.com...
>
>|||Brian,
Thank you again for your feedback. I appreciate the tip, in particular on
handling concurrency problems using RowVersion, and I believe understand the
thrust of your points.
However, I would like to place a spot light on a point I originally made
that I feel may not have been addressed:
*** If @.@.TRANCOUNT is = X in ParentSP when ChildSP is called, it must be = X
immediately after returning from the CHILD call. , else an error results***
If feel that this is the situation in the example you proposed.
If the ParentSP BEGINs a TRANSACTON (Transaction count is now 1), then calls
ChildSP, which does a ROLLBACK within Child, TranCount will be 0 when
control is returned to the Parent. Since TranCount was 1 just prior to
calling the Child and it is zero immeditely after returning, this result in
an ERROR:
> Server: Msg 50000, Level 16, State 1, Procedure ChildSP, Line 10
> an error was raised
> Server: Msg 266, Level 16, State 2, Procedure ChildSP, Line 26
> Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
> TRANSACTION statement is missing. Previous count = 1, current count = 0.
This is the part that I am missing. It seems to me that the Child cannot do
the rollback if the Parent already began a Transaction.
I hope I am not trying your patience. I would really like to get this point
down.
Thanks,
Chad
"Brian Selzer" <BrianSelzer@.discussions.microsoft.com> wrote in message
news:9E615D99-D61A-4AF3-BEFB-09C2C12281D3@.microsoft.com...
> The code I provided will work when called directly or from another stored
> procedure. Use it as a template for both the parent and the child
> procedure--in fact use this mechanism in all of your procedures.
> You should declare an additional variable, @.RC, in the parent procedure to
> receive the return code from the stored procedure call. Otherwise you will
> lose the error code that originally caused the failure, for example:
> DECLARE @.RC INT, @._ERROR INT
> EXEC @.RC = ChildProc
> SET @._ERROR = @.@.ERROR
> IF @.RC != 0 OR @._ERROR != 0 GOTO ERROR
> The key to this approach is that any error, regardless of the reason
> (Constraint violation, out of memory, Deadlock victim, etc.) is detected
> and
> handled immediately after it occurs, and the error handling code rolls
> back
> the transaction. When an error occurs in the child procedure, it rolls
> back
> any pending transaction and returns the error code to the caller. The
> parent
> procedure detects that an error occurred by examining the return code, and
> transferrs control to its own error handler. Since the transaction had
> already been rolled back in the child procedure, @.@.TRANCOUNT is zero and
> thus
> a rollback in the parent's error handler would cause an additional error.
> The condition IF @.@.TRANCOUNT > 0 prevents this. (It also prevents an
> additional error in the event the procedure is chosen as a deadlock
> victim.)
> I often extend this mechanism to detect concurrency problems. For
> example:
> DECLARE @._ERROR INT, @._ROWCOUNT INT
> BEGIN TRANSACTION
> UPDATE t1 SET col1 = @.col1 where key1 = @.Key and ver1 = @.version
> SELECT @._ERROR = @.@.ERROR, @._ROWCOUNT = @.@.ROWCOUNT
> IF @._ERROR != 0 OR @._ROWCOUNT = 0 GOTO ERROR
> COMMIT TRANSACTION
> RETURN 0
> ERROR:
> IF @.@.TRANCOUNT > 0 ROLLBACK TRANSACTION
> IF @._ERROR = 0 AND @._ROWCOUNT = 0
> RETURN -1 -- indicate that a record was changed by another
> user
> ELSE
> RETURN @._ERROR
> END
> ver1 is a rowversion (timestamp) column, which is changed any time a
> record
> is changed. If another user changes the record after the time it was
> read,
> then ver1 will be different than @.version, the update statement will not
> affect any rows, and consequently @.@.ROWCOUNT will be zero.
>
> "Chad" wrote:
>|||Brian & Chad
I believe I'm having the same issue as Chad with nested stored procedures
inside a transaction.
What I'd like to do is begin a transaction in an outer SP. If all goes
well, the transaction will be committed in the outer stored procedure - no
problem there. However, if an error or other unexpected condition is
encountered, I would like to rollback as close to the error as possible (in
the statement following detection, if possible).
Problem is this might involve a transaction begun in an outer SP being
rollbed back in an inner SP. Due to the fact that on entry to inner SP
@.@.Trancount == 1 but on exit @.@.Trancount == 0, a new error, Error 266, gets
generated.
Following illustrates the problem:
-- INNER SP
create procedure InnerSP as begin
declare @.ErrNo int
/* Do something here */
select @.ErrNo = @.@.ERROR
if @.ErrNo <> 0 begin
if @.@.TRANCOUNT > 0 rollback transaction
return @.ErrNo -- on return new error (266) generated
end
return 0
end
-- OUTER SP
create procedure OuterSP as begin
declare @.ErrNo int
Begin Transaction
exec @.ErrNo = InnerSP
-- if InnerSP failed, @.@.ERROR = 266 here
if @.ErrNo <> 0 begin
if @.@.TRANCOUNT > 0 rollback transaction
return @.ErrNo
end
commit transaction
return 0
end
I could hold off on performing the rollback until OuterSP examines the
return value from InnerSP, but this is not ideal:
(1) Immediately after the error I may want to do some logging or
other fixup. If these are done before the rollback, they will be wiped out
by the rollback.
(2) In code subsequent to ther error there is always the possibility
that execution will be terminated due to a severe error, preventing the
enclosing SP from ever executing the rollback. While the lack of a
subsequent COMMIT will ultimately lead to the transaction being rolled back,
I would have no control over when the rollback occurs.
I can avoid this problem via the kludge of a new "Begin Transaction"
statement just before returning the error code from InnerSP to OuterSP. Is
there a cleaner way to resolve this problem (beyond waiting for SQL Server
2005 try...catch blocks)?
Ron Strong
"Chad" <chad.dokmanovich@.unisys.com> wrote in message
news:cv7rc2$h6a$1@.trsvr.tr.unisys.com...
> Brian,
> Thank you again for your feedback. I appreciate the tip, in particular on
> handling concurrency problems using RowVersion, and I believe understand
the
> thrust of your points.
> However, I would like to place a spot light on a point I originally made
> that I feel may not have been addressed:
> *** If @.@.TRANCOUNT is = X in ParentSP when ChildSP is called, it must be =
X
> immediately after returning from the CHILD call. , else an error
results***
> If feel that this is the situation in the example you proposed.
> If the ParentSP BEGINs a TRANSACTON (Transaction count is now 1), then
calls
> ChildSP, which does a ROLLBACK within Child, TranCount will be 0 when
> control is returned to the Parent. Since TranCount was 1 just prior to
> calling the Child and it is zero immeditely after returning, this result
in
> an ERROR:
>
>
> This is the part that I am missing. It seems to me that the Child cannot
do
> the rollback if the Parent already began a Transaction.
> I hope I am not trying your patience. I would really like to get this
point
> down.
> Thanks,
> Chad
>
> "Brian Selzer" <BrianSelzer@.discussions.microsoft.com> wrote in message
> news:9E615D99-D61A-4AF3-BEFB-09C2C12281D3@.microsoft.com...
stored
to
will
and
error.
another
a
handler,
perform
0.
fail,
that
calls
=
not
entered
--
outer
>|||PMFJI, but if your child proc is using an explicit tran, then it can be
coded as follows:
create proc dbo.ChildProc
as
set nocount on
declare @.trancount int
set @.trancount = @.@.TRANCOUNT
if @.trancount > 0
begin tran ChildProcTran
else
save tran ChildProcTran
/*
Do some stuff
*/
if @.@.ERROR > 0
begin
raiserror ('We have a problem.', 16, 1)
rollback ChildProcTran
return
end
else if @.trancount = 0 -- began our own
commit tran
go
This way, only the child proc's txn will be rolled back by the child proc.
The parent proc will be unaffected.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Ron Strong" <rstrong@.DoNotSpamerols.com> wrote in message
news:ulKZzJtFFHA.1528@.TK2MSFTNGP09.phx.gbl...
Brian & Chad
I believe I'm having the same issue as Chad with nested stored procedures
inside a transaction.
What I'd like to do is begin a transaction in an outer SP. If all goes
well, the transaction will be committed in the outer stored procedure - no
problem there. However, if an error or other unexpected condition is
encountered, I would like to rollback as close to the error as possible (in
the statement following detection, if possible).
Problem is this might involve a transaction begun in an outer SP being
rollbed back in an inner SP. Due to the fact that on entry to inner SP
@.@.Trancount == 1 but on exit @.@.Trancount == 0, a new error, Error 266, gets
generated.
Following illustrates the problem:
-- INNER SP
create procedure InnerSP as begin
declare @.ErrNo int
/* Do something here */
select @.ErrNo = @.@.ERROR
if @.ErrNo <> 0 begin
if @.@.TRANCOUNT > 0 rollback transaction
return @.ErrNo -- on return new error (266) generated
end
return 0
end
-- OUTER SP
create procedure OuterSP as begin
declare @.ErrNo int
Begin Transaction
exec @.ErrNo = InnerSP
-- if InnerSP failed, @.@.ERROR = 266 here
if @.ErrNo <> 0 begin
if @.@.TRANCOUNT > 0 rollback transaction
return @.ErrNo
end
commit transaction
return 0
end
I could hold off on performing the rollback until OuterSP examines the
return value from InnerSP, but this is not ideal:
(1) Immediately after the error I may want to do some logging or
other fixup. If these are done before the rollback, they will be wiped out
by the rollback.
(2) In code subsequent to ther error there is always the possibility
that execution will be terminated due to a severe error, preventing the
enclosing SP from ever executing the rollback. While the lack of a
subsequent COMMIT will ultimately lead to the transaction being rolled back,
I would have no control over when the rollback occurs.
I can avoid this problem via the kludge of a new "Begin Transaction"
statement just before returning the error code from InnerSP to OuterSP. Is
there a cleaner way to resolve this problem (beyond waiting for SQL Server
2005 try...catch blocks)?
Ron Strong
"Chad" <chad.dokmanovich@.unisys.com> wrote in message
news:cv7rc2$h6a$1@.trsvr.tr.unisys.com...
> Brian,
> Thank you again for your feedback. I appreciate the tip, in particular on
> handling concurrency problems using RowVersion, and I believe understand
the
> thrust of your points.
> However, I would like to place a spot light on a point I originally made
> that I feel may not have been addressed:
> *** If @.@.TRANCOUNT is = X in ParentSP when ChildSP is called, it must be =
X
> immediately after returning from the CHILD call. , else an error
results***
> If feel that this is the situation in the example you proposed.
> If the ParentSP BEGINs a TRANSACTON (Transaction count is now 1), then
calls
> ChildSP, which does a ROLLBACK within Child, TranCount will be 0 when
> control is returned to the Parent. Since TranCount was 1 just prior to
> calling the Child and it is zero immeditely after returning, this result
in
> an ERROR:
>
>
> This is the part that I am missing. It seems to me that the Child cannot
do
> the rollback if the Parent already began a Transaction.
> I hope I am not trying your patience. I would really like to get this
point
> down.
> Thanks,
> Chad
>
> "Brian Selzer" <BrianSelzer@.discussions.microsoft.com> wrote in message
> news:9E615D99-D61A-4AF3-BEFB-09C2C12281D3@.microsoft.com...
stored
to
will
and
error.
another
a
handler,
perform
0.
fail,
that
calls
=
not
entered
--
outer
>|||Typo:
create proc dbo.ChildProc
as
set nocount on
declare @.trancount int
set @.trancount = @.@.TRANCOUNT
if @.trancount = 0 -- No existing tran
begin tran ChildProcTran
else -- Existing tran
save tran ChildProcTran
/*
Do some stuff
*/
if @.@.ERROR > 0
begin
raiserror ('We have a problem.', 16, 1)
rollback ChildProcTran
return
end
else if @.trancount = 0 -- began our own
commit tran
go
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%232UHhStFFHA.3972@.TK2MSFTNGP15.phx.gbl...
PMFJI, but if your child proc is using an explicit tran, then it can be
coded as follows:
create proc dbo.ChildProc
as
set nocount on
declare @.trancount int
set @.trancount = @.@.TRANCOUNT
if @.trancount > 0
begin tran ChildProcTran
else
save tran ChildProcTran
/*
Do some stuff
*/
if @.@.ERROR > 0
begin
raiserror ('We have a problem.', 16, 1)
rollback ChildProcTran
return
end
else if @.trancount = 0 -- began our own
commit tran
go
This way, only the child proc's txn will be rolled back by the child proc.
The parent proc will be unaffected.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Ron Strong" <rstrong@.DoNotSpamerols.com> wrote in message
news:ulKZzJtFFHA.1528@.TK2MSFTNGP09.phx.gbl...
Brian & Chad
I believe I'm having the same issue as Chad with nested stored procedures
inside a transaction.
What I'd like to do is begin a transaction in an outer SP. If all goes
well, the transaction will be committed in the outer stored procedure - no
problem there. However, if an error or other unexpected condition is
encountered, I would like to rollback as close to the error as possible (in
the statement following detection, if possible).
Problem is this might involve a transaction begun in an outer SP being
rollbed back in an inner SP. Due to the fact that on entry to inner SP
@.@.Trancount == 1 but on exit @.@.Trancount == 0, a new error, Error 266, gets
generated.
Following illustrates the problem:
-- INNER SP
create procedure InnerSP as begin
declare @.ErrNo int
/* Do something here */
select @.ErrNo = @.@.ERROR
if @.ErrNo <> 0 begin
if @.@.TRANCOUNT > 0 rollback transaction
return @.ErrNo -- on return new error (266) generated
end
return 0
end
-- OUTER SP
create procedure OuterSP as begin
declare @.ErrNo int
Begin Transaction
exec @.ErrNo = InnerSP
-- if InnerSP failed, @.@.ERROR = 266 here
if @.ErrNo <> 0 begin
if @.@.TRANCOUNT > 0 rollback transaction
return @.ErrNo
end
commit transaction
return 0
end
I could hold off on performing the rollback until OuterSP examines the
return value from InnerSP, but this is not ideal:
(1) Immediately after the error I may want to do some logging or
other fixup. If these are done before the rollback, they will be wiped out
by the rollback.
(2) In code subsequent to ther error there is always the possibility
that execution will be terminated due to a severe error, preventing the
enclosing SP from ever executing the rollback. While the lack of a
subsequent COMMIT will ultimately lead to the transaction being rolled back,
I would have no control over when the rollback occurs.
I can avoid this problem via the kludge of a new "Begin Transaction"
statement just before returning the error code from InnerSP to OuterSP. Is
there a cleaner way to resolve this problem (beyond waiting for SQL Server
2005 try...catch blocks)?
Ron Strong
"Chad" <chad.dokmanovich@.unisys.com> wrote in message
news:cv7rc2$h6a$1@.trsvr.tr.unisys.com...
> Brian,
> Thank you again for your feedback. I appreciate the tip, in particular on
> handling concurrency problems using RowVersion, and I believe understand
the
> thrust of your points.
> However, I would like to place a spot light on a point I originally made
> that I feel may not have been addressed:
> *** If @.@.TRANCOUNT is = X in ParentSP when ChildSP is called, it must be =
X
> immediately after returning from the CHILD call. , else an error
results***
> If feel that this is the situation in the example you proposed.
> If the ParentSP BEGINs a TRANSACTON (Transaction count is now 1), then
calls
> ChildSP, which does a ROLLBACK within Child, TranCount will be 0 when
> control is returned to the Parent. Since TranCount was 1 just prior to
> calling the Child and it is zero immeditely after returning, this result
in
> an ERROR:
>
>
> This is the part that I am missing. It seems to me that the Child cannot
do
> the rollback if the Parent already began a Transaction.
> I hope I am not trying your patience. I would really like to get this
point
> down.
> Thanks,
> Chad
>
> "Brian Selzer" <BrianSelzer@.discussions.microsoft.com> wrote in message
> news:9E615D99-D61A-4AF3-BEFB-09C2C12281D3@.microsoft.com...
stored
to
will
and
error.
another
a
handler,
perform
0.
fail,
that
calls
=
not
entered
--
outer
>|||This will take care of what is done in the child, but what I want to do is
rollback the entire outer transaction - the one initiated in the outer SP.
My example may have been too brief -- the outer SP may be making calls to
several child SPs. What I would like is that any error, whether
encountered in the outer SP or its child SPs, results in an immediate
rollback of all the work performed within the transaction initiated in the
outer SP.
The rule, enforced by the raising of error 266, that entry Trancount = exit
Trancount, seems to preclude doing this.
Ron Strong
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#2UHhStFFHA.3972@.TK2MSFTNGP15.phx.gbl...
> PMFJI, but if your child proc is using an explicit tran, then it can be
> coded as follows:
> create proc dbo.ChildProc
> as
> set nocount on
> declare @.trancount int
> set @.trancount = @.@.TRANCOUNT
> if @.trancount > 0
> begin tran ChildProcTran
> else
> save tran ChildProcTran
> /*
> Do some stuff
> */
> if @.@.ERROR > 0
> begin
> raiserror ('We have a problem.', 16, 1)
> rollback ChildProcTran
> return
> end
> else if @.trancount = 0 -- began our own
> commit tran
> go
> This way, only the child proc's txn will be rolled back by the child proc.
> The parent proc will be unaffected.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "Ron Strong" <rstrong@.DoNotSpamerols.com> wrote in message
> news:ulKZzJtFFHA.1528@.TK2MSFTNGP09.phx.gbl...
> Brian & Chad
> I believe I'm having the same issue as Chad with nested stored procedures
> inside a transaction.
> What I'd like to do is begin a transaction in an outer SP. If all goes
> well, the transaction will be committed in the outer stored procedure - no
> problem there. However, if an error or other unexpected condition is
> encountered, I would like to rollback as close to the error as possible
(in
> the statement following detection, if possible).
> Problem is this might involve a transaction begun in an outer SP being
> rollbed back in an inner SP. Due to the fact that on entry to inner SP
> @.@.Trancount == 1 but on exit @.@.Trancount == 0, a new error, Error 266,
gets
> generated.
> Following illustrates the problem:
> -- INNER SP
> create procedure InnerSP as begin
> declare @.ErrNo int
> /* Do something here */
> select @.ErrNo = @.@.ERROR
> if @.ErrNo <> 0 begin
> if @.@.TRANCOUNT > 0 rollback transaction
> return @.ErrNo -- on return new error (266) generated
> end
> return 0
> end
> -- OUTER SP
> create procedure OuterSP as begin
> declare @.ErrNo int
> Begin Transaction
> exec @.ErrNo = InnerSP
> -- if InnerSP failed, @.@.ERROR = 266 here
> if @.ErrNo <> 0 begin
> if @.@.TRANCOUNT > 0 rollback transaction
> return @.ErrNo
> end
> commit transaction
> return 0
> end
>
> I could hold off on performing the rollback until OuterSP examines the
> return value from InnerSP, but this is not ideal:
> (1) Immediately after the error I may want to do some logging or
> other fixup. If these are done before the rollback, they will be wiped out
> by the rollback.
> (2) In code subsequent to ther error there is always the
possibility
> that execution will be terminated due to a severe error, preventing the
> enclosing SP from ever executing the rollback. While the lack of a
> subsequent COMMIT will ultimately lead to the transaction being rolled
back,
> I would have no control over when the rollback occurs.
> I can avoid this problem via the kludge of a new "Begin Transaction"
> statement just before returning the error code from InnerSP to OuterSP.
Is
> there a cleaner way to resolve this problem (beyond waiting for SQL Server
> 2005 try...catch blocks)?
>
> Ron Strong
> "Chad" <chad.dokmanovich@.unisys.com> wrote in message
> news:cv7rc2$h6a$1@.trsvr.tr.unisys.com...
on
> the
=
> X
> results***
> calls
> in
0.
> do
> point
> stored
procedure
> to
> will
detected
rolls
> and
had
and
> error.
> another
not
call
> a
child,
proc,
> handler,
an
> perform
> 0.
message
> fail,
> that
on
entry.
> calls
a
be
> =
> not
seems
> entered
>
> --
> outer
>|||Well, you could nevertheless have the child manage itself, since it never
knows how it will be called. When it throws its error, it returns to the
calling proc, which then decides to rollback and exit, without calling any
more procs.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Ron Strong" <rstrong@.DoNotSpamerols.com> wrote in message
news:O2r1EmtFFHA.3648@.TK2MSFTNGP09.phx.gbl...
This will take care of what is done in the child, but what I want to do is
rollback the entire outer transaction - the one initiated in the outer SP.
My example may have been too brief -- the outer SP may be making calls to
several child SPs. What I would like is that any error, whether
encountered in the outer SP or its child SPs, results in an immediate
rollback of all the work performed within the transaction initiated in the
outer SP.
The rule, enforced by the raising of error 266, that entry Trancount = exit
Trancount, seems to preclude doing this.
Ron Strong
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#2UHhStFFHA.3972@.TK2MSFTNGP15.phx.gbl...
> PMFJI, but if your child proc is using an explicit tran, then it can be
> coded as follows:
> create proc dbo.ChildProc
> as
> set nocount on
> declare @.trancount int
> set @.trancount = @.@.TRANCOUNT
> if @.trancount > 0
> begin tran ChildProcTran
> else
> save tran ChildProcTran
> /*
> Do some stuff
> */
> if @.@.ERROR > 0
> begin
> raiserror ('We have a problem.', 16, 1)
> rollback ChildProcTran
> return
> end
> else if @.trancount = 0 -- began our own
> commit tran
> go
> This way, only the child proc's txn will be rolled back by the child proc.
> The parent proc will be unaffected.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "Ron Strong" <rstrong@.DoNotSpamerols.com> wrote in message
> news:ulKZzJtFFHA.1528@.TK2MSFTNGP09.phx.gbl...
> Brian & Chad
> I believe I'm having the same issue as Chad with nested stored procedures
> inside a transaction.
> What I'd like to do is begin a transaction in an outer SP. If all goes
> well, the transaction will be committed in the outer stored procedure - no
> problem there. However, if an error or other unexpected condition is
> encountered, I would like to rollback as close to the error as possible
(in
> the statement following detection, if possible).
> Problem is this might involve a transaction begun in an outer SP being
> rollbed back in an inner SP. Due to the fact that on entry to inner SP
> @.@.Trancount == 1 but on exit @.@.Trancount == 0, a new error, Error 266,
gets
> generated.
> Following illustrates the problem:
> -- INNER SP
> create procedure InnerSP as begin
> declare @.ErrNo int
> /* Do something here */
> select @.ErrNo = @.@.ERROR
> if @.ErrNo <> 0 begin
> if @.@.TRANCOUNT > 0 rollback transaction
> return @.ErrNo -- on return new error (266) generated
> end
> return 0
> end
> -- OUTER SP
> create procedure OuterSP as begin
> declare @.ErrNo int
> Begin Transaction
> exec @.ErrNo = InnerSP
> -- if InnerSP failed, @.@.ERROR = 266 here
> if @.ErrNo <> 0 begin
> if @.@.TRANCOUNT > 0 rollback transaction
> return @.ErrNo
> end
> commit transaction
> return 0
> end
>
> I could hold off on performing the rollback until OuterSP examines the
> return value from InnerSP, but this is not ideal:
> (1) Immediately after the error I may want to do some logging or
> other fixup. If these are done before the rollback, they will be wiped out
> by the rollback.
> (2) In code subsequent to ther error there is always the
possibility
> that execution will be terminated due to a severe error, preventing the
> enclosing SP from ever executing the rollback. While the lack of a
> subsequent COMMIT will ultimately lead to the transaction being rolled
back,
> I would have no control over when the rollback occurs.
> I can avoid this problem via the kludge of a new "Begin Transaction"
> statement just before returning the error code from InnerSP to OuterSP.
Is
> there a cleaner way to resolve this problem (beyond waiting for SQL Server
> 2005 try...catch blocks)?
>
> Ron Strong
> "Chad" <chad.dokmanovich@.unisys.com> wrote in message
> news:cv7rc2$h6a$1@.trsvr.tr.unisys.com...
on
> the
=
> X
> results***
> calls
> in
0.
> do
> point
> stored
procedure
> to
> will
detected
rolls
> and
had
and
> error.
> another
not
call
> a
child,
proc,
> handler,
an
> perform
> 0.
message
> fail,
> that
on
entry.
> calls
a
be
> =
> not
seems
> entered
>
> --
> outer
>
Showing posts with label child. Show all posts
Showing posts with label child. Show all posts
Thursday, March 22, 2012
Thursday, March 8, 2012
Child/ Parent relationship within table
Hi everyone, I have a categories table which has the following main
attributes
CategoryName, CategoryID <- Identity , ParentCategoryID
What I need help doing is constructing a Procedure/ SQL query where I can
show the expanded relationships for each record in the table.
e.g. If I have three records in the table (Following the attributes
described above)
ParentCategory, 1, 0
ChildCategory, 2,1
SubChildCategory, 3,2
I want to be able to dynamically return the following information when I
execute the query (Each row looks at the parent id and concatenates itself
to its parents CategoryName).
ParentCategory
ParentCategory/ChildCategory
ParentCategory/ChildCategory/SubChildCategory
Thanks in advance
MarlkEverything you are doing is wrong. Let's get back to the basics of an
RDBMS. Rows are not records; fields are not columns; tables are not
files; there is no sequential access or ordering in an RDBMS, so
"first", "next" and "last" are totally meaningless.
Stop using IDENTITY and learn what a relational key is.
Get a copy of TREES & HIERARCHIES IN SQL You are trying to write
(uughh!) procedural code to build a traversal.that will create a path.
This is not the best way; google a "nested sets model" instead.|||There are many, many resources for describing ways to efficiently model
hierarchies and trees in SQL.
Google for the following topics: Nested Sets, Nested Intervals, Adjacency
List, Materialized Path
Here is a good starting point for finding information about this topic:
http://troels.arvin.dk/db/rdbms/links/#hierarchical
As a first step, you might consider creating a new table to manage the
relationship between categories - right now, you are modeling both the
category and the relationship between categories in the same table.
Normalizing the design can give you added flexibility depending on your
requirements.
What you have here is basically an Adjacency List model. This model has
excellent characteristics with regard to modifying the layout of the
hierarchy; you simply change the ParentCategoryID of a node to a different
value, and you instantly "move" that node and all referencing nodes to a
different location in the hierarchy. However, it does not work very well for
retrieving the structure, as you are seeing. SQL Server Books Online has a
section titled "Expanding Hierarchies" that describes an iterative process
of querying the hierarchy that involves using a temporary table as a stack,
but SQL is really optimized for set-based operations. You will probably find
something at the link above that better meets your needs.
"Mark" <dont@.spam.me> wrote in message
news:%233O9T%23KuFHA.1560@.TK2MSFTNGP09.phx.gbl...
> Hi everyone, I have a categories table which has the following main
> attributes
> CategoryName, CategoryID <- Identity , ParentCategoryID
> What I need help doing is constructing a Procedure/ SQL query where I can
> show the expanded relationships for each record in the table.
> e.g. If I have three records in the table (Following the attributes
> described above)
> ParentCategory, 1, 0
> ChildCategory, 2,1
> SubChildCategory, 3,2
> I want to be able to dynamically return the following information when I
> execute the query (Each row looks at the parent id and concatenates itself
> to its parents CategoryName).
> ParentCategory
> ParentCategory/ChildCategory
> ParentCategory/ChildCategory/SubChildCategory
> Thanks in advance
> Marlk
>
>
>
>|||Whoa! Cool, thanks for the links
Cheers
Mark
"Jeremy Williams" <jeremydwill@.netscape.net> wrote in message
news:ekyuNZLuFHA.2072@.TK2MSFTNGP14.phx.gbl...
> There are many, many resources for describing ways to efficiently model
> hierarchies and trees in SQL.
> Google for the following topics: Nested Sets, Nested Intervals, Adjacency
> List, Materialized Path
> Here is a good starting point for finding information about this topic:
> http://troels.arvin.dk/db/rdbms/links/#hierarchical
> As a first step, you might consider creating a new table to manage the
> relationship between categories - right now, you are modeling both the
> category and the relationship between categories in the same table.
> Normalizing the design can give you added flexibility depending on your
> requirements.
> What you have here is basically an Adjacency List model. This model has
> excellent characteristics with regard to modifying the layout of the
> hierarchy; you simply change the ParentCategoryID of a node to a different
> value, and you instantly "move" that node and all referencing nodes to a
> different location in the hierarchy. However, it does not work very well
for
> retrieving the structure, as you are seeing. SQL Server Books Online has a
> section titled "Expanding Hierarchies" that describes an iterative process
> of querying the hierarchy that involves using a temporary table as a
stack,
> but SQL is really optimized for set-based operations. You will probably
find
> something at the link above that better meets your needs.
> "Mark" <dont@.spam.me> wrote in message
> news:%233O9T%23KuFHA.1560@.TK2MSFTNGP09.phx.gbl...
can
itself
>
attributes
CategoryName, CategoryID <- Identity , ParentCategoryID
What I need help doing is constructing a Procedure/ SQL query where I can
show the expanded relationships for each record in the table.
e.g. If I have three records in the table (Following the attributes
described above)
ParentCategory, 1, 0
ChildCategory, 2,1
SubChildCategory, 3,2
I want to be able to dynamically return the following information when I
execute the query (Each row looks at the parent id and concatenates itself
to its parents CategoryName).
ParentCategory
ParentCategory/ChildCategory
ParentCategory/ChildCategory/SubChildCategory
Thanks in advance
MarlkEverything you are doing is wrong. Let's get back to the basics of an
RDBMS. Rows are not records; fields are not columns; tables are not
files; there is no sequential access or ordering in an RDBMS, so
"first", "next" and "last" are totally meaningless.
Stop using IDENTITY and learn what a relational key is.
Get a copy of TREES & HIERARCHIES IN SQL You are trying to write
(uughh!) procedural code to build a traversal.that will create a path.
This is not the best way; google a "nested sets model" instead.|||There are many, many resources for describing ways to efficiently model
hierarchies and trees in SQL.
Google for the following topics: Nested Sets, Nested Intervals, Adjacency
List, Materialized Path
Here is a good starting point for finding information about this topic:
http://troels.arvin.dk/db/rdbms/links/#hierarchical
As a first step, you might consider creating a new table to manage the
relationship between categories - right now, you are modeling both the
category and the relationship between categories in the same table.
Normalizing the design can give you added flexibility depending on your
requirements.
What you have here is basically an Adjacency List model. This model has
excellent characteristics with regard to modifying the layout of the
hierarchy; you simply change the ParentCategoryID of a node to a different
value, and you instantly "move" that node and all referencing nodes to a
different location in the hierarchy. However, it does not work very well for
retrieving the structure, as you are seeing. SQL Server Books Online has a
section titled "Expanding Hierarchies" that describes an iterative process
of querying the hierarchy that involves using a temporary table as a stack,
but SQL is really optimized for set-based operations. You will probably find
something at the link above that better meets your needs.
"Mark" <dont@.spam.me> wrote in message
news:%233O9T%23KuFHA.1560@.TK2MSFTNGP09.phx.gbl...
> Hi everyone, I have a categories table which has the following main
> attributes
> CategoryName, CategoryID <- Identity , ParentCategoryID
> What I need help doing is constructing a Procedure/ SQL query where I can
> show the expanded relationships for each record in the table.
> e.g. If I have three records in the table (Following the attributes
> described above)
> ParentCategory, 1, 0
> ChildCategory, 2,1
> SubChildCategory, 3,2
> I want to be able to dynamically return the following information when I
> execute the query (Each row looks at the parent id and concatenates itself
> to its parents CategoryName).
> ParentCategory
> ParentCategory/ChildCategory
> ParentCategory/ChildCategory/SubChildCategory
> Thanks in advance
> Marlk
>
>
>
>|||Whoa! Cool, thanks for the links
Cheers
Mark
"Jeremy Williams" <jeremydwill@.netscape.net> wrote in message
news:ekyuNZLuFHA.2072@.TK2MSFTNGP14.phx.gbl...
> There are many, many resources for describing ways to efficiently model
> hierarchies and trees in SQL.
> Google for the following topics: Nested Sets, Nested Intervals, Adjacency
> List, Materialized Path
> Here is a good starting point for finding information about this topic:
> http://troels.arvin.dk/db/rdbms/links/#hierarchical
> As a first step, you might consider creating a new table to manage the
> relationship between categories - right now, you are modeling both the
> category and the relationship between categories in the same table.
> Normalizing the design can give you added flexibility depending on your
> requirements.
> What you have here is basically an Adjacency List model. This model has
> excellent characteristics with regard to modifying the layout of the
> hierarchy; you simply change the ParentCategoryID of a node to a different
> value, and you instantly "move" that node and all referencing nodes to a
> different location in the hierarchy. However, it does not work very well
for
> retrieving the structure, as you are seeing. SQL Server Books Online has a
> section titled "Expanding Hierarchies" that describes an iterative process
> of querying the hierarchy that involves using a temporary table as a
stack,
> but SQL is really optimized for set-based operations. You will probably
find
> something at the link above that better meets your needs.
> "Mark" <dont@.spam.me> wrote in message
> news:%233O9T%23KuFHA.1560@.TK2MSFTNGP09.phx.gbl...
can
itself
>
child,chid of child ,child of child of child
.........
child |parent|
..........
a1 |a |
..........
a2 |a |
..........
a11 |a1 |
..........
b1 |b |
..........
b11 |b1 |
..........
b111 |b1 |
..........
Here is my table
I want to get all childs of "a" ie {a1,a11,a2}
I mean recursively
ie child,chid of child ,child of child of child ......etc up to any extentet the table contain
How can i for mulate the select querry
?
It depends which type of database you are using. If it is SQL Server 2005 then you can use CTE but this isn't able in 2000. Have a look atthis article for more info.
i am using SQL Server 2000
|||
There are various methods you can use in 2000. Most of them are listedhere.
Child packages: Execute them all out of process?
HI, I have some parent parent packages that calls child packages. When I added a bunch of packages, I faced the buffer out of memory error. I then decided to set the child packages property ExecuteOutOfProcess to TRUE. I noticed that the execution time is longer now. Is this a good practice to set the ExecuteOutOfProcess to true? If so, is it normal that the execution time is longer?
Thank you,
Ccote
ccote wrote:
HI, I have some parent parent packages that calls child packages. When I added a bunch of packages, I faced the buffer out of memory error. I then decided to set the child packages property ExecuteOutOfProcess to TRUE. I noticed that the execution time is longer now. Is this a good practice to set the ExecuteOutOfProcess to true? If so, is it normal that the execution time is longer?
Thank you,
Ccote
I don't think there is a any best practice guidance around this. Personally I tend to think if you need to execute them out of process, then do so. otherwise, in proc is fine. I can't think of another rationale for one or the other.
-Jamie
|||
Out of process is slower, but gives you a new process (Obviously!) and this allows a new set of memory. For 32-bit this can be benefical as you get another 2Gb (/3Gb), just for the out of proc package execution host, rather than sharing the memory of the parent. So if you have a high memory requirement and the machine has enough memory to support the two processes taking their own share, then out of proc makes sense, but at the cost of speed.
So what you see is expected. It takes time to setup a new process and allocate it all that memory.
Child packages : Failed to acquire connection
Hi!
I am having a problem with a Parent package that invokes Child SSIS packages.
The Child packages have EncryptSensitiveWithPassword as their security setting.
I have placed the PackagePassword for each Child in the Parent package.
Each Child package contains a SQL Server Authentication username and password; they are not using Windows Authentication for the SQL Server login.
Here are what seem to be the relevant entries from a log file when the failure occurs:
User
User
User
User
OnError,XXXX,YYYYY\xxxx,SQLTask1 in ChildPackage1,GUID3,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,-1073573396,0x,Failed to acquire connection "Microsoft OLE DB Provider for SQL Server". Connection may not be configured correctly or you may not have the right permissions on this connection.
OnError,XXXX,YYYYY\xxxx,ChildPackage1,GUID4,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,-1073573396,0x,Failed to acquire connection "Microsoft OLE DB Provider for SQL Server". Connection may not be configured correctly or you may not have the right permissions on this connection.
When I run the Child package by itself, it works without any errors. So the problem seems to be related to the fact that it is being called by the parent, and somehow the "security settings" for the parent are not quite the same as the settings when I run the Child package by itself.
What might I be doing wrong?
I am using File System storage for my packages, on a shared network drive.
Just to check - you are setting the password in the Execute Package task, right? My apologies if this is obvious, just wanted to confirm.
|||Yes, I have set the password in each Execute Package task -- and the error message is not about an inability to open the package (for which there appear to be a number of error messages available). Instead, it is one that seems to indicate an inability to login to SQL Server with the SQL Server Authentication incorporated in the package.
I have read some web pages (e.g., the link below) that refer to the Integrated Security of a SQL Server connection. I see what appears to be some XML for that in the Child packages (but none in the Parent package) -- and the value that I found was "True". I tried changing it to "SSPI", but then I couldn't even run the child package by itself, nor when called by the Parent.
http://blogs.msdn.com/suryaj/archive/2006/05/09/594039.aspx
|||Is it possible to use the Debugger in Visual Studio 2005 to study the connection parameter values that are present when a Parent package invokes a Child package?
Dan
|||I'm going to take a look at using the script Jamie Thompon wrote, as found at
http://blogs.conchango.com/jamiethomson/archive/2005/10/10/2253.aspx
I hope it will give me some clues as to why the connection cannot be acquired when the SSIS package is run as a child package.
|||John,
Using Jamie Thomson's "script task" I could see that you were correct: my problem was still with the package password. I must have typed it incorrectly, or something.
I am sorry to have dismissed your suggestion so quickly -- but I didn't see it until I was off on vacation and happened to logon and notice your post. So I didn't have SQL Server at my disposal to investigate further. You have my sincere apologies.
Dan
|||No problem - I'm glad you were able to resolve it.Child packages : Failed to acquire connection
Hi!
I am having a problem with a Parent package that invokes Child SSIS packages.
The Child packages have EncryptSensitiveWithPassword as their security setting.
I have placed the PackagePassword for each Child in the Parent package.
Each Child package contains a SQL Server Authentication username and password; they are not using Windows Authentication for the SQL Server login.
Here are what seem to be the relevant entries from a log file when the failure occurs:
User
User
User
User
OnError,XXXX,YYYYY\xxxx,SQLTask1 in ChildPackage1,GUID3,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,-1073573396,0x,Failed to acquire connection "Microsoft OLE DB Provider for SQL Server". Connection may not be configured correctly or you may not have the right permissions on this connection.
OnError,XXXX,YYYYY\xxxx,ChildPackage1,GUID4,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,-1073573396,0x,Failed to acquire connection "Microsoft OLE DB Provider for SQL Server". Connection may not be configured correctly or you may not have the right permissions on this connection.
When I run the Child package by itself, it works without any errors. So the problem seems to be related to the fact that it is being called by the parent, and somehow the "security settings" for the parent are not quite the same as the settings when I run the Child package by itself.
What might I be doing wrong?
I am using File System storage for my packages, on a shared network drive.
Just to check - you are setting the password in the Execute Package task, right? My apologies if this is obvious, just wanted to confirm.|||Yes, I have set the password in each Execute Package task -- and the error message is not about an inability to open the package (for which there appear to be a number of error messages available). Instead, it is one that seems to indicate an inability to login to SQL Server with the SQL Server Authentication incorporated in the package.
I have read some web pages (e.g., the link below) that refer to the Integrated Security of a SQL Server connection. I see what appears to be some XML for that in the Child packages (but none in the Parent package) -- and the value that I found was "True". I tried changing it to "SSPI", but then I couldn't even run the child package by itself, nor when called by the Parent.
http://blogs.msdn.com/suryaj/archive/2006/05/09/594039.aspx
|||Is it possible to use the Debugger in Visual Studio 2005 to study the connection parameter values that are present when a Parent package invokes a Child package?
Dan
|||I'm going to take a look at using the script Jamie Thompon wrote, as found at
http://blogs.conchango.com/jamiethomson/archive/2005/10/10/2253.aspx
I hope it will give me some clues as to why the connection cannot be acquired when the SSIS package is run as a child package.
|||John,
Using Jamie Thomson's "script task" I could see that you were correct: my problem was still with the package password. I must have typed it incorrectly, or something.
I am sorry to have dismissed your suggestion so quickly -- but I didn't see it until I was off on vacation and happened to logon and notice your post. So I didn't have SQL Server at my disposal to investigate further. You have my sincere apologies.
Dan
|||No problem - I'm glad you were able to resolve it.Child Package Fails when called from parent
So I have a parent package that calls another package using the Execute Package Task. When I run the child it runs fine but when I run it from the parent i get this msg...Any ideas?
Error: 0xC00220E4 at Execute VR Account Load: Error 0xC0012050 while preparing to load the package. Package failed validation from the ExecutePackage task. The package cannot run.
Pls take a look at this post to see whether the investigations and solutions there helps. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=241941&SiteID=1
thanks
wenyang
|||Wenyang,
Thanks for the reply but the solution did not work. I might have found a bug here because the child package uses package configurations and even though I have disabled the package config on the child when I execute it from the parent the output window says that it is trying to load the package configurations. Not sure if this has anything to do with it.
Information: 0x40016040 at VR Load Account: The package is attempting to configure from SQL Server using the configuration string ""localhost.CALLMIS";"[dbo].[SSIS Configurations]";"MISLoads_ServerName";".
Thx
|||Disregard last msg I posted. It appears the problem was that there was a bad connection guid or something like that still hanging around in the child? I recreated the package and it appears to be working now...
Not my idea of fun...
Child package ConnectionManager visibility
Hopefully a simple question about parent-child package relationship. For this example, let's say I have a simple setup - one parent package: parent.dtsx, and one child package: child.dtsx. The parent package calls the child package via the ExecutePackage Task.
If I add an OleDB ConnectionManager to the parent package called MySqlConnectionManager, should I be able to reference this connection via a script task (or custom component) from my child package? I realize that I will have a problem doing this at design time, but I thought I could get around it with the script task or custom component. That said, when I look in the Connections collection at run-time from within my child package, I do not see the parent package's MySqlConnectionManager. Am I missing something, or is this the way it was intended to work?
Thanks,
David
David,
I suspect you cannot do this. Connection managers can only be used in the package in which they reside - even if you're using a script task.
-Jamie
|||
Jamie,
Thanks for the response. I must say it is somewhat disappointing, though I think I have a work around for my situation. That said, I would still be interested in hearing a rationale for why this is the case. It seems to me like it breaks the container hierarchy paradigm.
David
|||Well I can see why you think this but remember that connection managers don't follow container scope like variables do so the same rules don't apply.
Having said that, there were plans to scope conenction managers to the container hierarchy but it couldn't be done in time (or something). Reading between the lines its something they (well...kirk Haselden) wanted to do but it was down the priority list.
-Jamie
|||
Thanks again, Jamie. Hopefully this will be implemented at some point in the future.
Related, I found the opposite to be true when dealing with log providers. Interestingly, the connections collection of the parent package DOES appear to be available to child package log providers (I have built a custom log provider in which this appears to be true). It strikes me as bizzare that the functionality I want is there for log providers, but not for the package tasks. That may be due, however, to gaps in my understanding of parent-child package relationships.
Child Package cannot always be found
Hi folks, please can you help.
I have an Integration Services file package that calls another file package. The child package will always reside in the same directory as the parent package. In the connection manager for the child package I have simply specified the name of the child package not the full directory path so that the child package path is a relative path to the parent package. This all works perfectly when running the package from within Visual Studio however when running it using dtexecui it sometimes works and other times it fails with the error “the system cannot find the file specified”. Does anyone know what the reason for this inconsistency is? Alternatively is there a way to get the package path during runtime and dynamically set the path using the task expressions?
I can't explain why it sometimes works and other times does not.
There is no way to get the package path at runtime although i think this would be quite a nice feature. Possibly available in a system variable.
The way I get around this problem is documented here: http://blogs.conchango.com/jamiethomson/archive/2006/01/05/2559.aspx
Hope that makes sense.
-Jamie
|||
The reason relative paths don't work is because they are relative to the location of the application that runs the package. So, if the application you're running is in the right location, it will work. If not, the relative path points to a location that doesn't exist or at least, where the file doesn't exist.
K
|||Kirk you are absolutely right but the inconsistency of it was throwing me off. Fortunately I have now figured that out! As it turns out it is the way in which the package gets run that causes it to work or not work. When executing the package by double-clicking on it to bring up dtexecui it works perfectly, however when running dtexec or dtexecui from the command line it does not work! Makes perfect sense now that I can see that there is consistency!
Jamie I can’t seem to access your link at the moment but I will have a look at it because it sounds like it may be the only an alternative.
Thanks for your responses guys.
CHILD functions?
How can I pick out a "top child" in SQL Server 2000?
Example:
----------
| Employee || code || action date |
----------
| 00000001 || AAA || 01/02/2005 |
| 00000001 || DDD || 01/02/2001 |
| 00000001 || CCC || 01/06/2000 |
| 00000002 || AAA || 01/02/2006 |
| 00000002 || CCC || 01/10/2004 |
| 00000003 || DDD || 05/12/2001 |
| 00000004 || AAA || 09/09/2006 |
| 00000004 || SSS || 09/06/2000 |
----------
I want to pick out the record with the latest date for every employee so I end up with something like this:
----------
| Employee || code || action date |
----------
| 00000001 || AAA || 01/02/2005 |
| 00000002 || AAA || 01/02/2006 |
| 00000003 || DDD || 05/12/2001 |
| 00000004 || AAA || 09/09/2006 |
----------
Any ideas?
Thanks in advance!
-GeorgeV??:
SELECT Employee, code, MAX([action date])
FROM YourTable
GROUP BY Employee, code|||Hmm, nice idea but it doesn't want to work.
Any more suggestions?
-GeorgeV|||SELECT Employee, code, [action date]
FROM YourTable YT
WHERE [action date] = (SELECT MAX([action date])
FROM YourTable
WHERE Employee = YT.Employee)
Example:
----------
| Employee || code || action date |
----------
| 00000001 || AAA || 01/02/2005 |
| 00000001 || DDD || 01/02/2001 |
| 00000001 || CCC || 01/06/2000 |
| 00000002 || AAA || 01/02/2006 |
| 00000002 || CCC || 01/10/2004 |
| 00000003 || DDD || 05/12/2001 |
| 00000004 || AAA || 09/09/2006 |
| 00000004 || SSS || 09/06/2000 |
----------
I want to pick out the record with the latest date for every employee so I end up with something like this:
----------
| Employee || code || action date |
----------
| 00000001 || AAA || 01/02/2005 |
| 00000002 || AAA || 01/02/2006 |
| 00000003 || DDD || 05/12/2001 |
| 00000004 || AAA || 09/09/2006 |
----------
Any ideas?
Thanks in advance!
-GeorgeV??:
SELECT Employee, code, MAX([action date])
FROM YourTable
GROUP BY Employee, code|||Hmm, nice idea but it doesn't want to work.
Any more suggestions?
-GeorgeV|||SELECT Employee, code, [action date]
FROM YourTable YT
WHERE [action date] = (SELECT MAX([action date])
FROM YourTable
WHERE Employee = YT.Employee)
Wednesday, March 7, 2012
Checkpoints problem in parallel tasks
Hi,
I have a master package with a sequence container with around 10 execute package tasks (for child packages), all in parallel. Checkpoints has been enabled in the master package. For the execute package tasks FailParentOnFailure is set to true and for the sequence container FailPackageOnFailure is set to true.
The problem i am facing is as follows. One of the parallel tasks fails and at the time of failure some of the parallel tasks (say set S1) are completed succesfully and few are still in execution (say set S2) which eventually complete successfully. The container fails after all the tasks complete execution and fails the package. When the package is restarted the task which failed is not executed, but the tasks in set S2 are executed.
If FailPackageOnFailure is set to true and whatever be the FailParentOnFailure value for the execute package task, in case of restart the failed package is executed but the tasks in set S2 are also executed.
Please let me know if there is any setting that only the failed task executes on restart.
Thanks in advance
Essentially, you want to track the outcome of parallel execute package tasks, and only re-execute those which have failed. The problem with using checkpoint files to accomplish this, is that checkpoint files don't track the status of parallel containers after the first task failure associated with FailPackageOnFailure happens.
What that means, if you have 10 parallel EPTs (execute package tasks), and any one of them has a task failure, none of the subsequently completed EPT tasks, whether they succeed or fail, have their outcomes written to the checkpoint file. So, on package restart, "post last checkpoint file write" tasks will run again.
An easier approach might be to put a for loop around each EPT, and loop until successful, using a variable scoped at the For Loop as a"Go/No Go" decision maker, and max retry count.
However, if you want to do it in SSIS using a restart mechanism, you could roll your own checkpointing mechanism.
Such a mechanism would mean creating an OnTaskFailed event handler which would track the failed EPT SourceID/SourceName's (read TaskID/TaskName).
Then add an OnPostExecute event handler to determine those EPTs which succeeded by inference (they didn't fail). Add in a final "On Completetion" script task to append the successful TaskIDs to a configuration file which would then be read in automatically on subsequent execution.
Lastly, you'd set the the Disable property on each EPT to something like FINDSTRING(@.SuccessfulTaskIDs,@.System::TaskID,1) > 0. You can do it that way, but its not point and click by any stretch.
Subscribe to:
Posts (Atom)