Insufficient memory when opening TClientDataSet wi

Question

My Delphi code opens a TFDQuery (FireDAC), then opens the TClientDataSet connected to it via a TDataSetProvider:

ClientDataSetData.Close;
with QueryData do
begin
  Close;
  SQL.Clear;
  SQL.Add(ASelectSQL);
  Open;
end;    
ClientDataSetData.Open;

ASelectSQL contains this SQL:

SELECT TT_NIV_ID,
TT_NIV,
REPLACE(TT_NIV_NAME, '|', '!') as TT_NIV_NAME2
FROM TT_SYS_PRJ_NIV

The ClientDataSetData.Open gives an insufficient memory error on a dataset with 42200 records.

If I inspect the result data (in the Delphi code) I see that TT_NIV_NAME2 is a string of length 8000!
From the REPLACE() documentation:

If string_expression is not of type varchar(max) or nvarchar(max), REPLACE truncates the return value at 8,000 bytes

... so this seems to be happening; not just truncating but also setting the result type.

TT_NIV_NAME is a VARCHAR(50), so a quick fix is to change the SQL to

SELECT TT_NIV_ID,
TT_NIV,
CAST(REPLACE(TT_NIV_NAME, '|', '!') as VARCHAR(50))  as TT_NIV_NAME2
FROM TT_SYS_PRJ_NIV

but this is with user defined SQL that I have no control over. Next time (s)he may run a with a REPLACE on something else... I'd prefer a generic solution.

Is there anything I can do to the Delphi code (TClientDataset or TFDQuery or TFDConnection settings?) that prevents this high memory usage?

And frankly, why would REPLACE conclude that "string_expression is not of type varchar(max)", when TT_NIV_NAME is a VARCHAR(50)?

Notes:

• Tested with several SQL server drivers - it's not a driver issue.
• The TFDConnection has FetchOptions.Mode := fmAll and FetchOptions.Items := FetchOptions.Items - [fiMeta];
  fmAll must stay^*, and not subtracting fiMeta makes no difference.
• Related [1] but here string_expression is longer than 8000, or [2] where string_expression is not of type varchar(max) but both do not apply to my SQL data.
• Delphi Tokyo 10.2.3 using FireDAC against SQL Server 2012, Win32 app running under Win7 or Win10

^{* .. to prevent the famous hstmt error in an environment where only very basic SQL Server drivers are installed; and not setting it makes no difference anyway in my test app.}

Answer 1

We have 'solved' this by applying a mapping rule specifically for this 8000 character ANSIstring. I know, it's dirty, but for the legacy app it works (especially since we were using SQLDirect before switching to FireDAC, and there varchars > 256 bytes were translated to memo already).

with FDConnection.FormatOptions.MapRules.Add do
begin
  SourceDataType := dtAnsiString;
  SizeMin := 8000;
  SizeMax := 8000;
  TargetDataType := dtMemo;
end;

This mapping is specific to SQL Server, because the REPLACE function in the other two database types we support (Oracle, FireBird) does not have the SQL Server behavior.