How does B.O. handle temporary tables created in a stored procedure?
I have a stored procedure that creates 3 temporary tables in Sybase SQL. Normally, in Sybase, temporary tables last for the entire Session so if you want to rerun the stored procedure, the temporary tables have to somehow be “dropped” so they can be recreated, you have to log off and back on again to establish a new session.
In B.O. reporter, when you want to rerun the report in the same Session,
Does the Refresh button establish a new Session?
Are the tables “magically” dropped?
Or do the tables have to be dropped in the stored procedure?
First I have to say that BO would have nothing to do with Temp tables created by a SP. That sort of thing is handled on the database server. I cant speak for sybase, but I used SP in SQL server quite extensivly. I would think they would be similar. So, with that. I never, ever let a session ending drop my temp tables, I use the drop statement at the end of my sql statements. This guarentees that they are dropped. Now, the second way is for the connection to dissconnect, I am not sure on this instance if BO ends its connection when you use SP. There might be a setting in supervisor to make it disconnect after each (cant think of the word) transaction(?). You could get with your DBA’s and test it pretty easily though. Have them watch connections and run a test SP and watch to see if it connects, transacts and then disconnects.
(BOB member since 2002-09-30)