Retrieving stored proc info from SQL Server

Using ADO, Delphi 5, I am writing a simple interface for administering MSDE,
since the SQL Server Enterprise manager cannot be distributed with it. I am
running into 2 basic issues that I can't seem to resolve. Both are related
to retrieving information from stored procedures.

First, I want to be able to retreive the SQL Server messages returned by the
server, such as PRINT statements, errors, warning, etc. In the group, I got
a reference to the following,1410,20245,00.html where I found the
following code:

procedure TForm1.Button1Click(Sender: TObject);
  MyReturn : Integer;
  looper : Integer;
  MyRecordSet : _RecordSet;
  ADOCommand1.Prepared := True;
  ADOCommand1.Parameters.ParamByName('@param1').Value := Edit1.Text;
  MyRecordSet := ADOCommand1.Execute;
  if MyRecordSet.State = adStateOpen then
    ADODataSet1.Recordset := MyRecordSet;
    ADOCommand1.ExecuteOptions := [eoExecuteNoRecords];
    MyReturn := ADOCommand1.Parameters.ParamByName('RETURN_VALUE').Value;
    for looper := 0 to ADOConnection1.Errors.Count - 1 do
      ShowMessage(Format('Stored Procedure %s Failed with Return Code %d and
Message "%s"',


This code is seriously flawed. If your command does not return a result set,
then the command is executed twice. So if you are doing an INSERT, UPDATE or
DELETE, then you are going to have lots of problems. Or if you do something
like EXEC sp_attach_db 'mydatabase', it attempts to attach the database
twice. That's not really fatal, but it isn't hard to come up with scenarios
where executing stored procedures this way would be problematic.

Another problem with this code is that you can't retreive the messages that
might come back from the server if you also are getting a result set. So if
you write a stored procedure that returns both result sets and text
messages, you can't get both. As an aside, I can't use a stored procedure
component as I have no apriori knowledge of the command text, parameters,

The other big issue I am having is trying to grab multiple result sets from
a stored procedure. Also in this group, I was advised to look at the
NextRecordSet method of the TConnection component. If I use the above code
to send the command EXEC sp_helpdb 'mydatabase' and step through the code,
the connection object shows only on active data set, not two, which is what
the SQL Server documentation shows as being returned. If you send the same
command through the SQL Server Query Analyzer, you get both datasets nicely
displayed, and the text messages from the server.

Any suggestions or code samples would be most appreciated.


Successware Software