MS SQL Server - Session expires after an execution of stored procedure?

Author

Session expires after an execution of stored procedure?

guxu@hotmail.com

2006-01-04, 9:23 am

I created a temp table as follows:

Dim objCmd As New ADODB.Command
Dim objConn As New ADODB.Connections
Dim strSQLStmt As String
strSQLStmt = "CREATE TABLE #tblBooks (BookTitle VARCHAR(100), "
& _
"Publisher VARCHAR(100), DateOfPurchase SMALLDATETIME)"
objConn.Execute(strSQLStmt)

Then I called a stored procedure to update my table from the data in
the temp table as follows:
objCmd.CommandText = "update_tblBooks"
objCmd.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
objCmd.Parameters.Refresh()
On Error Resume Next
objCmd.Execute()
On Error GoTo 0

Right after the stored procedure call, I tried to drop the temp table
and close connection
objConn.Execute("DROP TABLE #tblBooks")
objConn.Close()

But I got an error saying the table #tblBooks did not exist in the
system catalog. Does that mean my session already expired? How could
this happen? My connection was closed after the DROP TABLE. Could
anyone please explain this situation? Thanks very much.

Ryan

2006-01-04, 9:23 am

You don't need to explicity drop the temporary table as SQL takes care of
this for you.

From SQL Books Online :- tsqlref.chm::/ts_create2_8g9x.htm

Temporary tables are automatically dropped when they go out of scope, unless
explicitly dropped using DROP TABLE:

a.. A local temporary table created in a stored procedure is dropped
automatically when the stored procedure completes. The table can be
referenced by any nested stored procedures executed by the stored procedure
that created the table. The table cannot be referenced by the process which
called the stored procedure that created the table.

b.. All other local temporary tables are dropped automatically at the end
of the current session.

c.. Global temporary tables are automatically dropped when the session
that created the table ends and all other tasks have stopped referencing
them. The association between a task and a table is maintained only for the
life of a single Transact-SQL statement. This means that a global temporary
table is dropped at the completion of the last Transact-SQL statement that
was actively referencing the table when the creating session ended.

--
HTH. Ryan

<guxu@hotmail.com> wrote in message
news:1136386374.641522.108660@g49g2000cwa.googlegroups.com...
>I created a temp table as follows:
>
> Dim objCmd As New ADODB.Command
> Dim objConn As New ADODB.Connections
> Dim strSQLStmt As String
> strSQLStmt = "CREATE TABLE #tblBooks (BookTitle VARCHAR(100), "
> & _
> "Publisher VARCHAR(100), DateOfPurchase SMALLDATETIME)"
> objConn.Execute(strSQLStmt)
>
> Then I called a stored procedure to update my table from the data in
> the temp table as follows:
> objCmd.CommandText = "update_tblBooks"
> objCmd.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
> objCmd.Parameters.Refresh()
> On Error Resume Next
> objCmd.Execute()
> On Error GoTo 0
>
> Right after the stored procedure call, I tried to drop the temp table
> and close connection
> objConn.Execute("DROP TABLE #tblBooks")
> objConn.Close()
>
> But I got an error saying the table #tblBooks did not exist in the
> system catalog. Does that mean my session already expired? How could
> this happen? My connection was closed after the DROP TABLE. Could
> anyone please explain this situation? Thanks very much.
>

guxu@hotmail.com

2006-01-04, 11:23 am

Thanks for your quick response.
I knew I did not have to DROP it explicity. But I thought it was good
for us to drop the table rather than having to wait for the system
cleanup. My question was that since the table was NOT created in a
stored procedure and the connection was NOT even closed, how come the
table got droped after a stored procedure call which only did a table
update from the temp table? I thought the session ended when the
connection is closed.

Sponsored Links