Recent Failures on Delphi TADOStoredProc / D6 and RAD Studio XE2

Question

Thanks to any that can provide some assistance...

Background:

I have an application coded and still supported in Borland Delphi v6. Very recently I have had issues with the TADOStoredProc class failing to execute the stored procedure. This code had previously been stable for several years and had never been modified.

I can configure a timeout on the request, which is respected, however the stored procedure call never runs, even on a very long timeout. The application just hangs, or bails on a timeout exception. (I know the server is not overburdened and responds to other SQL SELECT requests originated by the same client.)

I know D6 is old. I do have a separate environment with Embarcadero RAD Studio XE2, where I managed to build the same project, and have the same issues still. ... Just to make sure.

Where to go?

Please review the code provided, and see if there's a better way to do things. (Maybe the MSSQL interface is more picky, after a recent update?) I certainly welcome recommendations.
Is there an alternate method I can plug into the app, that is reliable and doesn't require TADOStoredProc? I've done my digging, but haven't hit any good examples.

Code Example

function TImport.OpenHeader(DriverID: Integer, …, ScanStart: DateTime, ...): integer;
var
    suid: integer;
    jid: integer;

    con : TADOConnection;
    sp : TADOStoredProc;
begin
    suid := getScanUnitID();
    jid := deriveJobID(ScanStart);

    con := TADOConnection.Create(nil);
    con.LoginPrompt := false;
    con.ConnectionString :=  'Provider=SQLOLEDB.1;Password=<testPwd>;Persist Security Info=True;User ID=<testUser>;Initial Catalog=<myDB>;Data Source=<myServer>';
    con.CommandTimeout := 10;
    con.KeepConnection := true;
    con.Connected := true;

    sp := TADOStoredProc.Create(nil);
    sp.Connection := con;
    sp.CommandTimeout := 10;
    sp.ProcedureName := 'mon4_OpenHeader;1';
    sp.Parameters.Refresh;

    sp.Parameters.ParamByName('@ScanUnitID').Value := suid;
    sp.Parameters.ParamByName('@JobID').Value := jid;
    sp.Parameters.ParamByName('@DriverID').Value := DriverID;
    //[…]

    sp.Parameters.ParamByName('@Result').Direction := pdOutput; //returned from stored proc

    sp.ExecProc;

    Result := sp.Parameters.ParamByName('@Result').Value;
    sp.Free;
    con.Free;
end; // end OpenHeader(DriverID: Integer, …, ScanStart: DateTime, …): integer

Thanks for any help you can provide.

Have you got any errors? Have you tried Profiler to know if the procedure was executed? And have you tried to execute the procedure from ssms? — Igor Borisenko
Thanks on the recommendation. Oddly, SQL Profiler seemed to shake enough around it "worked" this time. The Delphi ADO runtime exceptions thrown were "Timeouts". Running from SSMS had no issues. — user1631866
Thanks on the recommendation. | Oddly, SQL Profiler seemed to shake enough around it "worked" this time. The Delphi ADO runtime exceptions thrown were "Timeouts" of great length. Running from SSMS had no issues. | Strange through that running dynamic SQL through TADOQuery was OK in all instances, unless I ran an "EXEC ..." for a stored procedure call. | Is something "off" on the server? — user1631866
I think, the problem must be in changing of execution plan of the procedure. Try to execute the procedure in SSMS with the same parameter values as in Delphi (you can get them in Profiler) and you should face long execution time, which is cause of timeouts. If it is true than you should improve your procedure performance. — Igor Borisenko
Igor, thanks for the input. I've done some digging and it's the debug components of the rather outdated development platform that have been throwing things off. | Today I found some locking issues on the database. Some other development teams were testing some queries that take hours to run (over a wide-area network), and the shared locks were blocking any update/insert/delete operation against the main tables. I'm still poking at resolutions on these issues, including using "read_committed_snapshot" on the database itself. Still testing tonight. Thanks again. — user1631866

Dan S Dan S · Accepted Answer · 2013-06-21T12:38:56

Try to use SQL Server Native Client 10.0 OLE DB Provider

Provider=SQLNCLI10;Server=myServerAddress;Database=myDataBase;Uid=myUsername;
Pwd=myPassword;

Recent Failures on Delphi TADOStoredProc / D6 and RAD Studio XE2

2 Answers