Stored procedures and row versioning

dbashley1
dbashley1 used Ask the Experts™
on
I have a MS SQL 2005 stored procedure that executes 4 other stored procedures.  Each proceedure that executes relies on the completion of the previous in order to perform correctly.  The trouble I'm having is that it appears that procedure 3 doesn't finish or commit it's work before procedure 4 runs.

If I manually execute the procedures in order everything works correctly.

My gut feeling is that this has something to do with the fact that I have row versioning turned on.  

Is there a way to make sure that procedure 3 completes and commits it's data before procedure 4 executes.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
David ToddSenior Database Administrator

Commented:
Hi,

The simple answer is, from the main procedure, can't you add a delay between the procedures?

eg
select 'starting', getdate()

waitfor delay '0:00:10'

select 'finish', getdate()

Regards
  David
Senior SAP CRM Consultant
Commented:
why not specify an output parameter for sproc 3, and wait until you can read that before firing sproc4

eg
say sproc3 has a return parm of @out, set it initially to 0, and on completion, set it to 1
in your calling sproc:

while @out <>0
begin
exec sproc4
end
David ToddSenior Database Administrator
Commented:
Hi,

Nice idea about the output parameter, but the logic is kinda skewed somewhat.

declare @ReturnFrom3 int
Exec @ReturnFrom3 = sproc3

while @ReturnFrom3 is null begin
    -- wait 10 seconds instead of merely doing nothing and
    -- looping like crazy
    -- waits and sleeps should yeild the CPU under NT/2000/2003
    waitfor delay '0:00:10'
end

Exec sproc4

Regards
  David

Author

Commented:
Thanks for the assistance.  Using the combination of your suggestions, I was able to confirm that the problem was not as I suggested.  There was no row versioning issue or any problems with sp3 still running and the system moving on to sp4.  There was just a problem in my code.  Thanks for your help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial