Re: VB with SQL Server... Unable to create temp table using ADO connection object

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Dejan Sarka (dejan_please_reply_to_newsgroups.sarka_at_avtenta.si)
Date: 05/06/04 

Date: Thu, 6 May 2004 13:47:29 +0200

My guess is that you have connection pooling, so even if you close the
connection from the app, it is still in the pool and alive. So the global
temp table is not destroyed, and when you try to create next time, the
creation fails. 

-- 
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Peri" <lperi@CSPL.com> wrote in message
news:eyCkDh1MEHA.620@TK2MSFTNGP10.phx.gbl...
> Hi,
>
> This is the connection string I am using:
>
> objConn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security
> Info=False;User ID=sa;Initial Catalog=FormsDevp;Data Source=CSPL;Use
> Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation
> ID=PERI;Use Encryption for Data=False;Tag with column collation when
> possible=False"
>
> I tried using all the 3 sql statements given below.
> objConn.Execute "SELECT * INTO ##TempScrip7569 FROM [FormsDevp]..Scrip
WHERE
> 1=2"
> OR
> objConn.Execute "SELECT * INTO ##TempScrip7569 FROM Scrip WHERE 1=2"
> OR
> objConn.Execute "SELECT * INTO ##TempScrip7569 FROM Scrip WHERE ScripCode
IS
> NULL"
>
> But I am not able to create a temp table with the same structure of the
> original table "Scrip" with empty records.
>
> Suprisingly, If I run the same query in the SQL Server query analyser, I
am
> able to see the temp table.
>
> FYI - Reference to the Project - Microsoft ActiveX Data Objects 2.5
Library.
>
> Can any one help me to overcome this ?
>
> Thanks and Regards,
>
> Peri
>
>

Relevant Pages

  • 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: Temp Table Problem
    ... >I have a query that creates a temp table on SQL Server 2K. ... >This was actually a unit in a working program that was converted to a DLL. ... SQL server has two kinds of temporary tables. ... local temporary tables visible only to the connection that creates them. ...
    (borland.public.delphi.database.ado)
  • Re: Connection Pooling in ASP
    ... print '#tmp exists - dropping it now' ... >> A pooled connection is a single connection. ... the users would have to be using separate temp tables. ... >> the pool, ...
    (microsoft.public.inetserver.asp.db)
  • 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: tempdb and #temp tables
    ... And the reason is that temp tables are unique to the creating connection. ... > profiler you can clearly see the code executed from Enterprise manager. ... >> remove items from a stock are put in that table. ...
    (microsoft.public.sqlserver.server)