global temporary table is getting dropped in between

Question

global temporary table is getting dropped in between

paulselva_26

SSC Rookie

Points: 28
More actions
June 26, 2009 at 11:32 pm

#133878

global temporary table is getting dropped in between the same session.
Kindly find the code and the error for the same
113 sql3="create table ##t_abc(avg decimal(5,2),year int,month int,week int)"
112 set rs=CCon.execute(sql3)
114 sql1="insert into ##t_abc select * from x table"
115 set rs=CCon.execute(sql1)
'temp table is getting dropped here
116 sql= "select isnull(avg(avg),0),year,month,week from ##t_abc group by year,month,week order by year,month,week"
117 set rs=CCon.execute(sql)
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '##t_abc'. line no 117

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 1

I'm wondering if the connection is getting dropped and reconnected. Have you tried SQL Profiler to see exactly what is going on with SQL and that app?

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 2

paulselva_26 (6/26/2009)
113 sql3="create table ##t_abc(avg decimal(5,2),year int,month int,week int)"
112 set rs=CCon.execute(sql3)

Do you use exactly and only this statement?

If you first check the tables existence before you create it, the table can be dropped by another closing session.

hein-1120388 Ten Centuries Points: 1391 More actions · Answer 3

Not sure about ASP, but I know ADO Connections has a KeepConnection property. If it's false, it will drop the connection automatically if no datasets are open. Perhaps this is the case?

You check this by using one large query string including all three queries and execute it once. If possible.

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 4

I still think his best bet is to trace it, then he can see exactly what is going on..

CEWII

paulselva_26 SSC Rookie Points: 28 More actions · Answer 5

sorry i forgot to update i found the solution but without logic

previous code

113 sql3="create table ##t_abc(avg decimal(5,2),year int,month int,week int)"

112 set rs=CCon.execute(sql3)

114 sql1="insert into ##t_abc select * from x table"

115 set rs=CCon.execute(sql1)

'temp table is getting dropped here

116 sql= "select isnull(avg(avg),0),year,month,week from ##t_abc group by year,month,week order by year,month,week"

117 set rs=CCon.execute(sql)

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '##t_abc'. line no 117

new code which worked for me

113 sql3="create table ##t_abc(avg decimal(5,2),year int,month int,week int)"

112 set rs=SCon.execute(sql3)

114 sql1="insert into ##t_abc select * from x table"

115 set rs=CCon.execute(sql1)

sql1="insert into ##t_abc select * from x table"

set rs=SCon.execute(sql1)(fetching value from other DB)

116 sql= "select isnull(avg(avg),0),year,month,week from ##t_abc group by year,month,week order by year,month,week"

117 set rs=CCon.execute(sql)

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '##t_abc'. line no 117

I just changed the connection string for the Temp table creation but the connection strings pointing 2 different databases in same server

And thanks for all your valuable suggestion anyway ill run the trace and ll provide the result