SQL Server: stored procedure and CLR Proc return data differently

Question

I'm using Delphi XE2 to call a stored procedure that returns data. The stored procedure is basically:

set nocount on -- ensures that Delphi will see that dataset
...
exec StoredProcThatReturnsData
...
update [table]
update [another table]

When the main procedure is a T-SQL procedure, it finishes executing the main procedure including the last update statements before it returns the data to Delphi.

When the main procedure is a CLR procedure, it returns the data at the moment the sub-procedure finishes executing and doesn't run the update statements until I close the TSQLQuery object in Delphi.

The Delphi code looks like this:

rpt := TSQLQuery.Create(nil);
try
  rpt.MaxBlobSize := -1;
  rpt.SQLConnection := ASqlConnection;
  rpt.SQL.Text := 'exec RunMainProc';
  rpt.Open;
  // do some things that depend on the update statements
  // process the 'rpt' data
finally
  FreeAndNil(rpt);
end;

The CLR proc looks like this:

[Microsoft.SqlServer.Server.SqlProcedure]
public static void RunReport(SqlGuid ReportID, SqlGuid UserID, SqlBoolean ForDownload)
{
  using (SqlConnection con = new SqlConnection("context connection=true"))
  {
    con.Open();
    using (SqlCommand cmd = new SqlCommand())
    {
      cmd.Connection = con;
      cmd.CommandText = "[identical SQL as T-SQL proc]";
      SqlContext.Pipe.ExecuteAndSend(cmd);
    }
  }
}

Why does the T-SQL procedure finish executing before returning the data while the CLR procedure waits to run the update statements until after Delphi closes/destroys the rpt query?

What happens in .NET when you do a `exec RunMainProc` there? — Jeroen Wiert Pluimers, Sep 28 '12 at 09:39
@JeroenWiertPluimers: Thanks for pointing me in that direction. It helped me discover the issue. — James L., Sep 28 '12 at 15:45
You are welcome (: Every time when I loose things or break things I ask myself what did I do last... — Jeroen Wiert Pluimers, Sep 29 '12 at 20:31

score 1 · Accepted Answer · answered Sep 28 '12 at 15:59

Thanks to Jeroen, I tried running things from somewhere besides Delphi and found the issue.

When I was converting the T-SQL procedures to CLR, I added some debug code to the sub-procedure, which made it return two datasets instead of one. It was the second pending dataset that caused the CLR version of the procedure to hand back the data before the main procedure finished executing.

Once I removed the debug code (so only one dataset was generated), the T-SQL and CLR versions of the procedure ran exactly the same -- the main procedure finished running before the data was handed back to Delphi.

score 0 · Answer 2 · answered Sep 28 '12 at 10:25

Does your SP return records or not ? It seems ti does not. Then you should not call Open on Query. Open prepares DataSet to fetch data rows, and since requests can go long - it fetches them on demand. When you call TDataSet.Next and TDataSet.Last and such. So i think, if you want to sync with SP after .Open you can call rpt.Last.

But correct way would be not use Open at all and use ExecSQL instead.

http://docs.embarcadero.com/products/rad_studio/delphiAndcpp2009/HelpUpdate2/EN/html/delphivclwin32/SqlExpr_TSQLQuery_ExecSQL.html

PS: in C# you call SqlContext.Pipe.Execute, not Open. And same expected to apply to Delphi, which in some sense was model for .Net and OLE DB

The main stored procedure returns the data that is pulled from the sub-procedure. So I do need to use `Open` in Delphi. Will try the `rpt.Last` though. — James L., Sep 28 '12 at 15:52

SQL Server: stored procedure and CLR Proc return data differently

2 Answers2