volatile table disappears

Analytics
Enthusiast

volatile table disappears

I've created a SQL pass-through query in MS Access that creates a volatile table in my logon user database. After I create that table, I can query the table in Access and get the expected results. However, the table is NOT visible when I query it in SQL Assistant. I get an error message that that object does not exist.

Any ideas where MS Access might be putting the table if it isn't in my logon database as specified? (I've also queried the default database and the database referenced by the SQL statement used to create the volatile table. The table isn't there either.)
3 REPLIES
Enthusiast

Re: volatile table disappears

Nevermind - a colleague suggested that my connection through MS Access might be considered a separate session and, so, the table would not be visible in a simultaneous connection through SQL Assistant.
Enthusiast

Re: volatile table disappears

its a matter of sessions. when u logon thru Access, u ll get one session established and the VT you create in that session would get visibility only to that session. When u come to SQLA and log in again with same id, your session would be different and you wont get access to any VTbles u created in other sessions. fyi, volatile tables are gettin meterialized in spool space and their DDLs wont get stored in any of the DD tables. DDLs are memory resident till end of your session.
Enthusiast

Re: volatile table disappears

The table is u r session specific. Once you close the session it will no longer exist. It is accessible through active session only.

Regds,
Sachin