Post reply

temporary tables

  • February 14, 2006 at 1:44 pm

    #171137

    I have a little asp script that keeps getting this error:

    SQL Server]Invalid object name '#tablenettemp'.

    My asp logic is:

    DropTable(tablenettemp)  'function to check if temp table exist, drop if it does

    strSQL = "select col1 AS JobNum, col2, col3 into #tablenettemp"    'select data into temp table

    strSQL = strSQL & "FROM tbl1"

    Set tempRs = Server.CreateObject("ADODB.RecordSet")      'connection string for temp table

    TempRs.Open strSQL, objCn

    strSQL = "select col1, col2, col3 FROM tbl2"

    strSQL = strSQL & "LEFT JOIN #tablenettemp AS cnt ON tbl2.Rid=JobNum"

    Set objRs = Server.CreateObject("ADODB.RecordSet")      'connection string for main query

    objRs.Open strSQL, objCn

     

    Now if I run this query in SQL Query Analyzer, it works.  But when I run it from the webpage, I get the error above.

    Can anyone see a flaw in my logic?

    Thanks

     

     

  • February 14, 2006 at 2:05 pm

    #620825

    What is the purpose of the 1st recordset (TempRS), if the SQL that is executed does not return a recordset ?

    Why do you need a #temp table when this can easily be expressed as 1 chunk of SQL, with a derived table in place of the temp table ?

    strSQL =

    "SELECT col1, col2, col3

    FROM tbl2

    LEFT JOIN

    (

      select col1 AS JobNum, col2, col3 FROM tbl1

    ) cnt

      AS cnt ON tbl2.Rid=JobNum "

    Set objRs = Server.CreateObject("ADODB.RecordSet")      'connection string for main query

    objRs.Open strSQL, objCn

  • February 14, 2006 at 2:35 pm

    #620839

    Everytime you run objRs.Open strSQL, objCn you open new connection.

    #table exists only within connection scope. As soon as you close connection (objRs.Open strSQL, objCn finished) #Table is dropped automatically. Next connection you open has nothing to do with # tables from another connection.

    _____________
    Code for TallyGenerator

  • February 14, 2006 at 2:51 pm

    #620846

    If you need temp tables, move the code to a stored procedure on the SQL server and call it from the .ASP code.

     

  • February 15, 2006 at 3:05 am

    #620934

     

    if you want to create a temporary table,

    then you have to use the following Query:--

     

    select col1,col2,col3 into #temptable

    from table

    other wise you have to create a temptable first.

     

     

     

  • February 15, 2006 at 6:41 am

    #621005

    Th question I have thou is why create the temp table at all? Based on the snippet of logic you should just simply left join tbl1 to tbl 2 instead and get the desired results.

  • February 16, 2006 at 8:58 pm

    #621535

    If possible you can use table varibale and this problem will not arise.

     

    regards

     

     

  • February 16, 2006 at 10:07 pm

    #621548

    Not quite true... table variables are also connection sensitive...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

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

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