Stored procedure doesn't run sometimes, produces no results or errors

Question

Stored procedure doesn't run sometimes, produces no results or errors

freddie-897605

SSC Enthusiast

Points: 109
More actions
December 5, 2008 at 2:43 am

#94351

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)

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

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

krayknot SSCertifiable Points: 7998 More actions · Answer 1

freddie (12/5/2008)
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)

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

freddie-897605 SSC Enthusiast Points: 109 More actions · Answer 2

The Delphi equivalent of VB "do events" is Application.ProcessMessages... and no, I don't do that in my loop or in my application.

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 3

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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software