Schedule with critical paths

Transact-SQL
1

I have a table in which there are rows with start and end dates. Each item has a dependent task where its start date is calculated from. Below is a snip of some of the table rows. I need a statement that will update all the dependent tasks of the whole table when a start date or duration changes. I have this in asp.net using recursive functions but it takes about 45 seconds to update and i need something that does not use the client to recalculate but needs to remain in the database. I have tried recursive stored procs but it exceeds the recursion limit. Any ideas?

2

have you tried Recursive CTE with maxrecursion ?

; 
with rcte as
(
    select   *
    from     tbl
    where    DepTaskid    = 0
    and      TaskId        = @TaskId        

    union all

    select   t.*
    from     tbl t
             inner join rcte r    on    t.DepTaskid    = r.TaskId
)
update   t
set      StartOn    = @newStartOn
from     rcte r
         inner join tbl t    on    r.TaskId    = t.TaskId
option  (maxrecursion 0)
3

@jjaroska,

If you'd take a little time to provide the CREATE TABLE statement for the table you displayed and INSERT statements for the data (readily consumable data), we could actually provide you with code that's been test (although khtan's code looks to do the trick)