Professional Web Applications Themes

Problems Using Temp Tables (##TEMPNAME) - Microsoft SQL / MS SQL Server

I have a stored procedure that can open different tables, calculate data, and return for reporting purposes. For this, I create a Temp table with the data from the main tables queried and grouped. From the Temp table I calculate the numbers I need to return. This is much faster and efficient than using joins and it works great. However, if 2 users are running the same report, the second user will get an error when the stored procedure is attempting to create the ##TEMPTABLE because the first request just created it. As a work around, I tried to create ...

  1. #1

    Default Problems Using Temp Tables (##TEMPNAME)

    I have a stored procedure that can open different tables, calculate data,
    and return for reporting purposes. For this, I create a Temp table with the
    data from the main tables queried and grouped. From the Temp table I
    calculate the numbers I need to return. This is much faster and efficient
    than using joins and it works great. However, if 2 users are running the
    same report, the second user will get an error when the stored procedure is
    attempting to create the ##TEMPTABLE because the first request just created
    it.

    As a work around, I tried to create unique temp tables, but then I cannot
    use them in select statements:

    Select * From TEMPTABLENAME - this fails.

    Furthermore, because the database has dozens of tables, any which can be
    used by the same stored procedure, I have to build my select statement on
    the fly as follows:
    Exec('Select ' + Field1 + ', ' + Field2 + ' Into ##TEMPTABLE From ' +
    TableName + ' Where ' + WhereClause + ' Group By ' + Field1 + ', ' +
    Field2) - This is the line that fails when 2 users call the same stored
    procedure before the first request drops ##TEMPTABLE.

    I also tried using table variables instead of temp tables; however, because
    I need to build the select statement on the fly, I cannot load a table
    variable with an executable string like this:
    Insert Into TempTable
    exec('Select ' + Field1+ ', ' + Field2 + ' From ' + TableName + ' Where '
    + WhereClause + ' Group By ' + Field1 + ', ' + Field2) - this also fails.

    I do not want to create stored procedures for every possible table because
    the program is dynamic (users can add new components which then create new
    tables).

    Any ideas?

    Thanks
    JND


    JNDiaz Guest

  2. #2

    Default Re: Problems Using Temp Tables (##TEMPNAME)


    .. . . 

    Try using local temp tables (#TEMPTABLE instead of ##TEMPTABLE) or table
    variables.

    David


    David Guest

  3. #3

    Default Problems Using Temp Tables (##TEMPNAME)

    have u tried on #. This makes teh temp table session based
    rather than global.
    Also ensure the tables are dropped after use.
     
    calculate data, 
    Temp table with the 
    Temp table I 
    faster and efficient 
    are running the 
    stored procedure is 
    request just created 
    but then I cannot 
    any which can be 
    select statement on 
    ##TEMPTABLE From ' + 
    Field1 + ', ' + 
    the same stored 
    tables; however, because 
    load a table 
    TableName + ' Where ' 
    Field2) - this also fails. 
    possible table because 
    which then create new 
    Stu Guest

Similar Threads

  1. SP Temp Tables
    By navdeep virk in forum Informix
    Replies: 6
    Last Post: February 18th, 04:30 PM
  2. CF 6.1, cfquery, MySQL, and temp tables
    By datac0re in forum Coldfusion Database Access
    Replies: 3
    Last Post: September 6th, 05:11 PM
  3. Location of temp tables
    By Andy in forum Informix
    Replies: 2
    Last Post: February 6th, 03:08 PM
  4. Temp tables and jdbc 2.21.jc3 on IDS 9.4.UC1
    By Rajesh Kapur in forum Informix
    Replies: 0
    Last Post: August 26th, 09:59 PM
  5. Accessing Temp Tables
    By Karthik Nagaraj in forum Microsoft SQL / MS SQL Server
    Replies: 6
    Last Post: July 12th, 11:26 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139