I tried your example on 6.5 SP5a and it worked. I did need to specify
an active connection, though. You may need to check your code for
connection problems. Or maybe you need to specify the object owner.

I checked with the OLE DB native provider and the OLE DB provider for
ODBC. Are you using different providers (or versions?) to connect to
7.0 and 6.5?

I'm sure that your proc is more complex than this, so my guess is that
you've come across an issue we found with SQLOLEDB.

SQLOLEDB behaves differently from the ODBC Provider, in that the native
provider 'stops' on each statement, where ODBC does not. In your case,
if you have any statements before the final SELECT, you need to get
the final recordset using rs.NextRecordset or use the SET NOCOUNT ON
statement at the top of the stp.

I contacted Bill Vaughn (Hitchiker's Guide to VB and SQL
Server) and he forwarded my query to the developers. Below was their
response: -

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

From the developers:

Actually, I'm pretty sure I know what it is.  This is that difference
between SQL ODBC and SQL OLEDB where the ODBC driver doesn't stop on
each "command", whereas SQLOLEDB does.  It's hard to explain but, look
at the following:

CREATE PROC GetEmployeeStp
AS
BEGIN
     SELECT * INTO #employee
     FROM employee           'SQL OLE DB "stops" first here

     SELECT * FROM #employee 'SQL ODBC & OLE DB stop here
END

In order to get SQLOLEDB to behave like SQL ODBC, add SET NOCOUNT ON
to the beginning of the stored procedure.  Then the SQLOLEDB driver
will only stop on row returning commands.

For what it's worth, he could also use "set rs=rs1.NextRecordset" in
this case to get to the result set, since it's the second stop in the
stored proc.

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

Perhaps the reason it works in 7.0 is that they've changed the behaviour?