Re: Global Temp Table & Multiple Users

From: Steven (anonymous_at_discussions.microsoft.com)
Date: 09/16/04 

Date: Thu, 16 Sep 2004 12:51:31 -0700

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

Relevant Pages

  • Re: Global Temp Table & Multiple Users
    ... design and I'm not sure if performance would be any better. ... about serialization is that it limits the dbms pounding that these reports ... what is happening is this a dynamic SQL ... > The inner proc generates a portion of the SQL, ...
    (microsoft.public.sqlserver.server)
  • Re: Urgent: Permissions Problem with Dynamic SQL
    ... > having problems with permissions when using dynamic sql. ... > CREATE PROC a2 AS ... > GRANT EXECUTE ON a2 TO user1 ...
    (microsoft.public.sqlserver.security)
  • Re: Views vs Stored Procedures, whats the difference?
    ... In the proc I would write the MAX as you've done. ... @optional_parm1 int = NULL, ... While the above does contain logic, it will give you the best plan in MS SQL ... CREATE TABLE P(pk INT NOT NULL PRIMARY KEY, ...
    (comp.databases.ms-sqlserver)
  • Re: how to pass an sort by parameter to a stored proc
    ... When executing a string the are not optional. ... Also if security is of any concern, this proc is very exposed to SQL ... >> CREATE PROCEDURE spListUsers ...
    (microsoft.public.sqlserver)
  • Re: extended stored proc programming
    ... inserting when back in SQL, as it's not having to constantly cross ... I wrote a load of SQL code that instantiates the COM object ... > I'm curious as to why you want to retrieve and insert the rows within the ... > extended proc rather than retrieve the rows, ...
    (microsoft.public.dotnet.languages.vc)