PDA

View Full Version : Temp Tables in MSSQL



JA Queiroz
15-Mar-2006, 07:34 PM
Hi!

How can I create temp tables in MSSQL Server ?

Thanks

A Paul Anthony
16-Mar-2006, 04:35 AM
José Aparecido Queiroz wrote:

>Hi!
>
>How can I create temp tables in MSSQL Server ?
>
>Thanks
>
>
Seconded!

According to Books Online temporary tables can be createb by prefixing
the table name with a has, like this:

create table #temp (
i int identity(1,1),
v decimal(20, 8)
)

The really convienient thing about this is that its scoped to the SQL
session, even more granualar than just to the user, which is something I
would like to take advantage of myself.

To highjack the thread abit, I can execute the above statement via
embedded SQL and it /seems/ to create OK(no real way of telling as no
other user/session can confirm it) but any later statements error
denying its existance.

To confirm that the other statements weren't broken I simply removed the
# from the temporary table name (updating other references accordingly)
and I got the expected results. And, yes, I am sure they're being
executed in the same session.

So, to add to José's question, 'How can I create and USE temp tables in
MSSQL Server?'

--

A Paul Anthony
Software Developer

Peter Bragg
16-Mar-2006, 05:58 AM
Jose, Paul

We create and use temporary tables, but do so in a multiple-statement query
(if you follow) something along the lines of

string sMultiStatementQuery

