Use a Stored Procedure to return a result set via DataSnap

Question

I have created a DataSnap server along with a client (FireMonkey XE5 - update 2) and can successfully return data from the DataSnap server (which connects to SQL Server 2008). When using a stored procedure though, I receive the following error:

Remote Error: [0x0005]: Operation not supported.

Here is my DATASNAP SERVER function:

Note when using a "SELECT FROM" query, all works perfectly. As soon as I replace it with a stored procedure, I receive an error. The stored procedure definitely exists, and works and returns data.

function TServerMethods.GetUserDetails(UserID: integer) : TDataSet;
var ds : TSQLQuery;
begin
  //Ensure the SQL Connection is open - this works 100%
  if not SQLLocalTest.Connected then
    SQLLocalTest.Connected := true;

  ds := TSQLQuery.Create(self);
  try
    ds.SQLConnection := SQLLocalTest;
    //This produces the error when running the client  
    //The sp is valid and works 100%
    ds.SQL.Text := 'exec spMobileGetUserDetails ' + UserID.ToString();

    //If I replace the above with this, then it all works perfectly 
    ds.SQL.Text := 'SELECT FirstName, Surname, Email FROM Users WHERE UserID= ' + UserID.ToString();
    ds.Open;

    result := ds
  finally
  end;
end;

DataSnap client function:

procedure TfrmTest.Button1Click(Sender: TObject);
var
  d : TClientDataSet;
  sm : TSqlServerMethod;
  dp : TDataSetProvider;
begin
  try
    sm := TSqlServerMethod.Create(Self);
    if not DS_VCL.Connected then
      DS_VCL.Connected := true;
    sm.SQLConnection := DS_VCL;

    sm.ServerMethodName := 'TServerMethods.GetUserDetails';
    sm.Params[0].AsInteger := 1;

    dp := TDataSetProvider.Create(Self);
    dp.Name := 'dspGetUserDetails';
    dp.DataSet := sm;

    d := TClientDataSet.Create(Self);
    d.ProviderName := 'dspGetUserDetails';
    d.Open;  //  <---- ERROR OCCURS HERE WHEN USING STORED PROC

    ShowMessage(d.Fields[0].ToString());
    d.Close;

    FreeAndNil(d);
    FreeAndNil(dp);
    FreeAndNil(sm);

  except on E: exception do
    ShowMessage(e.Message);
 end;
end;

Answer 1

My datasnap server uses Stored procedure calls virtually identical to yours but uses TAdoQueries instead of TSqlQueries and works fine, so any other readers with the same problem should maybe try substituting TAdoQueries.