Please could somebody clarify situation with temp tables

Please could somebody clarify situation with temp tables

Post by Pau » Sun, 07 Jul 2002 01:42:53



Hi,

If I have a stored proc that comprises of the following steps:

Step 1. Generate some sql on the fly and insert result set into temp
table 1
Step 2. Generate some more sql on the fly and insert result set into
temp table 2
Step 3. Select from joins on temp tables and other result sets not
involving temp tables

The stored proc returns the expected result set ok as I executed the
stored proc in query analyzer.

Can I base an asp page on this result set or is the result set lost as
soon as the stored procedure is executed, its just that when I called
a function from my asp page that returns the result set I get an error
"Object reference not set to an instance of an object" which suggest
nothing is getting returned. I'm just not sure as to the status of
temp tables. I have another stored proc which is similar in that it
involves temp tables but all it returns is a row count as an ouput
parameter.

Thanks

Paul

 
 
 

Please could somebody clarify situation with temp tables

Post by BP Margoli » Sun, 07 Jul 2002 11:12:16


Paul,

The short answer is Yes, you can base an ASP on the result set returned from
a stored procedure, even if the result set uses temp tables in its
generation. However without additional information, it is impossible, at
least for me, to provide any additional assistance.

Try simplifying the situation as much as you can, and post the simplified
code ... the less the better, because no one is likely to look through
hundreds of lines of SQL and ASP coding.

BTW, while I do not personally do ASP coding, I have worked on projects
where ASP pages were based on result sets returned from stored procedures I
coded ... point being that I might not be able to help you with the ASP
part.

One last thought ... if you are using stored procedures, be sure to add a
SET NOCOUNT ON at the beginning of the procedure. Otherwise, the "x row(s)
affected" is returned as the first result set, and that throws a lot of
developers   :-)

-------------------------------------------
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:> Hi,

> If I have a stored proc that comprises of the following steps:

> Step 1. Generate some sql on the fly and insert result set into temp
> table 1
> Step 2. Generate some more sql on the fly and insert result set into
> temp table 2
> Step 3. Select from joins on temp tables and other result sets not
> involving temp tables

> The stored proc returns the expected result set ok as I executed the
> stored proc in query analyzer.

> Can I base an asp page on this result set or is the result set lost as
> soon as the stored procedure is executed, its just that when I called
> a function from my asp page that returns the result set I get an error
> "Object reference not set to an instance of an object" which suggest
> nothing is getting returned. I'm just not sure as to the status of
> temp tables. I have another stored proc which is similar in that it
> involves temp tables but all it returns is a row count as an ouput
> parameter.

> Thanks

> Paul


 
 
 

Please could somebody clarify situation with temp tables

Post by Pau » Tue, 09 Jul 2002 17:06:41


Hi BP,

Thanks for the reply. I eventually found my mistake - it turned out to
be a simple asp error rather than a sql error. I'm not too familiar
with temp tables in sql server and was my primary concern initially.
Thanks again though.

Paul


> Paul,

> The short answer is Yes, you can base an ASP on the result set returned from
> a stored procedure, even if the result set uses temp tables in its
> generation. However without additional information, it is impossible, at
> least for me, to provide any additional assistance.

> Try simplifying the situation as much as you can, and post the simplified
> code ... the less the better, because no one is likely to look through
> hundreds of lines of SQL and ASP coding.

> BTW, while I do not personally do ASP coding, I have worked on projects
> where ASP pages were based on result sets returned from stored procedures I
> coded ... point being that I might not be able to help you with the ASP
> part.

> One last thought ... if you are using stored procedures, be sure to add a
> SET NOCOUNT ON at the beginning of the procedure. Otherwise, the "x row(s)
> affected" is returned as the first result set, and that throws a lot of
> developers   :-)

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

 
 
 

1. Want to clarify some concept about temp segment

Some confusing on temp segment, could anyone clarify for me,

1. If a temp segment is using by one transaction, is it possible another
transaction will try to use the same tmp segment?

2. Is there anything about contention of temp segment?

3. If a single temp segment occupied almost most the space of the tempoary
tablespace. Is it possible that another transaction can not proceed due to
lack of space to have another temp segment.

Tnks,

Eric

2. tempdb problem

3. Urgent - Please suggest a way to substitute temp tables with permanent tables in Sprocs

4. Ascii Fixed width

5. Temp table vs Global Temp table

6. WithEvents Stored procedure??

7. Can MS please clarify? re: CURSOR_CLOSE_ON_COMMIT error

8. Reboot after MSDE 2000 installation

9. Please clarify parts of select statement

10. Please clarify documentation error.

11. Please clarify with regard to Renaming a Sequence

12. Please clarify about dynamic sql