uniqueness of global names 
Author Message
 uniqueness of global names

What's the best way to guarantee uniqueness of global names in T-SQL?

I have several procs which CREATE TABLE ##gTabName.  I've written these

variety of conditions.  The temp tables are only available to later
processing within the proc when they are globals.  I then drop the table at
the end of the proc.

All this works fine.  I'm concerned about multiple simultaneous users of the
proc.  Since the ##gTabName is global, I'm pretty sure any attempt to create
the table for a second user will fail while the proc is running for the
first user (although I haven't yet tested this).  I'm pretty sure this
wouldn't happen for a local temp table such as #TabNme, but I don't seem to

the global temp table in a way that guarantees uniqueness, so that a second
user would not be attempting to create a table that already exists.

I'm in an application-driven, SQL Authentication environment with SQL Server
7.0, where the clients will be ASP pages connecting through a DSN-less ADO
connection with a hard-coded UID and PW (not my choice of connection
architectures, but that's another story).  The client apps could be
executing within Netscape or IE, on any operating system.  Only the procs
are made available to this userid.  So I don't think I have variables like
USER available to me to determine uniqueness.

I've thought about just retrieving getdate(), and parsing it into an

I'm sure I can do that, and for all intents and purposes that should solve
my problem.

My questions:  Does getdate() guarantee uniqueness?  Is there a better, more
elegant way to do this?  And if I can't guarantee uniqueness, how should I
construct the IF exists test for the existence of the global temp table so
that it loops somehow and comes back and tests again milliseconds later?

A related question:  If somehow the app crashes, leaving the ##TempTable
open, how long will it stay open?  How can I find out which global temp
tables currently exist?  How can I write a maintenance proc to drop all
global temp tables?

Thanks for your help.



Thu, 05 Dec 2002 03:00:00 GMT
 uniqueness of global names

Irv,

If I understand you correctly, you need the tables to be unique across
connections, but the tables are really connection-specific. If that is correct,
how about something like ...



---------------------------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc) which can be
cut and pasted into Query Analyzer is appreciated.


Quote:
> What's the best way to guarantee uniqueness of global names in T-SQL?

> I have several procs which CREATE TABLE ##gTabName.  I've written these

> variety of conditions.  The temp tables are only available to later
> processing within the proc when they are globals.  I then drop the table at
> the end of the proc.

> All this works fine.  I'm concerned about multiple simultaneous users of the
> proc.  Since the ##gTabName is global, I'm pretty sure any attempt to create
> the table for a second user will fail while the proc is running for the
> first user (although I haven't yet tested this).  I'm pretty sure this
> wouldn't happen for a local temp table such as #TabNme, but I don't seem to

> the global temp table in a way that guarantees uniqueness, so that a second
> user would not be attempting to create a table that already exists.

> I'm in an application-driven, SQL Authentication environment with SQL Server
> 7.0, where the clients will be ASP pages connecting through a DSN-less ADO
> connection with a hard-coded UID and PW (not my choice of connection
> architectures, but that's another story).  The client apps could be
> executing within Netscape or IE, on any operating system.  Only the procs
> are made available to this userid.  So I don't think I have variables like
> USER available to me to determine uniqueness.

> I've thought about just retrieving getdate(), and parsing it into an

> I'm sure I can do that, and for all intents and purposes that should solve
> my problem.

> My questions:  Does getdate() guarantee uniqueness?  Is there a better, more
> elegant way to do this?  And if I can't guarantee uniqueness, how should I
> construct the IF exists test for the existence of the global temp table so
> that it loops somehow and comes back and tests again milliseconds later?

> A related question:  If somehow the app crashes, leaving the ##TempTable
> open, how long will it stay open?  How can I find out which global temp
> tables currently exist?  How can I write a maintenance proc to drop all
> global temp tables?

> Thanks for your help.



Thu, 05 Dec 2002 03:00:00 GMT
 uniqueness of global names
Yep, perfect.  Thanks.  SPID will do it.
Do you have any insight into the "search for all global temp tables and drop
them" question?

Quote:
> Irv,

> If I understand you correctly, you need the tables to be unique across
> connections, but the tables are really connection-specific. If that is
correct,
> how about something like ...



> ---------------------------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc) which
can be
> cut and pasted into Query Analyzer is appreciated.



> > What's the best way to guarantee uniqueness of global names in T-SQL?

> > I have several procs which CREATE TABLE ##gTabName.  I've written these

> > variety of conditions.  The temp tables are only available to later
> > processing within the proc when they are globals.  I then drop the table
at
> > the end of the proc.

> > All this works fine.  I'm concerned about multiple simultaneous users of
the
> > proc.  Since the ##gTabName is global, I'm pretty sure any attempt to
create
> > the table for a second user will fail while the proc is running for the
> > first user (although I haven't yet tested this).  I'm pretty sure this
> > wouldn't happen for a local temp table such as #TabNme, but I don't seem
to

name
> > the global temp table in a way that guarantees uniqueness, so that a
second
> > user would not be attempting to create a table that already exists.

> > I'm in an application-driven, SQL Authentication environment with SQL
Server
> > 7.0, where the clients will be ASP pages connecting through a DSN-less
ADO
> > connection with a hard-coded UID and PW (not my choice of connection
> > architectures, but that's another story).  The client apps could be
> > executing within Netscape or IE, on any operating system.  Only the
procs
> > are made available to this userid.  So I don't think I have variables
like
> > USER available to me to determine uniqueness.

> > I've thought about just retrieving getdate(), and parsing it into an

> > I'm sure I can do that, and for all intents and purposes that should
solve
> > my problem.

> > My questions:  Does getdate() guarantee uniqueness?  Is there a better,
more
> > elegant way to do this?  And if I can't guarantee uniqueness, how should
I
> > construct the IF exists test for the existence of the global temp table
so
> > that it loops somehow and comes back and tests again milliseconds later?

> > A related question:  If somehow the app crashes, leaving the ##TempTable
> > open, how long will it stay open?  How can I find out which global temp
> > tables currently exist?  How can I write a maintenance proc to drop all
> > global temp tables?

> > Thanks for your help.



Thu, 05 Dec 2002 03:00:00 GMT
 uniqueness of global names
Irv,

From the SQL Server 7.0 Books Online, section 'CREATE TABLE (T-SQL)" ...

"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."

---------------------------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc) which can be
cut and pasted into Query Analyzer is appreciated.


Quote:
> Yep, perfect.  Thanks.  SPID will do it.
> Do you have any insight into the "search for all global temp tables and drop
> them" question?


> > Irv,

> > If I understand you correctly, you need the tables to be unique across
> > connections, but the tables are really connection-specific. If that is
> correct,
> > how about something like ...



> > ---------------------------------------------------------------
> > BP Margolin
> > Please reply only to the newsgroups.
> > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc) which
> can be
> > cut and pasted into Query Analyzer is appreciated.



> > > What's the best way to guarantee uniqueness of global names in T-SQL?

> > > I have several procs which CREATE TABLE ##gTabName.  I've written these

> > > variety of conditions.  The temp tables are only available to later
> > > processing within the proc when they are globals.  I then drop the table
> at
> > > the end of the proc.

> > > All this works fine.  I'm concerned about multiple simultaneous users of
> the
> > > proc.  Since the ##gTabName is global, I'm pretty sure any attempt to
> create
> > > the table for a second user will fail while the proc is running for the
> > > first user (although I haven't yet tested this).  I'm pretty sure this
> > > wouldn't happen for a local temp table such as #TabNme, but I don't seem
> to

> name
> > > the global temp table in a way that guarantees uniqueness, so that a
> second
> > > user would not be attempting to create a table that already exists.

> > > I'm in an application-driven, SQL Authentication environment with SQL
> Server
> > > 7.0, where the clients will be ASP pages connecting through a DSN-less
> ADO
> > > connection with a hard-coded UID and PW (not my choice of connection
> > > architectures, but that's another story).  The client apps could be
> > > executing within Netscape or IE, on any operating system.  Only the
> procs
> > > are made available to this userid.  So I don't think I have variables
> like
> > > USER available to me to determine uniqueness.

> > > I've thought about just retrieving getdate(), and parsing it into an
> > > acceptable identifier and naming the table (##TabName +

> > > I'm sure I can do that, and for all intents and purposes that should
> solve
> > > my problem.

> > > My questions:  Does getdate() guarantee uniqueness?  Is there a better,
> more
> > > elegant way to do this?  And if I can't guarantee uniqueness, how should
> I
> > > construct the IF exists test for the existence of the global temp table
> so
> > > that it loops somehow and comes back and tests again milliseconds later?

> > > A related question:  If somehow the app crashes, leaving the ##TempTable
> > > open, how long will it stay open?  How can I find out which global temp
> > > tables currently exist?  How can I write a maintenance proc to drop all
> > > global temp tables?

> > > Thanks for your help.



Thu, 05 Dec 2002 03:00:00 GMT
 uniqueness of global names
Right.  I know that quote.

But what I've found is that if a proc that created a global temp table
crashes out, and I haven't exited gracefully and explicitly dropped the temp
table, any future invocations of that proc, even from another session, fails
because the CREATE TABLE returns the message ""Table ##gTableName already
exists".  I'm not in an environment where I can absolutely guarantee that I
will never crash out of a proc because the user controls data that is used
in the construction of SQL strings which are then EXEC'd.

For example, in the middle of a proc I'm calculating Average Occupancy by
dividing Actual Occupancy by Total Rentable Area.  The invoking app makes it
impossible to have a Total Rentable Area of 0, but sure enough, I've run
into some situations where that's what's in the data.  So the proc fails.
The global temp table that was created earlier in the proc is still open,
and unless I close the session and re-establish a new connection, I have to
explicitly drop the table.

Now this has been from within the development environment, using QA and EM.
Perhaps what we will need to do in the app environment is when trapping
errors within the script, to always close the ADO connection and re-open it,
which should end the session and thus drop the temp global table.  This may
cause a slight problem, but seems like a workable solution.  Any other
ideas.

Thanks so much for your input.  It has been really helpful.

Quote:
> Irv,

> From the SQL Server 7.0 Books Online, section 'CREATE TABLE (T-SQL)" ...

> "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."

> ---------------------------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc) which
can be
> cut and pasted into Query Analyzer is appreciated.



> > Yep, perfect.  Thanks.  SPID will do it.
> > Do you have any insight into the "search for all global temp tables and
drop
> > them" question?


> > > Irv,

> > > If I understand you correctly, you need the tables to be unique across
> > > connections, but the tables are really connection-specific. If that is
> > correct,
> > > how about something like ...



> > > ---------------------------------------------------------------
> > > BP Margolin
> > > Please reply only to the newsgroups.
> > > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc)
which
> > can be
> > > cut and pasted into Query Analyzer is appreciated.



> > > > What's the best way to guarantee uniqueness of global names in
T-SQL?

> > > > I have several procs which CREATE TABLE ##gTabName.  I've written
these

a
> > > > variety of conditions.  The temp tables are only available to later
> > > > processing within the proc when they are globals.  I then drop the
table
> > at
> > > > the end of the proc.

> > > > All this works fine.  I'm concerned about multiple simultaneous
users of
> > the
> > > > proc.  Since the ##gTabName is global, I'm pretty sure any attempt
to
> > create
> > > > the table for a second user will fail while the proc is running for
the
> > > > first user (although I haven't yet tested this).  I'm pretty sure
this
> > > > wouldn't happen for a local temp table such as #TabNme, but I don't
seem
> > to

> > name
> > > > the global temp table in a way that guarantees uniqueness, so that a
> > second
> > > > user would not be attempting to create a table that already exists.

> > > > I'm in an application-driven, SQL Authentication environment with
SQL
> > Server
> > > > 7.0, where the clients will be ASP pages connecting through a
DSN-less
> > ADO
> > > > connection with a hard-coded UID and PW (not my choice of connection
> > > > architectures, but that's another story).  The client apps could be
> > > > executing within Netscape or IE, on any operating system.  Only the
> > procs
> > > > are made available to this userid.  So I don't think I have
variables
> > like
> > > > USER available to me to determine uniqueness.

> > > > I've thought about just retrieving getdate(), and parsing it into an
> > > > acceptable identifier and naming the table (##TabName +

> > > > I'm sure I can do that, and for all intents and purposes that should
> > solve
> > > > my problem.

> > > > My questions:  Does getdate() guarantee uniqueness?  Is there a
better,
> > more
> > > > elegant way to do this?  And if I can't guarantee uniqueness, how
should
> > I
> > > > construct the IF exists test for the existence of the global temp
table
> > so
> > > > that it loops somehow and comes back and tests again milliseconds
later?

> > > > A related question:  If somehow the app crashes, leaving the
##TempTable
> > > > open, how long will it stay open?  How can I find out which global
temp
> > > > tables currently exist?  How can I write a maintenance proc to drop
all
> > > > global temp tables?

> > > > Thanks for your help.



Fri, 06 Dec 2002 03:00:00 GMT
 uniqueness of global names
You can include a conditional drop of the global temp table before the
create.  For example:

IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE name = '##TableName') DROP
TABLE ##TableName

Hope this helps.



Fri, 06 Dec 2002 03:00:00 GMT
 uniqueness of global names
That's great.  Thanks.
I just didn't know where I would find the ##Table ... if it actually was an
object that I could reference in tempdb.


multiple users ... everything worked great.



Thanks again for your help.  I hope some other folks in the ng found your
info useful as well.


Quote:
> You can include a conditional drop of the global temp table before the
> create.  For example:

> IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE name = '##TableName')
DROP
> TABLE ##TableName

> Hope this helps.



Fri, 06 Dec 2002 03:00:00 GMT
 
 [ 7 post ] 

 Relevant Pages 

1. difference between uniqueness constraint and uniqueness index

2. Uniqueness of rule, constraint, and trigger names

3. Uniqueness of rule, constraint, and trigger names

4. Global Database Name, Database Name, and SID

5. Change domain name in global name

6. Creating a global named set in Analysis Services with MDX

7. Global temp table issue, occasional invalid object name error

8. tempdb naming scope for Primary Key constraints *global* on local #temptables

9. global name in replication

10. Using Global Names

11. Database Link creation with Global name TRUE

12. Database link and global names


 
Powered by phpBB® Forum Software