How to return ado recordset from temp table in stored procedure
Posted on 2011-10-18
I am using a stored procedure to collate some information into a temp table (#tmp) and I want to return the resultant set to an ado recordset in MS Access or VBScript.
The stored prodecure works like this:
/* procedure sptest */
create table #tmp(txtFilename varchar(100))
select top 100 txtFileName from tblfile1 where criteria1
select top 100 txtFileName from tblfile2 where criteria2
select * from #tmp
The caller is like this:
Dim con As New ADODB.Connection
Dim rs as ADODB.Recordset
con.ConnectionString = "Provider=SQLOLEDB;Server=xxxxxx;Initial Catalog=XXXX;User id=XXXX;Password=xxxx"
set rs = new adodb.Recordset
if not rs is nothing then
if rs.recordcount > 0 then
' do stuff
If I simply select records from a real table in the stored procedure, the records are returned to the calling script and into the recordset, but if I use a temp table, and select from that, the recordset is not created.
Any ideas what I'm doing wrong?