Passing Ado Recordsets between methods


Hi all.

I have been fighting this one for some time. I am trying to pass an Ado recordset between two methods in a COM+ object. The recordset is created by opening a TAdoStoredProc which is created dynamically within one of the methods and calls an SQL Server stored procedure.

It all seems to work apart from the fact that when the method that called the sp executing method gets the recordest back it is in a closed state. Hopefully the following simplified code will help make my problem clearer. Any help would be great as I am stumped!

TIA .

procedure TMyClass.Method1;
var
RecSet: RecordSet20;
begin
GetRecSet(RecSet);
// RecSet is closed here! WHY?????
end;

procedure TMyClass.GetRecSet(out Recset: RecordSet20);
var
SP: TADOStoredProc;
CONN: TADOConnection;
begin
CONN := TADOConnection.Create(nil);
try
CONN.ConnectionString := 'MyConnectionString';
CONN.CursorLocation := clUseServer;
CONN.LoginPrompt := False;
CONN.Open;
SP := TADOStoredProc.Create(nil);
try
SP.Connection := CONN;
SP.LockType := ltReadonly;
SP.CursorType := ctStatic;
SP.CursorLocation := clUseClient;
SP.ProcedureName := 'MyProcName';
SP.Open;
//copy the recordset into our output parameter
Recset := SP.Recordset.Clone(adLockUnspecified);
//RecSet is open here!
finally
SP.Free;
end;
finally
CONN.free;
end;
end;

.