Re: Global Temp Table & Multiple Users

From: Scott Morris (bogus_at_bogus.com)
Date: 09/17/04 

Date: Fri, 17 Sep 2004 09:27:28 -0400

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

Relevant Pages

  • 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: Variable server names
    ... My initial desire was to do this in a single stored proc. ... Then I realized that mixing dynamic SQL and a table variable was going ... executing the remote from the local. ... but I understood it as management wanted to connect to a server ...
    (microsoft.public.sqlserver.programming)
  • Re: Advice on persistent storage in Java?
    ... >>I need some advice on persistent storage in Java. ... objects are meant to store user defined data (the user defines the ... the SQL would probably all be pretty basic. ... i'm thinking about using serialization for now ...
    (comp.lang.java.programmer)
  • 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)