June 14, 2009 at 3:03 am
Hello,
I'm running a stored procedure which is completed successfully.
One part of it does update to a table and right after it it inserts the just updated rows to another table.
But not always... sometimes (which I can't find the scenario) the rows don't inserted into the second table like they weren't updated.
My question is if it might be that the SP moves to the insert statement before the update finishes and if so, how can I avoid it.
Thanks,
Ran
June 14, 2009 at 3:24 am
This should not happen. If you post this UPDATE/INSERT part of your procedure, the table DDL and some sample data we could help much better.
Flo
June 14, 2009 at 11:22 am
Hello Ran,
When you say the SP “completed successfully”, are you trapping all errors e.g. with Try and Catch blocks? It is possible for SPs to run through to the end even if errors occur.
Could you add some instrumentation to the SP? For example writing the SP parameters and @@Row_Count values to an Audit table? This would at least give you some diagnostics to help analyse the underlying cause.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
June 15, 2009 at 12:55 pm
Also, if the issue is intermittent you could setup a small table to log any errors. In the catch block set an insert into the logging table for ERROR_NUMBER() and ERROR_MESSAGE(). If your procedure fails to copy the expected rows, query your logging table for any error details.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply