TMSStoredProc and NVARCHAR(MAX)

[krhdevart]

I have an MS SQL output variable that's NVARCHAR(MAX) and it works fine when I test it in a backend call and I know it would work find in any vb.net code I'd write because I've done this many times there but when I try to get its value using TMSStoredProc I get nothing. It comes back as a zero length string. If I change it to NVARCHAR(1000) on the backend I get a correct result but I don't want that. I want NVARCHAR(MAX). On the front end I do exactly as in the SDAC example code for getting an output variable from a stored procedure. What do I need to do to be able to handle "MAX" with TMSStoredProc?

Thanks,

Keith

[Stellar]

Hi,

Unfortunately, we couldn't reproduce the issue. To investigate this behavior of SDAC, please compose a small sample demonstrating the issue and send it to us, including database objects creating scripts.
You can send the sample using the contact form at our site: devart.com/company/contactform.html

Best regards,
Sergey

[krhdevart]

Just in case this helps you answer this (before I take the time to create an entire sample project), here's my front end code. When @iudErrors in the backend is NVARCHAR(MAX) (OUTPUT variable) I get what I explained in my OP. I tried "AsWideString" as well but same result.

sp.Execute;
iudErrors := sp.Params.ParamByName('@iudErrors').AsString;
if iudErrors <> '' then
begin
Windows.MessageBox(Self.Handle, PChar(iudErrors), PChar(sAppTitle), MB_ICONEXCLAMATION + MB_OK);
end;

Does this help you at all? This seems like it should be something simple. I'm using Delphi 10.2 and SDAC 8.0.5

[paweld]

I don't confirm:

Code: Select all

uses
  MSAccess;
  
procedure TForm1.Button1Click(Sender: TObject);
var
  conn: TMSConnection;
  q: TMSQuery;
  sp: TMSStoredProc;
  i: Integer;
begin
  Memo1.Lines.Clear;
  conn := TMSConnection.Create(nil);
  conn.Server := '.';
  conn.Database := 'testdb';
  conn.Username := 'sa';
  conn.Password := '123';
  conn.Connect;
  q := TMSQuery.Create(nil);
  q.Connection := conn;
  q.SQL.Add(' if exists (select 1 from sys.procedures where name=''proc_test_max'') ');
  q.SQL.Add(' drop procedure proc_test_max ');
  q.ExecSQL;
  q.SQL.Clear;
  q.SQL.Add(' create procedure proc_test_max @repeat int, @text nvarchar(max) output ');
  q.SQL.Add(' as ');
  q.SQL.Add(' begin ');
  q.SQL.Add(' declare @i int=0 ');
  q.SQL.Add(' select @text='''' ');
  q.SQL.Add(' while @i<@repeat ');
  q.SQL.Add(' select @text=@text+''sample text '', @i=@i+1 ');
  q.SQL.Add(' end ');
  q.ExecSQL;
  sp := TMSStoredProc.Create(nil);
  sp.Connection := conn;
  sp.StoredProcName := 'proc_test_max';
  sp.ParamByName('repeat').AsInteger := 500;
  sp.Execute;
  for i := 0 to sp.Params.Count - 1 do
  begin
    Memo1.Lines.Add('Param index: ' + IntToStr(i));
    Memo1.Lines.Add('Param name: ' + sp.Params[i].Name);
    Memo1.Lines.Add('Param value: ' + sp.Params[i].Text);
    Memo1.Lines.Add('====================================');
  end;
  sp.Free;
  q.SQL.Clear;
  q.SQL.Add(' if exists (select 1 from sys.procedures where name=''proc_test_max'') ');
  q.SQL.Add(' drop procedure proc_test_max ');
  q.ExecSQL;
  q.Free;
  conn.Disconnect;
  conn.Free;
end;

Tested on Lazarus 2.3 with FPC 3.2.2 x86, Windows 10 x64, SDAC 10.0.2 and MSSQL 2017

[krhdevart]

I figured it out. The way you set up your connection object gave me an idea. I have a TMSConnection object (for new things) on my main data form along with my original TADOConnection object. I was using the same connection string for both of them. The TMSConnection object connected just fine and most things worked fine but something in my connection string made TMSConnection unhappy so I simplified it and it solved the entire problem.