? about using temp tables in a stored procedure

? about using temp tables in a stored procedure

Post by Libby Mitchel » Thu, 18 May 2000 04:00:00



SQL Server 6.5

I am trying to compile a stored procedure that makes reference to some
temp tables created in another procedure. It won't compile - "invalid
object #tempTable". I have created the tempTable in ISQL. The temp table
was created under my id so of course the compiler isn't seeing it. Do I
need to be dbo, or what am I doing wrong?

The plan is to have 1 procedure create the tables, then call this one to
read them.

Thanks,

Libby

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

? about using temp tables in a stored procedure

Post by DaveSat » Thu, 18 May 2000 04:00:00


in your SQL script:

-create the temp table(s)
-drop the stored proc if it exists
-create the stored proc
-drop the temp table(s)

make sure to have all 4 steps  separated with a GO
--
--
Thanks,
David Satz
Principal Software Engineer
Hyperion Solutions
(using VB6 SP3/MTS/SQL Server 6.5 SP5a./MDAC 2.1.2.4202.3)
(Please respond to the newsgroup.)


Quote:> SQL Server 6.5

> I am trying to compile a stored procedure that makes reference to some
> temp tables created in another procedure. It won't compile - "invalid
> object #tempTable". I have created the tempTable in ISQL. The temp table
> was created under my id so of course the compiler isn't seeing it. Do I
> need to be dbo, or what am I doing wrong?

> The plan is to have 1 procedure create the tables, then call this one to
> read them.

> Thanks,

> Libby

> Sent via Deja.com http://www.deja.com/
> Before you buy.


 
 
 

? about using temp tables in a stored procedure

Post by Sandeep Gupt » Thu, 18 May 2000 04:00:00


If a temporary table is not dropped when the user disconnects, it is dropped
automatically by SQL Server. Temporary tables are not stored in the current
database, they are instead stored in the system database tempdb. There are
two types of temporary tables:

  a.. Local temporary tables have only one number sign (#) at the start of
their name. They are visible only to the connection that created them.
  b.. Global temporary tables have a double number sign (##) at the start of
their name. They are visible to all connections. If they are not dropped
explicitly before the connection that created them disconnects, they are
dropped as soon as all other tasks stop referencing them. No new tasks can
reference a global temporary table after the connection that created it
disconnects. The association between a task and a table is always dropped
when the current statement completes executing, so global temporary tables
are generally dropped soon after the connection that created them
disconnects.
Thanx
Sandeep Gupta

 Server 6.5

Quote:

> I am trying to compile a stored procedure that makes reference to some
> temp tables created in another procedure. It won't compile - "invalid
> object #tempTable". I have created the tempTable in ISQL. The temp table
> was created under my id so of course the compiler isn't seeing it. Do I
> need to be dbo, or what am I doing wrong?

> The plan is to have 1 procedure create the tables, then call this one to
> read them.

> Thanks,

> Libby

> Sent via Deja.com http://www.deja.com/
> Before you buy.

 
 
 

? about using temp tables in a stored procedure

Post by BPMargoli » Thu, 18 May 2000 04:00:00


Libby,

You have just indicated exactly why SQL Server 7.0 uses deferred resolution.
If it is possible, do try to upgrade to SQL Server 7.0. The problem you are
having just disappears, plus SQL Server 7.0 is both faster and more reliable.
Additionally, with SQL Server 2000 on the horizon (late this summer probably),
Microsoft may end support for SQL Server 6.5.

---------------------------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc) which can be
cut and pasted into Query Analyzer is appreciated.


Quote:> SQL Server 6.5

> I am trying to compile a stored procedure that makes reference to some
> temp tables created in another procedure. It won't compile - "invalid
> object #tempTable". I have created the tempTable in ISQL. The temp table
> was created under my id so of course the compiler isn't seeing it. Do I
> need to be dbo, or what am I doing wrong?

> The plan is to have 1 procedure create the tables, then call this one to
> read them.

> Thanks,

> Libby

> Sent via Deja.com http://www.deja.com/
> Before you buy.

 
 
 

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. Indiana - Oracle Programmer

3. creating database fails

4. insert into a global temp table using stored procedure

5. VB4 ODBC Oracle/Rdb tranaction problem

6. Creating a Temp table using results from a store procedure

7. Some question about using temp table in stored procedure

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

9. Numerous deadlocks occurred involving Stored procedures using Temp tables

10. using RDO 2.0 and VB5: Getting Data from temp tables created in stored procedure