June 2, 2010 at 8:52 am
My problem is i have a driver table that controls what should be run as well as the associated input params. I then have a proc "usp_GenerateLeadsDriverProc" that loops through the driver table and makes the proc calls using dynamic sql because i think it's the only way. My problem is these procs are actually creating and dropping the same friggen tables. DONT ASK AND PLEASE DONT GET ME STARTED BECAUSE AT THE MOMENT REWRITTING THESE IS NOT AN OPTION. Therefore these procs need to run in sequence and one must not start till the prev completes. How can i accomplish that being i'm using dynamic sql as my driver? i wrote the wrapper driver so if there is a better way than dynamic sql i'm all ears.
CREATE TABLE [dbo].[usp_GenerateLeadsDriver](
[id] [int] IDENTITY(1,1) NOT NULL,
[ProcessName] [varchar](200) NOT NULL,
[ProcName] [varchar](100) NOT NULL,
[LastSuccessfulRunEndDate] [datetime] NOT NULL,
[PaddingCount] [int] NOT NULL,
[ActiveFlag] [int] NOT NULL,
[created_dt] [datetime] NOT NULL,
[updated_dt] [datetime] NOT NULL
----------------------------------------------------------------------------
proc running dynamic sql
create proc [dbo].[usp_GenerateLeadsDriverProc]
as
declare @ProcessName [varchar](200),
@ProcName [varchar](100) ,
@LastSuccessfulRunEndDate [datetime],
@PaddingCount int,
@sql varchar(1000)
select ProcessName
, ProcName
, LastSuccessfulRunEndDate
, PaddingCount
, 0 processed
into #ProcessList
from .dbo.usp_GenerateLeadsDriver
where ActiveFlag = 1
set @sql= ''
while exists ( select top 1 * from #ProcessList where processed = 0)
begin
select top 1 @ProcessName = ProcessName
, @ProcName = ProcName
, @LastSuccessfulRunEndDate = LastSuccessfulRunEndDate
, @PaddingCount = PaddingCount
from #ProcessList
where processed = 0
set @LastSuccessfulRunEndDate = dateadd( dd, (datediff(dd, -1,@LastSuccessfulRunEndDate)),'1/1/1900')
set @sql = 'exec '+ @ProcName + ' ''' + convert(varchar(10), @LastSuccessfulRunEndDate, 101) + ''', '+ cast(@PaddingCount as varchar(10))
execute(@sql)
print @sql
update #ProcessList
set processed = 1
where processed = 0
and ProcessName = @ProcessName
and ProcName = @ProcName
end
drop table #ProcessList
June 2, 2010 at 9:08 am
i just thought of something while in bathroom. i suppose i could have each proc set a flag in some table upon sucessful completion that i can check it's value before the next proc kicks off. i'll have to create another loop that loops say 10,000 times max w a 5 second wait then have it check sed value. loop until it finds the proper value meaning the prev proc completed etc. Other ideas would be appreciated.
June 2, 2010 at 9:35 am
not everyone at once here. I can only handle one reply at a time.
i fopund a way to do this using the waitfopr command however just not sure if it's the best way or not.
create proc [dbo].[usp_GenerateLeadsDriverProc]
as
declare @ProcessName [varchar](200),
@ProcName [varchar](100) ,
@LastSuccessfulRunEndDate [datetime],
@PaddingCount int,
@sql varchar(1000),
@loopCount int,
@loopCountUpperBound int
select ProcessName
, ProcName
, LastSuccessfulRunEndDate
, PaddingCount
, 0 processed
into #ProcessList
from .dbo.usp_GenerateLeadsDriver
where ActiveFlag = 1
set @loopCount= 0
set @loopCountUpperBound = 720 -- because there is a 5 second wait in the loop it will try for an hour
set @sql= ''
while exists ( select top 1 * from #ProcessList where processed = 0)
begin
select top 1 @ProcessName = ProcessName
, @ProcName = ProcName
, @LastSuccessfulRunEndDate = LastSuccessfulRunEndDate
, @PaddingCount = PaddingCount
from #ProcessList
where processed = 0
set @LastSuccessfulRunEndDate = dateadd( dd, (datediff(dd, -1,@LastSuccessfulRunEndDate)),'1/1/1900')
set @sql = 'exec '+ @ProcName + ' ''' + convert(varchar(10), @LastSuccessfulRunEndDate, 101) + ''', '+ cast(@PaddingCount as varchar(10))
execute(@sql)
--this loop will check every 5 seconds to see if the prev proc completed.
while (@loopCount < @loopCountUpperBound)
begin
if exists ( SELECT *
FROM [usp_GenerateLeadsDriver] (nolock)
where convert(varchar(10), [LastSuccessfulRunEndDate], 101) = convert(varchar(10), getdate(), 101)
and [ProcessName] = @ProcessName
and [ProcName] = @ProcName
)
begin
set @loopCount = @loopCountUpperBound + 1
end
else
begin
set @loopCount= @loopCount + 1
WAITFOR delay '00:00:05'
end
end --end waint loop
print @sql + ' _Completed'
update #ProcessList
set processed = 1
where processed = 0
and ProcessName = @ProcessName
and ProcName = @ProcName
set @loopCount= 0 --reset inner timed loop
end
drop table #ProcessList
June 2, 2010 at 9:43 am
Why do you do that? An execute statement be it dynamic or not, will "stop" your code, so it executes only 1 thing at a time, no need to implement a wait counter, or any sort of datetime think, just use your homemade cursor, execute the procedure, and delete it from the temp table you have, then run the next one. Or am I missing something?
Cheers,
J-F
June 2, 2010 at 9:46 am
yea. if u notice the actual proc call is dynamic sql. it just kicks off and then moves on. it wont wait for completion to move on. it's dynamic sql because the sprocs to be called are table driven based on active falgs and such
set @LastSuccessfulRunEndDate = dateadd( dd, (datediff(dd, -1,@LastSuccessfulRunEndDate)),'1/1/1900')
set @sql = 'exec '+ @ProcName + ' ''' + convert(varchar(10), @LastSuccessfulRunEndDate, 101) + ''', '+ cast(@PaddingCount as varchar(10))
execute(@sql)
June 2, 2010 at 9:48 am
maybe i'm wrong. i assumed it would continue w/o waiting for proc to finish. i will have to test now to find out for sure
June 2, 2010 at 9:53 am
It WILL wait, until the first exec is done, to start anything else.
EXEC ('WaitFor Delay ''0:00:02''')
Go
EXEC ('Select 1')
GO
EXEC ('WaitFor Delay ''0:00:02''')
GO
EXEC ('Select 2')
Try this piece of code, you will see it waits for completion, even for Dynamic SQL.
Cheers,
J-F
June 2, 2010 at 11:10 am
damn right it does. Serious brain fart on my part. i suppose i'm over thinking. Thanks for clearing that up.........
June 2, 2010 at 11:12 am
No problem man,
I betthe problem is a lot less tough to handle now!
Have a nice one,
Cheers,
J-F
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply