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.

Counterword

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? – Fielding

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. – Counterword

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? – Counterword

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. – Fielding

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. – Counterword

You are welcome. Thank you for posting your solution. – Fielding

The "other team" is the problem. you can ask them to do the test on a dev copy, not on your production db. – Rubbery

Answer 1

Try to use SQL Server Native Client 10.0 OLE DB Provider

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

Fourdrinier

Answer 2

You can try to simply remove that .1 after SQLOLEDB because it is only to specify the version number to use.

con.ConnectionString :=  'Provider=SQLOLEDB;Password=<testPwd>;Persist Security Info=True;User ID=<testUser>;Initial Catalog=<myDB>;Data Source=<myServer>';

You should consider to switch to newer SQLNCLI driver.

You have not specified the windows server version nor the sql server version nor the client windows version but:
SQLOLEDB should be present also in newer systems for backward compatibility;
SQLNCLI should come with SQL Server 2005;
SQLNCLI10 should come with SQL Server 2008;
SQLNCLI11 should come with SQL Server 2012 and 2014;
SQLNCLI13 should come with SQL Server 2016;

Pay attention to 32/64bit version of drivers because to talk with 32bit sql server you need 32 bit driver and vice versa.

Be sure to have the right driver installed on your clients.

Microsoft® SQL Server® 2016 Feature Pack
Windows 8, 8.1, 10, Windows Server 2012, 2012 R2, 2016
https://www.microsoft.com/en-us/download/details.aspx?id=52676
You will find both x86/x64 versions of sqlncli.msi

Microsoft® SQL Server® 2012 Native Client
Windows 7, 8, 8.1, 10, Windows Server 2008 R2, 2012, 2012 R2
https://www.microsoft.com/en-us/download/details.aspx?id=50402
You will find both x86/x64 versions of sqlncli.msi

Microsoft® SQL Server® 2008 R2 Native Client
Windows Vista, XP, 7, Windows Server 2003, 2008, 2008 R2
x86 Package: http://go.microsoft.com/fwlink/?LinkID=188400&clcid=0x409
x64 Package: http://go.microsoft.com/fwlink/?LinkID=188401&clcid=0x409

Pay also attention to OLEDB/ODBC lifecycle , OLEDB was declared deprecated to switch to newest ODBC drivers, but last October it was redeclared undeprecated.

Revolute