December 5, 2008 at 2:43 am
Hi Everyone
I have completed an application that works with SQL Server 2005. There is a particular part of the application (when the user clicks the "release & print" button) where a stored procedure is called that updates existing rows and inserts rows into 2 other financial audit tables. This procedure is run once for each "invoice line" In practice, with 1000's of invoices with an average of 20 lines per invoice (in any one day), the stored procedure sometimes does absolutely nothing! It reports no errors, raises no exceptions and writes nothing into the logs. It is as if the stored procedure did not run.
As I cannot get around this, I have put the stored procedure call (from my Delphi 7/ADO) application in a retry loop, when I call the stored proc (eg. exec processinvoiceline LineNo) then immediately after that, I check to see if the proc did it's job (eg. select * from invoicelines where LineNo = x and orderqty <> processqty) If it didn't do it's job, then I call it again, but not before inserting into my own RetryLog..
In practice, out of the 8145 lines that were processed yesterday, 4 were retried once and 1 was retried 3 times before it succeeded!
Yes, believe it or not, this is actually happening! Does anyone have any comments or thoughts about this.
Thanks in advance (This is my first posting to this forum)
December 5, 2008 at 3:04 am
freddie (12/5/2008)
Hi EveryoneI have completed an application that works with SQL Server 2005. There is a particular part of the application (when the user clicks the "release & print" button) where a stored procedure is called that updates existing rows and inserts rows into 2 other financial audit tables. This procedure is run once for each "invoice line" In practice, with 1000's of invoices with an average of 20 lines per invoice (in any one day), the stored procedure sometimes does absolutely nothing! It reports no errors, raises no exceptions and writes nothing into the logs. It is as if the stored procedure did not run.
As I cannot get around this, I have put the stored procedure call (from my Delphi 7/ADO) application in a retry loop, when I call the stored proc (eg. exec processinvoiceline LineNo) then immediately after that, I check to see if the proc did it's job (eg. select * from invoicelines where LineNo = x and orderqty <> processqty) If it didn't do it's job, then I call it again, but not before inserting into my own RetryLog..
In practice, out of the 8145 lines that were processed yesterday, 4 were retried once and 1 was retried 3 times before it succeeded!
Yes, believe it or not, this is actually happening! Does anyone have any comments or thoughts about this.
Thanks in advance (This is my first posting to this forum)
I have also experienced the same problem of sp executioin skipping in the visual basic especially in the sections where Do Events includes.
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
December 5, 2008 at 3:16 am
The Delphi equivalent of VB "do events" is Application.ProcessMessages... and no, I don't do that in my loop or in my application.
December 5, 2008 at 6:10 am
You should be getting a return code from the procedure when it's called initially. Normally, unless you mess with it, it's 0 for success and any other value for failure. I've never programmed in Delphi, but in C# & VB, using ADO.NET, I can always check for that return code. I've never seen an instance where I get the return code but the procedure itself did not execute.
I'd put a trace on the server for a day or two, capture events. When you get one of those logged errors in your app, check the trace and see if the procedure was actually executed.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply