Opening MSSQL TFDTable with DECIMAL types: 'Connection is busy with results for another hstmt' when FetchOptions.Items contains fiMeta

Question

If I open a MSSQL table containing DECIMAL/NUMERIC data types I get the 'Connection is busy with results for another hstmt' error. Tracing the FireDAC code I see that this happens when it fetches meta information, see the stack trace:

I understand that the error is about multiple open connections to the ODBC database, possibly because the dataset does not retrieve all data at once (and remains open), but what I do not understand is:

• Why does it happen only with tables containing DECIMAL fields? Other tables work just fine, small or large, I can open many in grids on multiple tabs at the same time, even edit the data.
• Why is the metadata relevant? If I do FetchOptions.Items := FetchOptions.Items - [fiMeta]; no errors occur. Is this a bug in FireDAC?

We explicitly have FetchOptions.Mode := fmAll (as the only fetch option) on the TFDConnection and no override of the options in the TFDTable.

This is in a small test app, and I'm (currently) not comfortable with the [fiMeta] exclusion in the code: I will now have to retest our main apps that nothing falls over because of this exclusion.

Are there other ways to fix this issue?
Would enabling MARS in my TFDConnection help? How do I do that?

I have used TFDMoniFlatFileClientLink to check what's going on regarding MARS. Whether my code uses a runtime assignment TFDConnection.DriverName := 'MSSQL', or I set the DriverName at design time (and then Params.MARS appears and is checked), this is what I see in the trace:

SQLDriverConnect [szConnStr="DRIVER=SQL Server;UID=test;PWD=****;Server=VS2003-2008;Database=test"]

If I explicitly add Params.Add('MARS=No'); this changes to:

SQLDriverConnect [szConnStr="DRIVER=SQL Server;UID=test;PWD=****;Server=VS2003-2008;Database=test;MARS_Connection=No"]

... which makes me wonder if MARS is active or not.

If I explicitly add Params.Add('MARS=Yes'); this changes to:

SQLDriverConnect [szConnStr="DRIVER=SQL Server;UID=test;PWD=****;Server=VS2003-2008;Database=test;MARS_Connection=Yes"]

... but the problem does not go away.

Background info:

• According to Browsing Tables (FireDAC) we operate in Live Data Window Mode because CachedUpdates, FetchOptions.UniDirectional and FetchOptions.CursorKind have their default values
• Delphi Tokyo 10.2.3, Win 32 app
• Must support SQL Server 2005 and up
• It happens with or without the use of mapping rules for the dtBCD and dtFmtBCD field type
• There was an Embarcadero forums thread about this.
• DelphiPraxis post here, no solution.

Test table:

CREATE TABLE dbo.TESTDEC
(
  TT_ID INTEGER NOT NULL CONSTRAINT TT_C0_TEST DEFAULT 0,
  TT_DEC DECIMAL NULL,
  TT_NUM NUMERIC NULL,
  TT_DEC5_0 DECIMAL(5,0) NULL,
  TT_NUM5_0 NUMERIC(5,0) NULL,
  TT_DEC5_3 DECIMAL(5,3) NULL,
  TT_NUM5_3 NUMERIC(5,3) NULL,
  TT_DEC15_0 DECIMAL(15,0) NULL,
  TT_NUM15_0 NUMERIC(15,0) NULL,
  TT_DEC15_3 DECIMAL(15,3) NULL,
  TT_NUM15_3 NUMERIC(15,3) NULL,
  TT_DEC25_0 DECIMAL(25,0) NULL,
  TT_NUM25_0 NUMERIC(25,0) NULL,
  TT_DEC25_3 DECIMAL(25,3) NULL,
  TT_NUM25_3 NUMERIC(25,3) NULL,
  TT_DEC35_0 DECIMAL(35,0) NULL,
  TT_NUM35_0 NUMERIC(35,0) NULL,
  TT_DEC35_3 DECIMAL(35,3) NULL,
  TT_NUM35_3 NUMERIC(35,3) NULL
);

insert into dbo.testdec values(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1);
insert into dbo.testdec values(2,2,2,2,2,2.22,2.22,2,2,2.22,2.22,2,2,2.22,2.22,2,2,2.22,2.22);
insert into dbo.testdec values(3,33333,33333,33333,33333,33.333,33.333,33333333333,33333333333,33333333333.333,33333333333.333,33333333333,33333333333,33333333333.333,33333333333.333,33333333333,33333333333,33333333333.333,2.22);

FULL SAMPLE PROGRAM SOURCE
Add FDConnection.Params.Add('MARS=Yes'); or FDConnection.FetchOptions.Mode := fmAll; to the FormShow if you want; they make no difference.