move "create table #TEMP_NAME (seq integer identity, store integer, state
varchar(100) .. ) ;" to sMultiStatementQuery
move (sMultiStatementQuery+"insert into #TEMP_NAME (store,state) select
........... from ........... inner join ....... where ..... " ; ) to
sMultiStatementQuery
move (sMultiStatementQuery+"insert into ELSEWHERE ([sequence], store, state)
select seq, store, state from #TEMP_NAME ;") to sMultiStatementQuery
etc

before eventually

Send SQLExecDirect of ... sMultiStatementQuery

As Paul also found, we were unable to create a Temp table and then user it
in elsewhere, as only lasted for the "current execution" (probably the wrong
terminology, but I'm sure you know what I mean). With a multi-statement
query though (or whatever you call it) it is fine. Create it, and do what
you like with it.


Peter Bragg


"A Paul Anthony" <Paul@asckey.com> wrote in message
news:NrkWvzNSGHA.540@dacmail.dataaccess.com...
José Aparecido Queiroz wrote:
Hi!

How can I create temp tables in MSSQL Server ?

Thanks

Seconded!

According to Books Online temporary tables can be createb by prefixing the
table name with a has, like this:

create table #temp (
i int identity(1,1),
v decimal(20, 8)
)
The really convienient thing about this is that its scoped to the SQL
session, even more granualar than just to the user, which is something I
would like to take advantage of myself.

To highjack the thread abit, I can execute the above statement via embedded
SQL and it seems to create OK(no real way of telling as no other
user/session can confirm it) but any later statements error denying its
existance.

To confirm that the other statements weren't broken I simply removed the #
from the temporary table name (updating other references accordingly) and I
got the expected results. And, yes, I am sure they're being executed in the
same session.

So, to add to José's question, 'How can I create and USE temp tables in
MSSQL Server?'


--

A Paul Anthony
Software Developer

David Martinko
16-Mar-2006, 07:49 AM
So is there a way to Create a temp SQL table and populate it,.. then print
from Crystal Reports? Sounds like a big NO.

--
David Martinko
248-535-7495
Tracker Systems, Inc.
Redeemed Software
Redeemed Hosting
www.trackersys.com
www.redeemedsoftware.com
www.redeemedhosting.com
Proud member of the Northeast DataFlex Consortium: (NEDC)
www.NEDataFlex.com

"Peter Bragg" <tech@care-data.co.uk> wrote in message
news:%23LZrQiOSGHA.3232@dacmail.dataaccess.com...
Jose, Paul

We create and use temporary tables, but do so in a multiple-statement query
(if you follow) something along the lines of

string sMultiStatementQuery

move "create table #TEMP_NAME (seq integer identity, store integer, state
varchar(100) .. ) ;" to sMultiStatementQuery
move (sMultiStatementQuery+"insert into #TEMP_NAME (store,state) select
........... from ........... inner join ....... where ..... " ; ) to
sMultiStatementQuery
move (sMultiStatementQuery+"insert into ELSEWHERE ([sequence], store, state)
select seq, store, state from #TEMP_NAME ;") to sMultiStatementQuery
etc

before eventually

Send SQLExecDirect of ... sMultiStatementQuery

As Paul also found, we were unable to create a Temp table and then user it
in elsewhere, as only lasted for the "current execution" (probably the wrong
terminology, but I'm sure you know what I mean). With a multi-statement
query though (or whatever you call it) it is fine. Create it, and do what
you like with it.


Peter Bragg


"A Paul Anthony" <Paul@asckey.com> wrote in message
news:NrkWvzNSGHA.540@dacmail.dataaccess.com...
José Aparecido Queiroz wrote:
Hi!

How can I create temp tables in MSSQL Server ?

Thanks

Seconded!

According to Books Online temporary tables can be createb by prefixing the
table name with a has, like this:

create table #temp (
i int identity(1,1),
v decimal(20, 8)
)
The really convienient thing about this is that its scoped to the SQL
session, even more granualar than just to the user, which is something I
would like to take advantage of myself.

To highjack the thread abit, I can execute the above statement via embedded
SQL and it seems to create OK(no real way of telling as no other
user/session can confirm it) but any later statements error denying its
existance.

To confirm that the other statements weren't broken I simply removed the #
from the temporary table name (updating other references accordingly) and I
got the expected results. And, yes, I am sure they're being executed in the
same session.

So, to add to José's question, 'How can I create and USE temp tables in
MSSQL Server?'


--

A Paul Anthony
Software Developer

A Paul Anthony
16-Mar-2006, 07:57 AM
Peter Bragg wrote:

>Jose, Paul
>
>We create and use temporary tables, but do so in a multiple-statement query
>(if you follow) something along the lines of
>
>string sMultiStatementQuery
>
>move "create table #TEMP_NAME (seq integer identity, store integer, state
>varchar(100) .. ) ;" to sMultiStatementQuery
>move (sMultiStatementQuery+"insert into #TEMP_NAME (store,state) select
>.......... from ........... inner join ....... where ..... " ; ) to
>sMultiStatementQuery
>move (sMultiStatementQuery+"insert into ELSEWHERE ([sequence], store, state)
>select seq, store, state from #TEMP_NAME ;") to sMultiStatementQuery
>etc
>
>before eventually
>
>Send SQLExecDirect of ... sMultiStatementQuery
>
>As Paul also found, we were unable to create a Temp table and then user it
>in elsewhere, as only lasted for the "current execution" (probably the wrong
>terminology, but I'm sure you know what I mean). With a multi-statement
>query though (or whatever you call it) it is fine. Create it, and do what
>you like with it.
>
>
>Peter Bragg
>
>
I've just tried your "statement concatenation method" and whilst I don't
get the "table not known" error returned, neither is the column count,
and therefore the returning set, of the SELECT which was the last of the
compound statements. So unfortunately using ";" doesn't solve my problem.

To my way of thinking using temporary tables across multiple statements
handles should work anyway. In the embedded SQL methodology, which
parallels OBDC for obvious reasons, there appear to be three main levels.

1. Handle manager, used to control your sessions (cSQLHandleManager)
2. The sessions themselves, created using various connection strings
and credentials. "Stealing" an existing DD's credentials I find
the easiest (SQLFileConnect returning an instance of cSQLConnection).
3. Statements used to execute queries etc. (cSQLStatement).
Statements can be prepared for multiple use within a session using
(SQLPrepare and SQLExecute), or SQLExecDirect which does a
prepare and execute in a single hit.

If my assumption that "Connections" = "Session" then there may be
something wrong in the background implementation of how statements are
treated. My observations of how Query Analyser and SQL Server behave is
that when you open a SQL window, that is a session. What you execute a
query within the window that is a statement (cSQLStatement) of that
session (cSQLConnection). Whilst you can do multiple things in a single
statement using ";" it is little limiting with regards to preparing
statements, especially with temporary tables.

When you prepare a statement within a connection you'd expect it to be
there to execute at a later point (within that same connection). If
that prepared statement contains anything to do with a temporary table
then there's a problem as the table appears to have dropped at the end
of its own statement.

Still assuming that "Connections" = "Session", and that according to
Books Online a temporary lasts until the end of the session, or its been
explicitly dropped, why can't I see my temporary table between
cSQLStatemant executions? This is implying that when a statement is
finished with, not only is the statement being closed but the session
also. Surely it should be present until I send SQLDisconnect to the
connection?

People are going to get sick of this phrase; "it worked in Query Analyser!"

--

A Paul Anthony
Software Developer

David Martinko
16-Mar-2006, 08:20 AM
Maybe the question to ask is more like, how do I get VDF to keep it's
session? Is there something going on in the setup that is causing this to
happen? What drivers and version numbers is everyone using here? Is there
some statement that needs to be added to VDF in order to get it to keep a
session open? Maybe there is a Get_Session or Start_Session that begins a
process in which you can use the tempfiles, and a Release_Session.
IMHO, Temp tables are a useless feature if you cannot use them across
multiple requests. So I have to assume there is something more to it.

--
David Martinko
248-535-7495
Tracker Systems, Inc.
Redeemed Software
Redeemed Hosting
www.trackersys.com
www.redeemedsoftware.com
www.redeemedhosting.com
Proud member of the Northeast DataFlex Consortium: (NEDC)
www.NEDataFlex.com

"A Paul Anthony" <Paul@asckey.com> wrote in message
news:whUBmkPSGHA.540@dacmail.dataaccess.com...
Peter Bragg wrote:

>Jose, Paul
>
>We create and use temporary tables, but do so in a multiple-statement query
>(if you follow) something along the lines of
>
>string sMultiStatementQuery
>
>move "create table #TEMP_NAME (seq integer identity, store integer, state
>varchar(100) .. ) ;" to sMultiStatementQuery
>move (sMultiStatementQuery+"insert into #TEMP_NAME (store,state) select
>.......... from ........... inner join ....... where ..... " ; ) to
>sMultiStatementQuery
>move (sMultiStatementQuery+"insert into ELSEWHERE ([sequence], store,
>state)
>select seq, store, state from #TEMP_NAME ;") to sMultiStatementQuery
>etc
>
>before eventually
>
>Send SQLExecDirect of ... sMultiStatementQuery
>
>As Paul also found, we were unable to create a Temp table and then user it
>in elsewhere, as only lasted for the "current execution" (probably the
>wrong
>terminology, but I'm sure you know what I mean). With a multi-statement
>query though (or whatever you call it) it is fine. Create it, and do what
>you like with it.
>
>
>Peter Bragg
>
>
I've just tried your "statement concatenation method" and whilst I don't
get the "table not known" error returned, neither is the column count,
and therefore the returning set, of the SELECT which was the last of the
compound statements. So unfortunately using ";" doesn't solve my problem.

To my way of thinking using temporary tables across multiple statements
handles should work anyway. In the embedded SQL methodology, which
parallels OBDC for obvious reasons, there appear to be three main levels.

1. Handle manager, used to control your sessions (cSQLHandleManager)
2. The sessions themselves, created using various connection strings
and credentials. "Stealing" an existing DD's credentials I find
the easiest (SQLFileConnect returning an instance of cSQLConnection).
3. Statements used to execute queries etc. (cSQLStatement).
Statements can be prepared for multiple use within a session using
(SQLPrepare and SQLExecute), or SQLExecDirect which does a
prepare and execute in a single hit.

If my assumption that "Connections" = "Session" then there may be
something wrong in the background implementation of how statements are
treated. My observations of how Query Analyser and SQL Server behave is
that when you open a SQL window, that is a session. What you execute a
query within the window that is a statement (cSQLStatement) of that
session (cSQLConnection). Whilst you can do multiple things in a single
statement using ";" it is little limiting with regards to preparing
statements, especially with temporary tables.

When you prepare a statement within a connection you'd expect it to be
there to execute at a later point (within that same connection). If
that prepared statement contains anything to do with a temporary table
then there's a problem as the table appears to have dropped at the end
of its own statement.

Still assuming that "Connections" = "Session", and that according to
Books Online a temporary lasts until the end of the session, or its been
explicitly dropped, why can't I see my temporary table between
cSQLStatemant executions? This is implying that when a statement is
finished with, not only is the statement being closed but the session
also. Surely it should be present until I send SQLDisconnect to the
connection?

People are going to get sick of this phrase; "it worked in Query Analyser!"

--

A Paul Anthony
Software Developer

A Paul Anthony
16-Mar-2006, 08:48 AM
David Martinko wrote:

>Maybe the question to ask is more like, how do I get VDF to keep it's
>session? Is there something going on in the setup that is causing this to
>happen? What drivers and version numbers is everyone using here? Is there
>some statement that needs to be added to VDF in order to get it to keep a
>session open? Maybe there is a Get_Session or Start_Session that begins a
>process in which you can use the tempfiles, and a Release_Session.
>IMHO, Temp tables are a useless feature if you cannot use them across
>multiple requests. So I have to assume there is something more to it.
>
>
If you're referring to Crystal reusing a temporary table, I think that
probably won;t be on the cards. Sharing connections across applications
sounds like a bad idea to me.

With regards to Start_Session and Release_Session, isn't that the point
of SQLConnect and SQLFileConnect methods within cSQLHandleManager to
open sessions and SQLDisconnect within cSQLConnection itself to close a
session?

--

A Paul Anthony
Software Developer

Chris Spencer
16-Mar-2006, 01:37 PM
David

On the contrary. have used been using MSSQL and my own developed RDC class
for a few years now.
All Crystal Reports use Stored procs as their datasource not tables. This
allows MSSQL Server to do all the hard work as these procs create,
popuplate and return result sets from the temp tables. You can use mutliple
stored procs in a Crystal reort and link them with data that is common as
though they were 'real' tables.
However you are correct that these tables exist only for the duration of the
report but that is all that is needed

Chris Spencer
TUFware Systems

"David Martinko" <RedeemedSoftware@Hotmail.com> wrote in message
news:PbmqrgPSGHA.540@dacmail.dataaccess.com...
> So is there a way to Create a temp SQL table and populate it,.. then print
> from Crystal Reports? Sounds like a big NO.
>
> --
> David Martinko
> 248-535-7495
> Tracker Systems, Inc.
> Redeemed Software
> Redeemed Hosting
> www.trackersys.com
> www.redeemedsoftware.com
> www.redeemedhosting.com
> Proud member of the Northeast DataFlex Consortium: (NEDC)
> www.NEDataFlex.com
>
> "Peter Bragg" <tech@care-data.co.uk> wrote in message
> news:%23LZrQiOSGHA.3232@dacmail.dataaccess.com...
> Jose, Paul
>
> We create and use temporary tables, but do so in a multiple-statement
> query
> (if you follow) something along the lines of
>
> string sMultiStatementQuery
>
> move "create table #TEMP_NAME (seq integer identity, store integer, state
> varchar(100) .. ) ;" to sMultiStatementQuery
> move (sMultiStatementQuery+"insert into #TEMP_NAME (store,state) select
> .......... from ........... inner join ....... where ..... " ; ) to
> sMultiStatementQuery
> move (sMultiStatementQuery+"insert into ELSEWHERE ([sequence], store,
> state)
> select seq, store, state from #TEMP_NAME ;") to sMultiStatementQuery
> etc
>
> before eventually
>
> Send SQLExecDirect of ... sMultiStatementQuery
>
> As Paul also found, we were unable to create a Temp table and then user it
> in elsewhere, as only lasted for the "current execution" (probably the
> wrong
> terminology, but I'm sure you know what I mean). With a multi-statement
> query though (or whatever you call it) it is fine. Create it, and do what
> you like with it.
>
>
> Peter Bragg
>
>
> "A Paul Anthony" <Paul@asckey.com> wrote in message
> news:NrkWvzNSGHA.540@dacmail.dataaccess.com...
> José Aparecido Queiroz wrote:
> Hi!
>
> How can I create temp tables in MSSQL Server ?
>
> Thanks
>
> Seconded!
>
> According to Books Online temporary tables can be createb by prefixing the
> table name with a has, like this:
>
> create table #temp (
> i int identity(1,1),
> v decimal(20, 8)
> )
> The really convienient thing about this is that its scoped to the SQL
> session, even more granualar than just to the user, which is something I
> would like to take advantage of myself.
>
> To highjack the thread abit, I can execute the above statement via
> embedded
> SQL and it seems to create OK(no real way of telling as no other
> user/session can confirm it) but any later statements error denying its
> existance.
>
> To confirm that the other statements weren't broken I simply removed the #
> from the temporary table name (updating other references accordingly) and
> I
> got the expected results. And, yes, I am sure they're being executed in
> the
> same session.
>
> So, to add to José's question, 'How can I create and USE temp tables in
> MSSQL Server?'
>
>
> --
>
> A Paul Anthony
> Software Developer
>
>
>

Ben Weijers
16-Mar-2006, 02:45 PM
Apparantly you can't use these types of tables. Pitty, but that seems to be the way it is.

I am not sure what a "session" is. The SQL Server CK uses ODBC and a session is not a concept in ODBC

Regards,

Ben Weijers
Data Access Worldwide

David Martinko
16-Mar-2006, 02:57 PM
Is it possible to create a temp file, but not use the # prefix?

I'm not sure how this would work yet, but my plan is to model my
tempfile.pkg for DF tables. We will start by having the file exist in MSSQL
and in the filelist. Then we will, at runtime, create an exact replica of
the temporary table and give it a unique name. Then we will close the open
connection to the original table and open the new temporary table as the
original. Then we can do whatever we like to to it. I will also modify the
RDC class to know when we are dealing with a temporary table and be able to
change the table location to point to this temporary file. When we are done,
we will close the connection to the temporary table, destroy it and reopen
the original temporary file.

The tempfile.pkg is generic enough that it works for most everyone (that I
know of)... and modelling this will allow this to be a SQLTempfile.pkg, and
have the same flexibility.

--
David Martinko
248-535-7495
Tracker Systems, Inc.
Redeemed Software
Redeemed Hosting
www.trackersys.com
www.redeemedsoftware.com
www.redeemedhosting.com
Proud member of the Northeast DataFlex Consortium: (NEDC)
www.NEDataFlex.com

"José Aparecido Queiroz" <teknodados@superig.com.br> wrote in message
news:s6gPZFJSGHA.1276@dacmail.dataaccess.com...
Hi!

How can I create temp tables in MSSQL Server ?

Thanks

A Paul Anthony
17-Mar-2006, 04:40 AM
Ben Weijers wrote:

> I am not sure what a "session" is. The SQL Server CK uses ODBC and a
> session is not a concept in ODBC

Having probed Book Online a bit more, specifically constrained to only
shown me ODBC entries I found the topic "Orphaned Sessions" which does
seem to use "Session" and "Connection" interchangably. Can anyone
confirm or deny this?

> Apparantly you can't use these types of tables. Pitty, but that seems
> to be the way it is.

Again, since the help I'm looking at is specifically restricted to show
ODBC, anything I find should be compatable with the CK. As it happens
I'm using the SQL Server CK v4.1 and I would expect it to impliment at
least the set of features marked as ODBC, temporary tables being one of
them. I have deliberately ignored functions specific to the ADO set,
the intention being to try and use only the most common features and the
most compatable SQL.

--

A Paul Anthony
Software Developer

Peter Bragg
17-Mar-2006, 05:24 AM
... and another question to which I don't know (or rather have never bothered
to find out) the answer.

There are, of course, different types of Temporary Table - local and global
(I am reading from a help file here by the way!). Local temporary tables are
visible only in the current session; global temporary tables are visible to
all sessions. Prefix local temporary table names with single number sign
(#table_name), and prefix global temporary table names with a double number
sign (##table_name).

I have always used local ones - what happens here (do we know) if you use
Global ones? Any difference?

Peter Bragg


"A Paul Anthony" <Paul@asckey.com> wrote in message
news:Yg34ibaSGHA.5084@dacmail.dataaccess.com...
Ben Weijers wrote:

I am not sure what a "session" is. The SQL Server CK uses ODBC and a session
is not a concept in ODBC
Having probed Book Online a bit more, specifically constrained to only shown
me ODBC entries I found the topic "Orphaned Sessions" which does seem to use
"Session" and "Connection" interchangably. Can anyone confirm or deny this?


Apparantly you can't use these types of tables. Pitty, but that seems to be
the way it is.
Again, since the help I'm looking at is specifically restricted to show
ODBC, anything I find should be compatable with the CK. As it happens I'm
using the SQL Server CK v4.1 and I would expect it to impliment at least the
set of features marked as ODBC, temporary tables being one of them. I have
deliberately ignored functions specific to the ADO set, the intention being
to try and use only the most common features and the most compatable SQL.


--

A Paul Anthony
Software Developer

A Paul Anthony
17-Mar-2006, 07:09 AM
Peter Bragg wrote:

>.. and another question to which I don't know (or rather have never bothered
>to find out) the answer.
>
>There are, of course, different types of Temporary Table - local and global
>(I am reading from a help file here by the way!). Local temporary tables are
>visible only in the current session; global temporary tables are visible to
>all sessions. Prefix local temporary table names with single number sign
>(#table_name), and prefix global temporary table names with a double number
>sign (##table_name).
>
>I have always used local ones - what happens here (do we know) if you use
>Global ones? Any difference?
>
>Peter Bragg
>
>
I've played with these a bit with another developer and Query Analyser.
A temporary table of either sort only lasts for as long as the session
is open (a Query Analyser statement window is a session). When the
session is closed, a temporary table of either sort automatically self
destructs.

The difference between the local and global types is what it sounds like
- only the opening session can see the local temporary table. A
globally created temporary table can be seen across sessions and across
users.

Incidentally these tables are created in the tempdb database, not the
database you're working with. Using the QA's Object Browser you can
find the global temporary table. Unfortunately, due to the "Object
Browser" using its own session, you will not be able to view a local
temporary table. The other developer and I tried creating various sorts
of tables with various credentials and the logic seems fairly straight
forward. Local = viewable to that session only (even more specific than
to just that user), global = viewable to all sessions.

I can't quite see the point of a global temporary table, though I sure
some developers will think they're invaluable. Its the local temporary
table I interested in getting to work. Its scoping properties will very
useful.
--

A Paul Anthony
Software Developer

David Martinko
17-Mar-2006, 07:32 AM
another important question is... what if 2 users attempt to create the same
temp table? It can only be seen by that user, but can they both co-exist?

User A creates #Temp
User B creates #Temp
does this work? or fail? Would the tempfiles have to be unique anyways? or
can they co-exist?

--
David Martinko
248-535-7495
Tracker Systems, Inc.
Redeemed Software
Redeemed Hosting
www.trackersys.com
www.redeemedsoftware.com
www.redeemedhosting.com
Proud member of the Northeast DataFlex Consortium: (NEDC)
www.NEDataFlex.com

"A Paul Anthony" <Paul@asckey.com> wrote in message
news:Kb4V0ubSGHA.1280@dacmail.dataaccess.com...
Peter Bragg wrote:

>.. and another question to which I don't know (or rather have never
>bothered
>to find out) the answer.
>
>There are, of course, different types of Temporary Table - local and global
>(I am reading from a help file here by the way!). Local temporary tables
>are
>visible only in the current session; global temporary tables are visible to
>all sessions. Prefix local temporary table names with single number sign
>(#table_name), and prefix global temporary table names with a double number
>sign (##table_name).
>
>I have always used local ones - what happens here (do we know) if you use
>Global ones? Any difference?
>
>Peter Bragg
>
>
I've played with these a bit with another developer and Query Analyser.
A temporary table of either sort only lasts for as long as the session
is open (a Query Analyser statement window is a session). When the
session is closed, a temporary table of either sort automatically self
destructs.

The difference between the local and global types is what it sounds like
- only the opening session can see the local temporary table. A
globally created temporary table can be seen across sessions and across
users.

Incidentally these tables are created in the tempdb database, not the
database you're working with. Using the QA's Object Browser you can
find the global temporary table. Unfortunately, due to the "Object
Browser" using its own session, you will not be able to view a local
temporary table. The other developer and I tried creating various sorts
of tables with various credentials and the logic seems fairly straight
forward. Local = viewable to that session only (even more specific than
to just that user), global = viewable to all sessions.

I can't quite see the point of a global temporary table, though I sure
some developers will think they're invaluable. Its the local temporary
table I interested in getting to work. Its scoping properties will very
useful.
--

A Paul Anthony
Software Developer

Peter Bragg
17-Mar-2006, 07:38 AM
Yes, I know about the temdb database, because we had a situation recently
where a user had a different collating_sequence setting for their temdb
database to every other database, and was getting errors when trying to join
a temp table to a user table as a result... but that is another story (for
another day!)

Peter Bragg



"A Paul Anthony" <Paul@asckey.com> wrote in message
news:Kb4V0ubSGHA.1280@dacmail.dataaccess.com...
Peter Bragg wrote:
... and another question to which I don't know (or rather have never bothered
to find out) the answer.

There are, of course, different types of Temporary Table - local and global
(I am reading from a help file here by the way!). Local temporary tables are
visible only in the current session; global temporary tables are visible to
all sessions. Prefix local temporary table names with single number sign
(#table_name), and prefix global temporary table names with a double number
sign (##table_name).

I have always used local ones - what happens here (do we know) if you use
Global ones? Any difference?

Peter Bragg

I've played with these a bit with another developer and Query Analyser. A
temporary table of either sort only lasts for as long as the session is open
(a Query Analyser statement window is a session). When the session is
closed, a temporary table of either sort automatically self destructs.

The difference between the local and global types is what it sounds like -
only the opening session can see the local temporary table. A globally
created temporary table can be seen across sessions and across users.

Incidentally these tables are created in the tempdb database, not the
database you're working with. Using the QA's Object Browser you can find
the global temporary table. Unfortunately, due to the "Object Browser"
using its own session, you will not be able to view a local temporary table.
The other developer and I tried creating various sorts of tables with
various credentials and the logic seems fairly straight forward. Local =
viewable to that session only (even more specific than to just that user),
global = viewable to all sessions.

I can't quite see the point of a global temporary table, though I sure some
developers will think they're invaluable. Its the local temporary table I
interested in getting to work. Its scoping properties will very useful.

--

A Paul Anthony
Software Developer

A Paul Anthony
17-Mar-2006, 08:35 AM
David Martinko wrote:

>another important question is... what if 2 users attempt to create the same
>temp table? It can only be seen by that user, but can they both co-exist?
>
>User A creates #Temp
>User B creates #Temp
>does this work? or fail? Would the tempfiles have to be unique anyways? or
>can they co-exist?
>
>
Experiments suggest that yes, they can co-exist. The help explains that
the length of a temp table's name isslighly shorter than other tables
due to in teh background SQL Server appends a session ID (or someting)
on the end to make them unique. This is obervable in the tempdb. You
can see the "raw" table and their names when they're created but not
their contents.

--

A Paul Anthony
Software Developer

A Paul Anthony
17-Mar-2006, 08:53 AM
>
>
> Experiments suggest that yes, they can co-exist. The help explains
> that the length of a temp table's name isslighly shorter than other
> tables due to in teh background SQL Server appends a session ID (or
> someting) on the end to make them unique. This is obervable in the
> tempdb. You can see the "raw" table and their names when they're
> created but not their contents.


Please see attached screenshot of Query Analyser with two sessions open,
the first one creates a global temp table which they can both use. Each
session also creates its own local temp table and there are no clashes.

If you look at the Object Browser you can seen the managed/mangled temp
tables names. Please ignore the big empty block - company and database
names have been obfuscated to protect the guilty and our clients'
anonymity :-)

--

A Paul Anthony
Software Developer

merchantr
17-Mar-2006, 09:25 AM
David,

SQL Server internally assigns a unique numeric suffix to a temporary table
name. Typically, a table name can be 128 characters but temp tables have a
length of 116 to allow for this numeric suffix. The system ensures that each
user gets the correct temp table.

Riaz Merchant
Mertech Data Systems, Inc

Ben Weijers
17-Mar-2006, 02:46 PM
Looks like this is a "feature" of an ODBC connection. If you use a prepare and an execute you cant use local temporary tables (see http://support.microsoft.com/default.aspx?scid=kb;en-us;155818). This happens to be what the ESQL implementation does. We will look into changing this.

So for the moment, you cannot use local temporary tables.

Regards,

Ben Weijers
Data Access Worldwide

A Paul Anthony
22-Mar-2006, 10:05 AM
Ben Weijers wrote:

> Looks like this is a "feature" of an ODBC connection. If you use a
> prepare and an execute you cant use local temporary tables (see
> http://support.microsoft.com/default.aspx?scid=kb;en-us;155818). This
> happens to be what the ESQL implementation does. We will look into
> changing this.
>
> So for the moment, you cannot use local temporary tables.
>
> Regards,
>
> Ben Weijers
> Data Access Worldwide


Thanks for looking.

I've managed to implement what I wanted to using a global temporary
table manually mangled the table name to give it a scope, effectively
what SQL Server does itself in the background, though obviously not as
elegant. There are lots of comments in the code to replace the hack
when that functionality becomes available :-)

--

A Paul Anthony
Software Developer