Setting CommandTimeout after Execution causes error (column appears multiple times)

Question

I have a Delphi project which uses the ADO-Components, currently SQL Server 2017.

I noticed that Microsoft has 3 possible providers , and Microsoft recommends using "Generation 3" for new projects.

  SQLOLEDB (Generation 1) works
  SQLNCLI11 (Generation 2) does NOT work
  MSOLEDBSQL (Generation 3) does NOT work

To test the "Generation 3", I changed the provider in the connection string from SQLOLEDB to MSOLEDBSQL.

However, I noticed that this causes a problem if a column is added using a TAdoCommand inside a transaction, and afterwards, the Timeout is set. The error happens when the timeout is set after the execution.

Here is an example that reproduces the problem:

uses
  ADODB;

procedure TForm2.Button1Click(Sender: TObject);
const
  // SQLOLEDB (Generation 1) works
  // SQLNCLI11 (Generation 2) does NOT work
  // MSOLEDBSQL (Generation 3) does NOT work
  SqlServerProvider= 'MSOLEDBSQL';
var
  mConnection: TADoConnection;
  command: TadoCommand;
begin
  mConnection := TAdoConnection.Create(nil);
  try
    mConnection.ConnectionString := 'Provider='+SqlServerProvider+';Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=CORAMASTER_1;Data Source=SHS\HS2017,49010';

    mConnection.KeepConnection := true;
    mConnection.LoginPrompt := false;

    mConnection.Connected := true;

    mConnection.BeginTrans;

    command := TADOCommand.Create(nil);
    try
      command.Connection := mConnection;
      command.ParamCheck := false;
      command.CommandText := 'alter table TESTTABLE add TESTCOLUMN int;';
      command.CommandTimeout := 100;
      command.Execute;

      // If I set "CommandTimeout" here, I get the following error:
      //        Spaltennamen müssen in jeder Tabelle eindeutig sein. Der Spaltenname "..." wurde in der ...-Tabelle mehrmals angegeben.
      //        Translated: Column names must be unique in each table. The column name "..." appears multiple times.
      // The error happens with provider MSOLEDBSQL (Generation 3) and SQLNCLI11 (Generation 2), but SQLOLEDB (Generation 1) works
      command.CommandTimeout := 50; // ERROR!
    finally
      FreeAndNil(command); // Note: In the real project, I am keeping the command object. This is just for the example
    end;

    mConnection.RollbackTrans;

  finally
    FreeAndNil(mConnection);
  end;

end;

What am I doing wrong? Is this an error in the ADO components?

Edit: Here is a trace log of the SQL server using the Microsoft SQL Server Profiler:

Generation 1 (works):

Generation 2:

Generation 3:

When I perform the second "SetTimeout" command, the SQL server performs the command exec [sys].sp_describe_first_result_set N'alter table TESTTABLE add TESTCOLUMN int;',NULL,1 which obviously causes the error. I have no idea why sp_describe_first_result_set is called and how to prevent it...

Which Delphi version? Istr that there was a problem setting ADO timeouts in Delphi a long time ago (around D3/D5 era). — MartynA
I am using Delphi 2007 (I know it is very old, but unfortunately, I depend on old VCLs/Frameworks that can't be updated) — Daniel Marschall
Why do you need to set CommandTimeout after command executed? — sddk
I know nothing about that Delphi - but i see a connection created but never opened. Hard to believe that mConnection.Connected := true; actually opens the connection. And TRY without CATCH makes no sense. — SMor
@SMor: Both your comments are incorrect, I'm afraid. In Delphi, setting the Connected property to True is a perfectly valid way of opening a connection (the actual action takes place in the setter of the property) and it has two try constructs, try ... except for exception-handling and try ...finally for resource protection. — MartynA

Daniel Marschall Daniel Marschall · Accepted Answer · 2020-08-26T14:10:37

I have found the problem.

Microsoft describes at Using Connection String Keywords with OLE DB Driver for SQL Server :

Use FMTONLY

Controls how metadata is retrieved when connecting to SQL Server 2012 (11.x) and newer. Possible values are true and false. The default value is false.

By default, the OLE DB Driver for SQL Server uses sp_describe_first_result_set and sp_describe_undeclared_parameters stored procedures to retrieve metadata. These stored procedures have some limitations (for example, they will fail when operating on temporary tables). Setting Use FMTONLY to true instructs the driver to use SET FMTONLY for metadata retrieval instead.

If I execute mConnection.Execute('set fmtonly on') , then everything works.

In the profiler screenshots (above) I can see, that the Generation-1 driver actually executes these commands very often.

Edit: I noticed that set fmtonly on does not work for me. For some reason, various things do not work, e.g. all result sets are empty.

But I have found a very interesting thing about the initial problem! Setting CommandTimeout a second time does ONLY crash if it is called inside the same function call! If I call CommandTimeout immediately afterwards, but inside a new function call, then everything works!

Here is an example:

procedure CausesBug;
begin
  command.CommandTimeout := 100;
  command.ParamCheck := false;
  command.CommandText := 'alter table TESTTABLE add TESTCOLUMN'+inttostr(Random(10000))+' int;';
  command.Execute;

  command.CommandTimeout := 100; // Crash!
  // This calls: exec [sys].sp_describe_first_result_set N'alter table TESTTABLE add TESTCOLUMN2729 int;',NULL,1
end;

procedure CausesNoBug;
  procedure Test1;
  begin
    command.CommandTimeout := 100;
    command.ParamCheck := false;
    command.CommandText := 'alter table TESTTABLE add TESTCOLUMN'+inttostr(Random(10000))+' int;';
    command.Execute;
  end;
begin
  Test1;
  Test1; // Although CommandTimeout is the first command, everything works!
         // "sp_describe_first_result_set" is NOT called!
  Test1;
  Test1;
end;

And also:

procedure AlsoCausesNoBug;
  procedure Test1;
  begin
    command.ParamCheck := false;
    command.CommandText := 'alter table TESTTABLE add TESTCOLUMN'+inttostr(Random(10000))+' int;';
    command.Execute;
  end;
begin
  command.CommandTimeout := 100;
  Test1;
  command.CommandTimeout := 100;
end;

Note: I have also tried disabling compiler optimization, to make sure that there are no issues there.

Setting CommandTimeout after Execution causes error (column appears multiple times)

1 Answers