Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-07-03 : 07:38:11
|
| Greg writes "Hello I am fairly new to TSql so I am hoping you can help me with this problem. I am creating a SP called DTE_spFullSearch which does a search based on parameters and stores the results into a temp table called #tblPagingTemp for paging. This works fine and I can access the Temp table from within DTE_spFullSearch. The problem is I need to access the table from a second SP called DTE_spFullSearchFetchResults which is called from asp.net code directly after the first one and uses the same connection and the table has not been dropped. SQL Server always returns that it can't find the temp table. The SP's are written using MS Access as a data project. The annoying thing is that if I run the following code from SQL Query Analyser then the temp table is created:Create table #tblPagingTemp (c1 int null);If however I execute a test stored procedure that contains the above line in SQL QA then the table is not created event though the command completed successfully: CREATE PROCEDURE TestAS Create table #tblPagingTemp (c1 int null);RETURN Execute Test Can you please help explain what I am doing wrong?Greg" |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2003-07-03 : 08:56:14
|
| Your temporary table will only exist for the life of the procedure that creates it.Either try a global temporary table (##) to see if that works, or a fixed table, or combine everything into one procedure.-------Moo. |
 |
|
|
|
|
|
Creating temporary tables withing stored procedures