Maintain Transaction even if need to loop through multiple data

I have a ADP app that executes several SPs with a click of a button
Now I want to combine these SP into 1 transaction so that it can
rollback even if one SP fails.
Problem is several SP need to process recordsets in vba so I have made
while loops in vba for them. I do this to avoid cursors as they no good
performers. My choice of avoiding cursors has broken the 'transaction
boundary' therefore I cannot do a roll back for these. How can I still
use store procedures but still maintain performance and be able to roll
back all the SPs if any of them fails?Have you considered rewriting the procs in set based fashion such that
you don't need to call them in a loop? A for each loop is really just a
cursor by another name. Without seeing what you are doing it's
difficult to advise otherwise.
David Portas
SQL Server MVP
--|||Thanks for the hint -> set based technique.
I've found what I've been looking for:
http://groups.google.com/group/micr...8924a2b73888ca4|||I got another problem.
I got this dynamic sp:
create procedure spFindData()
as
declare @.statement varchar(255),@.intYear int,intCount int
set @.intyear = 2010
while @.intyear >=2004
begin
select @.statement = "select count(*) from sales' + @.intYear
-- I want to assign the result of @.statement to @.intCount, how?
if (@.intCount>0)
begin
return @.intYear
end
@.intYear = @.intYear -1
end

Maintain Transaction even if need to loop through multiple data

I have a ADP app that executes several SPs with a click of a button
Now I want to combine these SP into 1 transaction so that it can
rollback even if one SP fails.
Problem is several SP need to process recordsets in vba so I have made
while loops in vba for them. I do this to avoid cursors as they no good
performers. My choice of avoiding cursors has broken the 'transaction
boundary' therefore I cannot do a roll back for these. How can I still
use store procedures but still maintain performance and be able to roll
back all the SPs if any of them fails?Have you considered rewriting the procs in set based fashion such that
you don't need to call them in a loop? A for each loop is really just a
cursor by another name. Without seeing what you are doing it's
difficult to advise otherwise.
--
David Portas
SQL Server MVP
--|||Thanks for the hint -> set based technique.
I've found what I've been looking for:
http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/eb09130dcb79c392/d8924a2b73888ca4?lnk=st&q=stored+procedure+set+based&rnum=3&hl=en#d8924a2b73888ca4|||I got another problem.
I got this dynamic sp:
create procedure spFindData()
as
declare @.statement varchar(255),@.intYear int,intCount int
set @.intyear = 2010
while @.intyear >=2004
begin
select @.statement = "select count(*) from sales' + @.intYear
-- I want to assign the result of @.statement to @.intCount, how?
if (@.intCount>0)
begin
return @.intYear
end
@.intYear = @.intYear -1
end

Maintain Transaction even if need to loop through multiple data

I have a ADP app that executes several SPs with a click of a button
Now I want to combine these SP into 1 transaction so that it can
rollback even if one SP fails.
Problem is several SP need to process recordsets in vba so I have made
while loops in vba for them. I do this to avoid cursors as they no good
performers. My choice of avoiding cursors has broken the 'transaction
boundary' therefore I cannot do a roll back for these. How can I still
use store procedures but still maintain performance and be able to roll
back all the SPs if any of them fails?
Have you considered rewriting the procs in set based fashion such that
you don't need to call them in a loop? A for each loop is really just a
cursor by another name. Without seeing what you are doing it's
difficult to advise otherwise.
David Portas
SQL Server MVP
|||Thanks for the hint -> set based technique.
I've found what I've been looking for:
http://groups.google.com/group/micro...924a2b73888ca4
|||I got another problem.
I got this dynamic sp:
create procedure spFindData()
as
declare @.statement varchar(255),@.intYear int,intCount int
set @.intyear = 2010
while @.intyear >=2004
begin
select @.statement = "select count(*) from sales' + @.intYear
-- I want to assign the result of @.statement to @.intCount, how?
if (@.intCount>0)
begin
return @.intYear
end
@.intYear = @.intYear -1
end

Main report Calls subreport Sps when subreport is set to invisible.

Hello,
I have about 15 subreports in main report, and i am making then visible/invisible based on the selection criteria. Currently even i make a subreport invisible, then also i found using profiler that it is calling SP. so the performance doesnot gets improved !

I want to stop these calls when the subreport is invisible! that helps me a gr8!
OR -
Is there any setting in the config file to avoid this ?

please reply ASAP.

Thanks in advance,
Abhay Lakkad

Currently, subreports will still get executed if the end up being invisible in the main report. We will improve this in future releases.

-- Robert

|||

Just wondered if there is any kind of work-around for this, or if it will be addressed in an upcoming RS 2005 service pack.

Thanks
Marie

Main report Calls subreport Sps when subreport is set to invisible.

Hello,
I have about 15 subreports in main report, and i am making then visible/invisible based on the selection criteria. Currently even i make a subreport invisible, then also i found using profiler that it is calling SP. so the performance doesnot gets improved !

I want to stop these calls when the subreport is invisible! that helps me a gr8!
OR -
Is there any setting in the config file to avoid this ?

please reply ASAP.

Thanks in advance,
Abhay Lakkad

Currently, subreports will still get executed if the end up being invisible in the main report. We will improve this in future releases.

-- Robert

|||

Just wondered if there is any kind of work-around for this, or if it will be addressed in an upcoming RS 2005 service pack.

Thanks
Marie