.dpr file:

  program BrowseSQLDecimal;

  uses
    Forms,
    uTest in 'uTest.pas' {FrmTest};

  {$R *.res}

  begin
    Application.Initialize;
    Application.Title := 'Browse DB';
    Application.CreateForm(TFrmTest, FrmTest);
    Application.Run;
  end.

Form .pas:

unit uTest;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Data.DB, Vcl.StdCtrls,

  FireDAC.Stan.Intf, FireDAC.Stan.Option,
  FireDAC.Stan.Error, FireDAC.UI.Intf, FireDAC.Phys.Intf, FireDAC.Stan.Def,
  FireDAC.Stan.Pool, FireDAC.Stan.Async, FireDAC.Phys, FireDAC.Phys.MSSQL,
  FireDAC.Phys.MSSQLDef, FireDAC.VCLUI.Wait, FireDAC.Stan.Param, FireDAC.DatS,
  FireDAC.DApt.Intf, FireDAC.DApt, FireDAC.Comp.DataSet,
  FireDAC.Comp.Client, FireDAC.Moni.Base, FireDAC.Moni.FlatFile;

type
  TFrmTest = class(TForm)
    Button1: TButton;
    Button2: TButton;
    FDConnection: TFDConnection;
    TableTT_ACT: TFDTable;
    TableTESTDEC: TFDTable;
    procedure FormShow(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
  private
  public
  end;

var
  FrmTest: TFrmTest;

implementation

{$R *.dfm}

procedure TFrmTest.Button1Click(Sender: TObject);
begin
   TableTT_ACT.TableName := 'TT_ACT';
   TableTT_ACT.Open;
end;

procedure TFrmTest.Button2Click(Sender: TObject);
begin
   TableTESTDEC.TableName := 'TESTDEC';
   TableTESTDEC.Open;
end;

procedure TFrmTest.FormShow(Sender: TObject);
begin
   FDConnection.Open;
end;

end.

Form .dfm:

object FrmTest: TFrmTest
  Left = 0
  Top = 0
  Caption = 'Test'
  ClientHeight = 276
  ClientWidth = 560
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'Tahoma'
  Font.Style = []
  OldCreateOrder = False
  OnShow = FormShow
  PixelsPerInch = 96
  TextHeight = 13
  object Button1: TButton
    Left = 32
    Top = 24
    Width = 97
    Height = 25
    Caption = 'Open TT_ACT'
    TabOrder = 0
    OnClick = Button1Click
  end
  object Button2: TButton
    Left = 32
    Top = 56
    Width = 97
    Height = 25
    Caption = 'Open TESTDEC'
    TabOrder = 1
    OnClick = Button2Click
  end
  object FDConnection: TFDConnection
    Params.Strings = (
      'MonitorBy=FlatFile'
      'DriverID=MSSQL'
      'User_Name=test'
      'Password=test'
      'Database=test'
      'Server=VS2003-2008')
    Left = 224
    Top = 24
  end
  object TableTT_ACT: TFDTable
    Connection = FDConnection
    Left = 72
    Top = 149
  end
  object TableTESTDEC: TFDTable
    Connection = FDConnection
    Left = 72
    Top = 213
  end
end

Answer 1

This is a driver issue.

My Windows 7 development machine only has the (default?) 32-bits ODBC driver named SQL Server, version 6.01.7601.17514

If I install a later native client or ODBC driver there are no issues.

Another workaround (as mentioned in the question) would be to change FetchOptions.Items := FetchOptions.Items - [fiMeta]; for the TFDTable (don't do it for the TFDConnection, it's not neccessary for other database components using that connection).

Notes:

1. Tested with:

   • SQL Server Native Client 10
   • SQL Server Native Client 11
   • Microsoft ODBC Driver 11 for SQL Server
   • Microsoft ODBC Driver 13.1 for SQL Server

2. This does not work with Microsoft ODBC Driver 17 for SQL Server, because the FireDAC code does not detect that driver yet. TFDPhysODBCDriverBase.FindBestDriver does string comparisons for the names of the drivers and the latest hardwired SQL Server driver strings in the code are ODBC DRIVER 13 FOR SQL SERVER and SQL SERVER NATIVE CLIENT 11.0. ^1,2

3. Use the x64 versions of these downloads; they will (also) install 32-bits ODBC drivers.

4. This is about drivers installed on the machine running the program, i.e. regardless of what is installed on the database servers.

5. I remain with one question: Isn't this a FireDAC bug, actually? Why does the FireDAC code execute a TFDPhysCommandAsyncOpen (see stack dump in question) instead of doing it synchronous?

<sup>1. Neither is SQL Server Native Client 9 present
2. Request done</sup>