FireDAC, Array DML, SQL Server, and IDENTITY_INSERT

Question

I'm trying to export data to SQL Server using Firedac Array DML feature. In the destination table, there is a IDENTITY column, and I need to put an explicit value to it.

But my query fails with the following error message:

SQL state: 23000. Native code: 544. Message: [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot insert explicit value for identity column in table 'dic_cities' when IDENTITY_INSERT is set to OFF.

The destination table is defined as follows:

CREATE TABLE dbo.dic_cities (
  id int IDENTITY(1,1) PRIMARY KEY,
  city_name varchar(50) NOT NULL
)

My code:

MyFDQuery.Connection.ExecSQL('SET IDENTITY_INSERT dbo.dic_cities ON');  

MyFDQuery.SQL.Text := 'INSERT INTO dbo.dic_cities (id, city_name) VALUES (:id, :city_name)';  

//Populating parameters and preparing the query
{...}

//Execute Array DML query with batch size of 100 
MyFDQuery.Execute(100, 0);  

//Finally, set IDENTITY_INSERT off for the destination table
MyFDQuery.Connection.ExecSQL('SET IDENTITY_INSERT dbo.dic_cities OFF');  

I must to note, that everything works well when I use a regular TFDQuery with parameters (i.e. when not using Array DML feature). But it fails for Array DML.

I also used Array DML for several other DBMS for years in a way like the code above, with success.

So, how to use the Array DML feature to insert explicit values to SQL Server IDENTITY column?

Answer 1

Update#2: See https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e652377d-0607-45ca-b4a0-274361bff85a/how-to-set-identityinsert-in-dynamic-sql?forum=transactsql

I haven't fully digested it yet, but the OP's problem seems very similar.

I've tried constructing the SQL to use EXEC

  FDQuery1.SQL.Text := 'EXEC (' + ''''+ sSetIIOn + ';' + sInsert + ';' + sSetIIOff + '''' + ')';

to avoid sp_executesql being used, but unfortunately FD then cannot parse the SQL properly so it produces an "argument out of range" error when setting up the parameters.
Update: Curiouser and curiouser...

The following code executes without error on SS2014 and inserts the expected 10 rows:

const
  sEmptyTable = 'delete from dbo.identtest';
  sSetIIOn = 'set identity_insert dbo.identtest ON';
  sSetIIOff = 'set identity_insert dbo.identtest OFF';
  sSelect = 'select * from dbo.identtest';
  sInsert = 'insert dbo.identtest (ID, Name) values(%d, %s)';

procedure TForm2.TestIdentityInsert;
var
  i : Integer;
  S : String;
begin
  FDQuery1.ExecSql(sEmptyTable);
  FDQuery1.ExecSql(sSetIIOn);

  for i := 1 to 10 do begin
    S := Format(sInsert, [i, '''Name' + IntToStr(i) + '''']);
    FDQuery1.ExecSQL(S);
  end;

  FDQuery1.ExecSql(sSetIIOff);
  FDQuery1.Sql.Text := sSelect;
  FDQuery1.Open;
end;

procedure TForm2.Button1Click(Sender: TObject);
begin
  TestIdentityInsert;
end;

However, replacing the for loop by

  FDQuery1.SQL.Text := sSetIIOn + ';' + sInsert + ';' + sSetIIOff;

  FDQuery1.Params.ArraySize := Rows;
  for i := 0 to Rows - 1 do begin
    FDQuery1.Params[0].AsIntegers[i] := i;
    FDQuery1.Params[1].AsStrings[i] := 'Name' + IntToStr(i);
  end;

produces the exception you quote. I've verified using SSMS Profiler that the SQL sent to the server seems to be correct (and not f.i. being mangled by the MDac layer as sometimes happens):

exec sp_executesql N'set identity_insert dbo.identtest ON;insert dbo.identtest values(@P1, @P2);set identity_insert dbo.identtest OFF',N'@P1 int,@P2 nvarchar(4000)',0,N'Name0' [etc, repeated 9 times]

so the question seems to be why doesn't using sp_executesql respect the Identity_Insert setting and is there another way that does?