Post reply

Creating table in Tempdb

  • March 30, 2004 at 8:35 am

    #65047

    Dear Friends,  

     I am working in VB and SQL server. While generating MIS reports i am dumping the data into temporary tables.  I am creating the table in tempdb with table name prefix with SPID so that no two user cannnot access same table.  My question, Till how much time the table exist in tempdb.  Some of my reports run more that 20 mins.  Please let me know this.

    Thanks in advance,

    Regards,

    Vijayendhar Reddy

  • March 31, 2004 at 1:48 am

    #501237

    You will find the information in BOL - look at the CREATE TABLE command.

  • March 31, 2004 at 2:38 am

    #501246

    Tables ( not the # or ## tables ) created in tempdb will exists till next restart of the server, as tempdb is rebuild from scratch at each start of the server.

  • March 31, 2004 at 7:47 am

    #501281

    Is there a reason you are creating directly into tempdb and not using # or ##? In any case, you have the option to drop the table when done, otherwise Bert is right, it sticks around until the restart of the server.

    K. Brian Kelley
    @kbriankelley

  • March 31, 2004 at 8:32 am

    #501301

    Hai,

     I am creating the tables in tempdb using ##. I am taking the @@SPID and adding to the name of the table before creating.  When run the report it will properly and report gives data.  When I rerun the same report the system is giving message that Table does not exist.   I am using Stored procedure to create table.  After getting message i tried to create the table from VB. Then also the same message is coming.  After restarting my application when i run the same report the report works properly.  If i go for second time the same message occurs.  I am not getting any messange while creating the table.

        Right now i am creating the temp tables in my live database and drop the same table after my work finish. Please let me know any problem in performance in the database.

    Regards,

    Vijayendhar Reddy

  • March 31, 2004 at 9:01 am

    #501309

    Global temporary tables (##) will stick around until the session ends (basically the connection is ended). Local temporary tables (#) will stick around to the end of the stored procedure.

    When you say you're re-running the report, each time the report is run, is a new table created? If so, are you closing connection after a run?

    K. Brian Kelley
    @kbriankelley

  • March 31, 2004 at 9:17 am

    #501315

    Hai,

      You are right. a new table created on each run.  We are using the global connection for whole module.  It will closed once the application terminated.

    Vijay

  • March 31, 2004 at 11:05 am

    #501339

    So one persistent connection... multiple runs within that connection.

    Okay, you probably want to run a Profiler trace just to verify the connection is staying open and the commands are being executed as you'd expect. Reason I say that is if you're creating a table on each new report run, there doesn't seem to be any reason for the table to suddenly not exist unless you get an error message on creation. Given that it's a global temporary table, it should be visible if it exists.

    K. Brian Kelley
    @kbriankelley

  • April 3, 2004 at 8:19 am

    #501696

    Hai,

       I found onething.  If i make new connection then the table created.  then i tried to create a table it is not created but no error message is giving.

      If i close my application then the same routine is coming.  Now i am creating the tables directly in the tempdb without # or ##.  Should i drop that tables also becuase my sql server will restart until unless we have problem in the server. 

     

    Regards,

    Vijayendhar Reddy

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply