Sproc using temp tables & disconnected recordset

Sproc using temp tables & disconnected recordset

Post by Newsgroup » Fri, 24 Jan 2003 00:07:54



Hello all,

Calling an sproc from VB6 with results going into a disconnected recordset.
The VB code is from MS and works great *unless* the sproc makes use of temp
tables.  When the sproc uses temp tables, the returned recordset cannot be
disconnected successfully.  Any help, pointers, etc. would be appreciated.
Thank you.

 
 
 

Sproc using temp tables & disconnected recordset

Post by Bob Barrow » Fri, 24 Jan 2003 10:27:13



Quote:> Hello all,

> Calling an sproc from VB6 with results going into a disconnected
> recordset.
> The VB code is from MS and works great *unless* the sproc makes use of
> temp
> tables.  When the sproc uses temp tables, the returned recordset cannot be
> disconnected successfully.  Any help, pointers, etc. would be appreciated.
> Thank you.

Ah! Yet another reason to avoid temp tables.

If you are selecting results from a temp table, you have to be aware tat the
temp table is destroyed when a stored procedure ends. Therefore, you cannot
reconnect to a table that no longer exists. You may as well use a firehose
cursor and read the results into an array or something - you will not be
able to reconnect a recordset to a temp table.

Sorry,
Bob Barrows

 
 
 

Sproc using temp tables & disconnected recordset

Post by Newsgroup » Fri, 24 Jan 2003 11:11:19



Quote:> If you are selecting results from a temp table, you have to be aware tat
the
> temp table is destroyed when a stored procedure ends. Therefore, you
cannot
> reconnect to a table that no longer exists. You may as well use a firehose
> cursor and read the results into an array or something - you will not be
> able to reconnect a recordset to a temp table.

Bob,

Thanks for the reply; concise and understandable.  I'm still surprised to
see this error crop up in what I see as the firehose/read-only scenario,
though.  Time for another pass through the ADO docs and some (more)
experimentation...

 
 
 

Sproc using temp tables & disconnected recordset

Post by Tibor Karasz » Fri, 24 Jan 2003 20:04:03


Might not be related, but perhaps you need a SET NOCOUNT ON in the beginning of the proc code?

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...




> > If you are selecting results from a temp table, you have to be aware tat
> the
> > temp table is destroyed when a stored procedure ends. Therefore, you
> cannot
> > reconnect to a table that no longer exists. You may as well use a firehose
> > cursor and read the results into an array or something - you will not be
> > able to reconnect a recordset to a temp table.

> Bob,

> Thanks for the reply; concise and understandable.  I'm still surprised to
> see this error crop up in what I see as the firehose/read-only scenario,
> though.  Time for another pass through the ADO docs and some (more)
> experimentation...

 
 
 

1. Problem using shape recordsets with stored procedure that uses a temp table

Hello,

i use a stored procedure to get data. This sp uses a temporary table to get
the correct results. There is no problem with this sp retrieving the data
directly into an ado recordset, but if i want to use this sp in a shape
command the ODBC driver raises an error that he could not find the temporary
table.
In normal the temporary table is created in the sp and will be deleted after
executing them.

Is there anywhere a documentation on using temporary tables with a
hierarchical recordset, or better knows anybody the solution for this
problem.

The used shape command:
SHAPE {{ CALL sp_GetProductsByCategory (10,3,12) }} AS Products
APPEND ({{ CALL sp_GetMediaFiles() }} AS Mediafiles RELATE 'ProductID' TO
'ProductID') AS Mediafiles

The raised error:
Microsoft OLE DB Provider for ODBC Drivers: -2147467259
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
'#results'

Thanks a lot for reply
Ciao

2. Reinstall ODBC

3. T-SQL Debugger

4. Temp Recordsets in SPROC

5. Transaction Backup Failures

6. Disconnected recordsets & SQL temporary tables

7. Copying Tables Using Disconnected Recordsets

8. Problem with retrieving an ADO Recordset from SQLServer using Temp Tables

9. Problem: Recordset is closed after calling a stored procedure that uses temp tables

10. Recordsets from temp tables using cursor