Some question about using temp table in stored procedure

Some question about using temp table in stored procedure

Post by Chusak Jinuntuy » Tue, 26 Dec 2000 18:30:16



i have one software package that  almost use stored procedure in SQL 6.5
database and stored procedure create some temp tables and then call another
stored procedure that use following created temp table. when i modify one
stored procedure to debug value and then apply the changes, there is an
error like they are not recognize that temp table. How can i debug such a
stored procedure and anybody can clearify me about using temp table in
nested stored procedure?
Thanks in adv.

Best Regards,
Chusak Jinuntuya

 
 
 

Some question about using temp table in stored procedure

Post by BP Margoli » Wed, 27 Dec 2000 01:59:34


Chusak,

Since in SQL Server 6.5, stored procedures are stored in compiled form, it
is a requirement that any database object, including temporary tables, must
exist for the stored procedure to compile.

Thus, if you are testing the dependent stored procedure, you must first
create the temp table either by executing the first sp, or by running a
CREATE TABLE for the temp table yourself.

SQL Server 7.0 introduced deferred name resolution which resolves this
issue.

----------------------------------------------------------------
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:> i have one software package that  almost use stored procedure in SQL 6.5
> database and stored procedure create some temp tables and then call
another
> stored procedure that use following created temp table. when i modify one
> stored procedure to debug value and then apply the changes, there is an
> error like they are not recognize that temp table. How can i debug such a
> stored procedure and anybody can clearify me about using temp table in
> nested stored procedure?
> Thanks in adv.

> Best Regards,
> Chusak Jinuntuya


 
 
 

Some question about using temp table in stored procedure

Post by Chusak Jinuntuy » Wed, 27 Dec 2000 12:42:06


Thank your for your information, and another problem is that is an original
stored procedure, thus after i modify the exist stored procedure but i can
not apply the change. how can i do?

> Chusak,

> Since in SQL Server 6.5, stored procedures are stored in compiled form, it
> is a requirement that any database object, including temporary tables,
must
> exist for the stored procedure to compile.

> Thus, if you are testing the dependent stored procedure, you must first
> create the temp table either by executing the first sp, or by running a
> CREATE TABLE for the temp table yourself.

> SQL Server 7.0 introduced deferred name resolution which resolves this
> issue.

> ----------------------------------------------------------------
> 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.



> > i have one software package that  almost use stored procedure in SQL 6.5
> > database and stored procedure create some temp tables and then call
> another
> > stored procedure that use following created temp table. when i modify
one
> > stored procedure to debug value and then apply the changes, there is an
> > error like they are not recognize that temp table. How can i debug such
a
> > stored procedure and anybody can clearify me about using temp table in
> > nested stored procedure?
> > Thanks in adv.

> > Best Regards,
> > Chusak Jinuntuya

 
 
 

Some question about using temp table in stored procedure

Post by BP Margoli » Thu, 28 Dec 2000 02:46:57


Chusak,

Sorry, but I don't understand the question?

----------------------------------------------------------------
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.


> Thank your for your information, and another problem is that is an
original
> stored procedure, thus after i modify the exist stored procedure but i can
> not apply the change. how can i do?


> > Chusak,

> > Since in SQL Server 6.5, stored procedures are stored in compiled form,
it
> > is a requirement that any database object, including temporary tables,
> must
> > exist for the stored procedure to compile.

> > Thus, if you are testing the dependent stored procedure, you must first
> > create the temp table either by executing the first sp, or by running a
> > CREATE TABLE for the temp table yourself.

> > SQL Server 7.0 introduced deferred name resolution which resolves this
> > issue.

> > ----------------------------------------------------------------
> > 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.



> > > i have one software package that  almost use stored procedure in SQL
6.5
> > > database and stored procedure create some temp tables and then call
> > another
> > > stored procedure that use following created temp table. when i modify
> one
> > > stored procedure to debug value and then apply the changes, there is
an
> > > error like they are not recognize that temp table. How can i debug
such
> a
> > > stored procedure and anybody can clearify me about using temp table in
> > > nested stored procedure?
> > > Thanks in adv.

> > > Best Regards,
> > > Chusak Jinuntuya

 
 
 

Some question about using temp table in stored procedure

Post by Erland Sommarsk » Fri, 29 Dec 2000 07:35:15



>SQL Server 7.0 introduced deferred name resolution which resolves this
>issue.

And creates a much more serious problem...

--

This is signature isn't half as witty as it used to be.

 
 
 

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. Setting Dialing preferences

3. VB GUI and VC++ backend communication.....

4. insert into a global temp table using stored procedure

5. INNER LOOP JOIN VS INNER JOIN

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

7. Oracle Resources (JOBS, RESUMES, LINKS, TUNING SCRIPT)!

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

9. ? about using temp tables in a stored procedure

10. Numerous deadlocks occurred involving Stored procedures using Temp tables

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