Re: Temp Table Problem

From: Brian Bushay TeamB (BBushay_at_Nmpls.com)
Date: 11/27/03

  • Next message: Brian Bushay TeamB: "Re: Multiple commands using tADOCommand" 
    Date: Wed, 26 Nov 2003 19:46:37 -0600

    >I have a query that creates a temp table on SQL Server 2K. The code resides
    >in a DLL.
    >Other queries then use this temp table.
    >
    >This was actually a unit in a working program that was converted to a DLL.
    >Problem is, now in the DLL, the temp table will not persist. When I try to
    >use it in other queries, it is gone. Wierd thing is, If I compile the DLL
    >into the directory where the source resides, then it works (When running
    >from D7 IDE). If I compile it to a different directory, then it doesn't
    >work.
    >
    >I have tried using Client and Server side cursors, niether works.

    SQL server has two kinds of temporary tables. Tables that start with # are
    local temporary tables visible only to the connection that creates them. The
    are deleted when the connection is closed.
    Table that start with ## are global temporary tables are available to any
    connection are deleted after all open connections using them are closed.
    If you are using the local Temporary tables there are a problems when you have
    the Adoconnection is set up to use connection Pooling. When you do this every
    time you execute a query you use a new connection and loose the ability to
    reconnect to temporary tables created in another connection.

    If you are using the Global temporary tables then the way you are using the Dll
    is closing the connection and the temporary table is being deleted because it is
    no longer being used. 

    --
Brian Bushay (TeamB)
Bbushay@NMPLS.com
  • Next message: Brian Bushay TeamB: "Re: Multiple commands using tADOCommand"

    Relevant Pages

    • Re: Sql Server 2000 slow
      ... If I close the connection (moving the Open/Close ... ADO) and the dll gives twice the performance. ... > Are there any known performance issues with .NET accessing Sql Server ...
      (microsoft.public.dotnet.framework.performance)
    • Re: How to navigate recursive stored procedures results
      ... connection is inherited by the next user, ... beforehand so any residual #Temp tables would be dropped. ... the identity is not "flow"ing to the SQL Server - ... union a SQL query and the results of a stored procedure. ...
      (microsoft.public.dotnet.framework.adonet)
    • Re: CoInitialize failure with many processes
      ... _connection object ... I'm surmising that you have a simple application that is accessing SQL Server using classic ADO. ... There are probably several ways of tackling it but I would recommend that a very simple COM DLL be written that calls the SQL Server on behalf of any client process. ...
      (microsoft.public.win32.programmer.ole)
    • Re: Temporary results used by SP
      ... If the application is using the same SQL Server connection, ... Tibor Karaszi, SQL Server MVP ... >>> if I use VB.NET and ADO.NET I can't see the temp table. ...
      (microsoft.public.sqlserver.programming)
    • Re: VB with SQL Server... Unable to create temp table using ADO connection object
      ... My guess is that you have connection pooling, so even if you close the ... > But I am not able to create a temp table with the same structure of the ... > Suprisingly, If I run the same query in the SQL Server query analyser, I ...
      (microsoft.public.sqlserver.programming)