Stored procedure returns closed ADO Recordset

[Gila47ac]

A co-worker has created a stored procedure (in MS SQL Server) that executes fine when called from the Query Analyzer. But when he uses VB and the ADO Command object to execute the procedure (i.e., objCmd.Execute after assigning the connection, parameter, etc.), the result is a Recordset object that seems to be closed. The ADO Connection object has nothing in its Error collection. Any ideas on what could be causing this, or what else to examine to obtain more info?

When he executes a different (simpler) stored procedure, using the same parameter, it DOES work. And he has previously written his fair share of stored procs.

He is doing something unusual in the stored proc. The parameter (there's only one) is a varchar containing a delimited list of values; the proc puts the list into the IN clauses of a UNION query that the proc builds and executes. Temporary tables are created, used, and dropped.

Details: The DB is SQL Server 2000. The ADO library version is 2.6 (although I noticed that his Connection object exposes a Version property of 2.7 ??).

 

[Gila47ac]

Additional details, the cause, and a solution:

The provider used was the SQL Server provider -- SQLOLEDB.1. The temporary tables are not global temporary tables.

The co-worker found a MS KnowledgeBase article (295743) that states in part "ADO is designed to provide high performance with parameterized queries. The architecture it uses, however, prevents temporary tables that are created within a parameterized query from persisting outside of that query. ... This behavior is by design."

One of the workarounds suggested in a different article is to use a different provider. When he uses the ODBC provider, the problem goes away.