Global Temp Table & Multiple Users

I have a stored procedure that *must* create a global
temporary table (##myTable), but the problem is that
there are multiple users which could be using this stored
procedure.
What best practices are there for dealing with this
problem?
Ideally, I want to queue up the execution if multiple
users all run the proc at the same time, rather than
return an IsRunning status of sorts and then waiting in
the application.
Any suggestions?
NOTE: I cannot change the name of the table dynamically
(i.e. '##myTable' + @.UserID) because of how I have to
deal with the data in the procedure (and nested
procedures)."Steven" <anonymous@.discussions.microsoft.com> wrote in message
news:0d9001c49c10$bc124420$a301280a@.phx.gbl...
> I have a stored procedure that *must* create a global
> temporary table (##myTable), but the problem is that
Why must it create a global temporary table? Is there truly no other
way for you to achieve your goal? Please post some more about your
situation. To date I've never seen any absolute requirement for a global
temporary table.|||Force each connection to do all of the processing related to the table
within a transaction, and at the start of the transaction get an exclusive
lock on the table. That will prevent mutliple connections from interfering
with the population and use of the table - but it will serialize them. Your
ideal design will require a pessimistic locking approach where the
application does some sort of checkout / reservation logic - nothing in sql
server that I know of that will do this for you.
The best approach is to avoid the global temp table, as Adam indicated.
"Steven" <anonymous@.discussions.microsoft.com> wrote in message
news:0d9001c49c10$bc124420$a301280a@.phx.gbl...
> I have a stored procedure that *must* create a global
> temporary table (##myTable), but the problem is that
> there are multiple users which could be using this stored
> procedure.
> What best practices are there for dealing with this
> problem?
> Ideally, I want to queue up the execution if multiple
> users all run the proc at the same time, rather than
> return an IsRunning status of sorts and then waiting in
> the application.
> Any suggestions?
> NOTE: I cannot change the name of the table dynamically
> (i.e. '##myTable' + @.UserID) because of how I have to
> deal with the data in the procedure (and nested
> procedures).
>|||This is kind of where I ended up going.
The reason that I have to use Global Temporary tables is
because one nested stored proc controls the creation of
the table and then the outer procedure uses the results.
Essentially, what is happening is this a dynamic SQL
query builder which uses the results of another query to
construct the SQL.
The inner proc generates a portion of the SQL, the outer
proc executes it, then when the execution is complete,
the outer proc uses it to complete the SQL.
After more careful review, I can actually create a
dynamically named global temporary table, but I would
have to build several more nested stored procedures to
pull it off. It is turning into an awful mess.
What would really be nice is
SELECT *
INTO #@.TableName
FROM Employees
(You listening MS')
All my problems would be solved.
I am going to try the transaction locking and see if
deadlocks result. Otherwise, I am going to have to go
deeper into nesting stored procs.
>--Original Message--
>Force each connection to do all of the processing
related to the table
>within a transaction, and at the start of the
transaction get an exclusive
>lock on the table. That will prevent mutliple
connections from interfering
>with the population and use of the table - but it will
serialize them. Your
>ideal design will require a pessimistic locking approach
where the
>application does some sort of checkout / reservation
logic - nothing in sql
>server that I know of that will do this for you.
>The best approach is to avoid the global temp table, as
Adam indicated.
>"Steven" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0d9001c49c10$bc124420$a301280a@.phx.gbl...
>> I have a stored procedure that *must* create a global
>> temporary table (##myTable), but the problem is that
>> there are multiple users which could be using this
stored
>> procedure.
>> What best practices are there for dealing with this
>> problem?
>> Ideally, I want to queue up the execution if multiple
>> users all run the proc at the same time, rather than
>> return an IsRunning status of sorts and then waiting in
>> the application.
>> Any suggestions?
>> NOTE: I cannot change the name of the table dynamically
>> (i.e. '##myTable' + @.UserID) because of how I have to
>> deal with the data in the procedure (and nested
>> procedures).
>>
>
>.
>|||One more thing....
Anyone see any problems with this method of creating a
temporary table?
DECLARE @.TempTable varchar(200)
SET @.TempTable = '##BaseName_' + REPLACE( CAST( NewID
() as varchar(80) ), '-', '' )
Seems to work even when two tables are created
milliseconds apart from one another.
Am I going to be in trouble later?
Thanks for all your help!
>--Original Message--
>Force each connection to do all of the processing
related to the table
>within a transaction, and at the start of the
transaction get an exclusive
>lock on the table. That will prevent mutliple
connections from interfering
>with the population and use of the table - but it will
serialize them. Your
>ideal design will require a pessimistic locking approach
where the
>application does some sort of checkout / reservation
logic - nothing in sql
>server that I know of that will do this for you.
>The best approach is to avoid the global temp table, as
Adam indicated.
>"Steven" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0d9001c49c10$bc124420$a301280a@.phx.gbl...
>> I have a stored procedure that *must* create a global
>> temporary table (##myTable), but the problem is that
>> there are multiple users which could be using this
stored
>> procedure.
>> What best practices are there for dealing with this
>> problem?
>> Ideally, I want to queue up the execution if multiple
>> users all run the proc at the same time, rather than
>> return an IsRunning status of sorts and then waiting in
>> the application.
>> Any suggestions?
>> NOTE: I cannot change the name of the table dynamically
>> (i.e. '##myTable' + @.UserID) because of how I have to
>> deal with the data in the procedure (and nested
>> procedures).
>>
>
>.
>|||Why don't you create a permanent table that uses uniqueidentifiers to track
calls? Then the inner proc can just return the correct uniqueidentifier to
the outer proc.
"Steven" <anonymous@.discussions.microsoft.com> wrote in message
news:278901c49c26$90228c10$a601280a@.phx.gbl...
> This is kind of where I ended up going.
> The reason that I have to use Global Temporary tables is
> because one nested stored proc controls the creation of
> the table and then the outer procedure uses the results.
> Essentially, what is happening is this a dynamic SQL
> query builder which uses the results of another query to
> construct the SQL.
> The inner proc generates a portion of the SQL, the outer
> proc executes it, then when the execution is complete,
> the outer proc uses it to complete the SQL.
> After more careful review, I can actually create a
> dynamically named global temporary table, but I would
> have to build several more nested stored procedures to
> pull it off. It is turning into an awful mess.
> What would really be nice is
> SELECT *
> INTO #@.TableName
> FROM Employees
> (You listening MS')
> All my problems would be solved.
> I am going to try the transaction locking and see if
> deadlocks result. Otherwise, I am going to have to go
> deeper into nesting stored procs.
> >--Original Message--
> >Force each connection to do all of the processing
> related to the table
> >within a transaction, and at the start of the
> transaction get an exclusive
> >lock on the table. That will prevent mutliple
> connections from interfering
> >with the population and use of the table - but it will
> serialize them. Your
> >ideal design will require a pessimistic locking approach
> where the
> >application does some sort of checkout / reservation
> logic - nothing in sql
> >server that I know of that will do this for you.
> >
> >The best approach is to avoid the global temp table, as
> Adam indicated.
> >
> >"Steven" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:0d9001c49c10$bc124420$a301280a@.phx.gbl...
> >> I have a stored procedure that *must* create a global
> >> temporary table (##myTable), but the problem is that
> >> there are multiple users which could be using this
> stored
> >> procedure.
> >>
> >> What best practices are there for dealing with this
> >> problem?
> >>
> >> Ideally, I want to queue up the execution if multiple
> >> users all run the proc at the same time, rather than
> >> return an IsRunning status of sorts and then waiting in
> >> the application.
> >>
> >> Any suggestions?
> >>
> >> NOTE: I cannot change the name of the table dynamically
> >> (i.e. '##myTable' + @.UserID) because of how I have to
> >> deal with the data in the procedure (and nested
> >> procedures).
> >>
> >>
> >
> >
> >.
> >|||Also:
http://www.sommarskog.se/share_data.html
"Steven" <anonymous@.discussions.microsoft.com> wrote in message
news:278901c49c26$90228c10$a601280a@.phx.gbl...
> This is kind of where I ended up going.
> The reason that I have to use Global Temporary tables is
> because one nested stored proc controls the creation of
> the table and then the outer procedure uses the results.|||dynamic table names will require the use of dynamic sql. be certain that
you need this before you start down this path. It is much easier to design
/ code / debug when the table name is static. You might also need to
"delimit" the table name if non-standard characters are included in the
name.
"Steven" <anonymous@.discussions.microsoft.com> wrote in message
news:279001c49c27$1c736a40$a601280a@.phx.gbl...
> One more thing....
> Anyone see any problems with this method of creating a
> temporary table?
> DECLARE @.TempTable varchar(200)
> SET @.TempTable = '##BaseName_' + REPLACE( CAST( NewID
> () as varchar(80) ), '-', '' )
> Seems to work even when two tables are created
> milliseconds apart from one another.
> Am I going to be in trouble later?
> Thanks for all your help!
> >--Original Message--
> >Force each connection to do all of the processing
> related to the table
> >within a transaction, and at the start of the
> transaction get an exclusive
> >lock on the table. That will prevent mutliple
> connections from interfering
> >with the population and use of the table - but it will
> serialize them. Your
> >ideal design will require a pessimistic locking approach
> where the
> >application does some sort of checkout / reservation
> logic - nothing in sql
> >server that I know of that will do this for you.
> >
> >The best approach is to avoid the global temp table, as
> Adam indicated.
> >
> >"Steven" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:0d9001c49c10$bc124420$a301280a@.phx.gbl...
> >> I have a stored procedure that *must* create a global
> >> temporary table (##myTable), but the problem is that
> >> there are multiple users which could be using this
> stored
> >> procedure.
> >>
> >> What best practices are there for dealing with this
> >> problem?
> >>
> >> Ideally, I want to queue up the execution if multiple
> >> users all run the proc at the same time, rather than
> >> return an IsRunning status of sorts and then waiting in
> >> the application.
> >>
> >> Any suggestions?
> >>
> >> NOTE: I cannot change the name of the table dynamically
> >> (i.e. '##myTable' + @.UserID) because of how I have to
> >> deal with the data in the procedure (and nested
> >> procedures).
> >>
> >>
> >
> >
> >.
> >|||I've done the something very similar for reporting purposes. Serialization
was acceptable since it was more important to use a consistent methodology
for the generation of the data (also, it was designed in v7 without a
complete set of requirements nor a good understanding of the data and how
ubiqitous it would become). In hindsight, it might have been better to use
regular temp tables, although that would have required a much different
design and I'm not sure if performance would be any better. The good part
about serialization is that it limits the dbms pounding that these reports
involve. Actually, the real problem is that the system is designed to
function as both an OLTP and a data warehouse - if only the requirements had
been known before coding started!
"Steven" <anonymous@.discussions.microsoft.com> wrote in message
news:278901c49c26$90228c10$a601280a@.phx.gbl...
> This is kind of where I ended up going.
> The reason that I have to use Global Temporary tables is
> because one nested stored proc controls the creation of
> the table and then the outer procedure uses the results.
> Essentially, what is happening is this a dynamic SQL
> query builder which uses the results of another query to
> construct the SQL.
> The inner proc generates a portion of the SQL, the outer
> proc executes it, then when the execution is complete,
> the outer proc uses it to complete the SQL.
> After more careful review, I can actually create a
> dynamically named global temporary table, but I would
> have to build several more nested stored procedures to
> pull it off. It is turning into an awful mess.
> What would really be nice is
> SELECT *
> INTO #@.TableName
> FROM Employees
> (You listening MS')
> All my problems would be solved.
> I am going to try the transaction locking and see if
> deadlocks result. Otherwise, I am going to have to go
> deeper into nesting stored procs.
> >--Original Message--
> >Force each connection to do all of the processing
> related to the table
> >within a transaction, and at the start of the
> transaction get an exclusive
> >lock on the table. That will prevent mutliple
> connections from interfering
> >with the population and use of the table - but it will
> serialize them. Your
> >ideal design will require a pessimistic locking approach
> where the
> >application does some sort of checkout / reservation
> logic - nothing in sql
> >server that I know of that will do this for you.
> >
> >The best approach is to avoid the global temp table, as
> Adam indicated.
> >
> >"Steven" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:0d9001c49c10$bc124420$a301280a@.phx.gbl...
> >> I have a stored procedure that *must* create a global
> >> temporary table (##myTable), but the problem is that
> >> there are multiple users which could be using this
> stored
> >> procedure.
> >>
> >> What best practices are there for dealing with this
> >> problem?
> >>
> >> Ideally, I want to queue up the execution if multiple
> >> users all run the proc at the same time, rather than
> >> return an IsRunning status of sorts and then waiting in
> >> the application.
> >>
> >> Any suggestions?
> >>
> >> NOTE: I cannot change the name of the table dynamically
> >> (i.e. '##myTable' + @.UserID) because of how I have to
> >> deal with the data in the procedure (and nested
> >> procedures).
> >>
> >>
> >
> >
> >.
> >