Using Temporary tables in SQL Server from ASP

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.

All Forums

Development Tools

Other Development Tools

Using Temporary tables in SQL Server from ASP

Author

Topic

Hoyas
Starting Member

8 Posts

Posted - 2004-03-01 : 15:49:36

Hi, guys:
I need to use temporary tables from my ASP application.

I insert records into temporary table #q1
do
Set objRecordset1 = objConnection.Execute(SQLs)

then i try to do
SQLs= "Select * from #q1"
Set objRecordset1 = objConnection.Execute(SQLs)

but at this point i get an error message

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#q1'.

I realize that this temporary table is expired already. How can I keep it alive so that i could use it with my other queries, and then have it destroyed at the end of the session?

Thanks a lot, i really need ur help

Nath

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-01 : 15:52:47

Use stored procedures as they are better for speed and security reasons.

CREATE PROC usp_SomeProc
AS

SET NOCOUNT ON

CREATE TABLE #Temp
(
Column1 VARCHAR(50) NOT NULL
)

INSERT INTO #Temp (Column1)
SELECT ColumnA
FROM Table1
WHERE ColumnB = 0

SELECT Column1
FROM #Temp

DROP TABLE #Temp
SET NOCOUNT OFF

RETURN
GO

Tara

Hoyas
Starting Member

8 Posts

Posted - 2004-03-02 : 10:18:13

Thanks, I do use stored procedures, but in this case it's not applicable, i have some ASP code generating the SQL query, like this

If objRecordset1.EOF=False And objRecordset1.BOF=False Then
SQLs="SELECT Users.Id": w=" WHERE 1=1": p1="(100-(" : k=0
For i=1 To objRecordset1.Fields.Count-1
n1=CStr(objRecordset1.Fields(i).Name)
n="#q1." & n1
a=""
For j=1 To Request.Form(n1).Count
If Request.Form(n1)(j)<>"0" Then a="1"
Next

so i need to be able to access this temp table

Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2004-03-02 : 10:26:15

You can create the temp table at the root nest level and it will be available.
i.e. if you create the temp table not inside an SP or an exec'd dynamic sql batch and retain the connection you should be OK.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Hoyas
Starting Member

8 Posts

Posted - 2004-03-02 : 10:50:40

The thing is that I use a dynamic query, so i have to create a table within the procedure. Any ideas :)????

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-02 : 12:37:28

You can use a global temporary table.

CREATE TABLE ##Temp
(
...
)

Tara

Hoyas
Starting Member

8 Posts

Posted - 2004-03-03 : 08:58:39

Thanks a lot, Tara!!! It works! My last question is what is a lifecycle of it?

Thanks again

Nath

nr
SQLTeam MVY

12543 Posts

Posted - 2004-03-03 : 09:35:16

It will stay untill the crating connections is closed or it is explicitely dropped.
Be careful as there is a single copy tof that table that can be accessed by any connection. If another connection tries to create the same table it will fail.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Hoyas
Starting Member

8 Posts

Posted - 2004-03-03 : 13:43:04

Thanks a lot to both of you, guys. Veeeeeery helpful

Sincerely

Nath

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources