StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POStored Procedure passing control back too quickily - VB6
text
Body
copied!<p>I have a stored procedure that is updating a very large table (with over 100 million records). The stored procedure is updating records in this table.</p> <p>The steps are as follows:</p> <ol> <li>Store record IDs to be updated in a recordset (not all records will be updated - only about 20000)</li> <li>Loop through the recordset and call the stored procedure for each record ID in the recordset</li> <li>Each time the stored procedure has finished (for each record in the recordset mentioned in part 1), update a flag in a table to say that the update completed.</li> </ol> <p>I am finding some strange behaviour. It appears that the stored procedure is passing control back to VB6 before it has completed its updates and is continuing processing the next record. The stored procedure is then timing out later on (on another record ID). Therefore there are flags that say updated (step 3), even though the stored procedure has not run (because it timed out). Is this normal behaviour i.e. for the stored procedure to pass control back to VB6 before it has finished the work?</p> <p>I have Googled this and I have discovered that it could be because of the way the stored procedure is optimised by SQL Server. I would expect control only to be passed back to VB6 after the updates have completed. Is this not the case?</p> <p>Please note that I realise there may be better ways of approaching this. My question specifically relates to SQL Server passing control back to VB6 before it has finished the work (update).</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload