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

From: Dan Guzman (danguzman_at_nospam-earthlink.net)
Date: 05/06/04 

Date: Thu, 6 May 2004 08:21:04 -0500

> I am not using connection pooling.

Try adding 'Pooling=False' to your connection string and specifying "SET
NOCOUNT ON" in your script:

objConn.Execute "SET NOCOUNT ON " & _
    "SELECT * INTO ##TempScrip7569 " & _
    "FROM [FormsDevp]..Scrip WHERE 1=2" 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"Peri" <lperi@CSPL.com> wrote in message
news:uOwNyl2MEHA.936@TK2MSFTNGP11.phx.gbl...
> Hi,
>
> I am not using connection pooling. I am not able to create the temp table
> even at the first time.
>
> FYI - While execute the SQL in VB, I am not getting any error. It seeems
to
> get executed, but when I query the temp table either in VB or in SQL
server
> query anaylyser, I get the error "Invalid Object.."
>
> Thanks and Regards,
>
> Peri
>
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@avtenta.si> wrote in
> message news:uw8Lq$1MEHA.556@tk2msftngp13.phx.gbl...
> > 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: VB with SQL Server... Unable to create temp table using ADO connection object
    ... I am not using connection pooling. ... I am not able to create the temp table ... FYI - While execute the SQL in VB, I am not getting any error. ... but when I query the temp table either in VB or in SQL server ...
    (microsoft.public.sqlserver.programming)
  • ODBC Connection Pooling and Temp Tables
    ... Hi, i need to filla .NET Data Set with 3 related tables, the bottom ... And the fill the tables with joins to the TEMP table. ... I would like to work with/without Connection pooling support all ... Its possible that the first query goes thru a connection and laters ...
    (comp.databases.informix)
  • Re: connection options to yukon
    ... Connection pooling depends on the fact that repeated SqlConnection objects ... My recommendation would be to use Windows authentication over sql Auth ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: How to SELECT records based upon ASP.NET Roles
    ... connection pooling will work better than if a large ... as a SQL Login.) ... Row level permissions is kind a complicated topic to be implemented ... Since you can't pass parameters to views, you'll restrict the ...
    (microsoft.public.dotnet.security)
  • Re: ODBC Connection Pooling and Temp Tables
    ... Temp table is something which will be dropped once database session closes ... Now when you use connection pooling you are not ... Its possible that the first query goes thru a connection and laters ...
    (comp.databases.informix)