When is a Stored Procedure Complete?

Question

When is a Stored Procedure Complete?

Tom Waitman

SSC Veteran

Points: 224
More actions
August 22, 2007 at 7:11 pm

#118318

I have a stored procedure that updates a number of records in a table. As soon as I run the stored procedure, I then open a view to retrieve the updated data. This code is all writting in VB using ADODB. I am finding that from time to time, the view retrieves data that has not yet been updated. Hence I need a way to know for sure that the stored procedure has finished writing the records.
I imagine this is a pretty novice question, but one I can't seem to find the answer to. Any advice would be very much appreciated.
Tom

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

As for know the SP is complete it depends on where you execute the Stored Procedure from, but let's say you do it in QA. When the Proc starts unless explicit, data changes will issue an implicit TRANSACTION and commit once all steps are complete. In QA you will see the message batch complete and know all work is done. Other methods of firing the SP such as an ADO process have ways of seeing the completion message as long as you have code to catch it and notify you.

Now while your Proc is in a transaction all changes are written to the transaction log and patially to the table (ghost records) until commited then the actual DB is altered to reflect the finalization of the change. Becuase of this when you run a query while the transaction is still in progress you only see the commited data. You can, however use query hint such as NOLOCK or other methods to allow you to see the uncommitted data, called a dirty read, but you may not actually want to do this for reporting and things like that which is why by default uncommited data reads are not allowed.

Hopefully that explains it well an correctly enough as I am tired an it is late. If not just say you don't understan and I or someone else will try to explain better or point to some reference material which I don't have a good example right now.

David McFarland SSChampion Points: 11815 More actions · Answer 2

Assuming you're using the Execute method of the Command object, is it possible that you've added the parameter to make the execution asynchronous? If so, removing that should take care of it.

Jeff Moden SSC Guru Points: 1004250 More actions · Answer 3

Are you doing this using embedded SQL in your VB code? If you are, that is likely the problem...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)