INF: Temporary Tables and Cursors

Last reviewed: April 29, 1997
Article ID: Q118664

The information in this article applies to:

  - Microsoft SQL Server Programmer's Toolkit, version 4.2

SUMMARY

The DB-Library (and VBSQL) cursor functions use the catalog stored procedures to retrieve system table information from the database. The current SQL Server version 4.2 catalog stored procedures do not work against temporary tables. Thus, you cannot open a cursor using a SELECT that references a temporary table at this time.

MORE INFORMATION

Cursors can be opened against sets of temporary information by using a permanent table that contains an additional column containing the users server process ID (SPID). The SPID is a value that is guaranteed to be unique to each connection to SQL Server.

This technique requires a table of the form:

   create table temp_table (spid int, col int)
   go
   create unique index tidx on temp_table( col )
   go

When you want to start using this table, you should first make sure there are no left-over values from previous users by using the following query:

   delete from temp_table where spid = @@spid

Then you can insert a value, 25 for example, into the table:

   insert into temp_table (spid, col) values (@@spid, 25)

You can then retrieve rows from this table:

   select col from temp_table where spid = @@spid

Provided that a unique index can be created on the table, the above query can also be used to open a cursor onto all rows inserted into the table by the current connection.

Additional query words: dbcursoropen temp temporary dblib
Keywords : kbprg SSrvDB_Lib
Version : 4.2 | 4.2
Platform : OS/2 WINDOWS

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 29, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.