Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Location
    Tasmania
    Posts
    58

    Unanswered: local temp vs global temp table as adodc source

    Using vb6, ADO data control 6.0 (sp6) & sql server 2000:

    Why can a data control find a global temp table (##name) but not a local temp table (#name)?

    I create a temp table in VB via ADO & set the record source of an ADO Data control as this file.

    This works with global temp table - ##name, but not with local temp table - #name

    1. create temp table:
    set rst = New ADODB.Recordset
    strSql = "CREATE TABLE #GridData " ...
    rst.Open strSql, conn, adOpenForwardOnly, adLockOptimistic, adCmdText

    2. point data control to temp table:
    I have set the record string for the data control, at design time, to point
    to tempdb & at run time set the record source to the temp table:

    Me.Adodc1.CommandType = adCmdText
    strSql = "select * from #GridData "
    Me.Adodc1.RecordSource = strSql

    3. refresh data control:
    Me.Adodc1.Recordset.Requery
    Me.Adodc1.Refresh

    The refresh gives the error: Invalid object name '#GridData'

    When I do the above using ##GridData rather than #GridData it works fine.

    I've tired pointing my connection (conn) for creating the file to tempdb & to the user db, & I've tried the same with the adodc properties (this uses udl file), pointing to tempdb & to user db.

    Any advice on what else to try?

    Thanks

    Colin
    catkins

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The problem lies in how connection pooling works. A local temp table (#) exists as long as the current spid has a reference to it, either the duration of a stored procedure if the temp table is created within that procedure, or for the duration of the connection if the temp table was created outside of a procedure. A global temp table (using ##) exists as long as any spid still has a reference to the object.

    The local temp table can only be accessed by the client thread that created it, because a new connection will use a different thread context. A global temp table can be used until the old connection is disposed(), which can range from milli-seconds to minutes.

    The solution is to create and return the temp table within a stored procedure, or use one of the schemes that lets you use a regular table as though it were a temp table.

    -PatP

  3. #3
    Join Date
    Dec 2003
    Location
    Tasmania
    Posts
    58
    Thanks Pat.

    If I create the temp table in a stored procedure will that be any different as far as my data control is concerned, given that it uses a connection defined in the udl file?

    And what are the schemes for using a regular table as though it were a temp table?

    Colin
    catkins

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    No difference that I can see caused by using the stored procedure and accessing it via UDL. Just create the procedure that you need, do your "data dancing" (creating the temp table, populating it, manipulating it) within the procedure, and life is good.

    There are many different ways to treat a permanent table as a temp table. The key trick is to allow different sets of data to safely reside within the table simultaneously. The easiest way to do that is to add a column to the table to differentiate between different sets of data. My preference for a set id is to use a GUID column. If you do this, you need to periodically "flush" the table. My preference for the flush is to create a startup procedure that simply empties the table (when the database starts up, nothing could have been using it), although some people prefer a scheduled job to do this.

    -PatP

Posting Permissions

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