INF: Temporary Tables and Cursors

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
Issue type :