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

From: Peri (lperi_at_CSPL.com)
Date: 05/24/04 

Date: Mon, 24 May 2004 16:03:06 +0530

Thanks. It worked.

Regards,

Peri

"Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message
news:eCY71HpNEHA.2480@tk2msftngp13.phx.gbl...
> > SQL:BatchCompleted SELECT * INTO ##TempScrip7301 FROM [FORMSDCBS]..Scrip
> > WHERE 1=2 Visual Basic sa 16 507 1 16 2596 59 2004-05-10 14:05:57.340
> > Audit Logout Visual Basic sa 16 517 1 16 2596 59 2004-05-10
14:05:57.340
>
> The trace shows that spid 59 disconnects immediately after the global temp
> table is created. This will drop the global table created on that
> connection assuming it is not actively being used on a different
connection.
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "Peri" <lperi@CSPL.com> wrote in message
> news:O$pGTrmNEHA.3380@TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > This is the copy of the trace message.
> >
> > Audit Logout Visual Basic sa 0 16 0 0 2596 59 2004-05-10 14:05:45.170
> > Audit Login -- network protocol: TCP/IP
> > set quoted_identifier on
> > set implicit_transactions off
> > set cursor_close_on_commit off
> > set ansi_warnings on
> > set ansi_padding on
> > set ansi_nulls on
> > set concat_null_yields_null on
> > set language us_english
> > set dateformat mdy
> > set datefirst 7
> > Visual Basic sa 2596 59 2004-05-10 14:05:57.340
> > SQL:BatchStarting SELECT * INTO ##TempScrip7301 FROM [FORMSDCBS]..Scrip
> > WHERE 1=2 Visual Basic sa 2596 59 2004-05-10 14:05:57.340
> > SQL:StmtStarting SELECT * INTO ##TempScrip7301 FROM [FORMSDCBS]..Scrip
> WHERE
> > 1=2 Visual Basic sa 2596 59 2004-05-10 14:05:57.357
> > SQL:StmtCompleted SELECT * INTO ##TempScrip7301 FROM [FORMSDCBS]..Scrip
> > WHERE 1=2 Visual Basic sa 0 480 1 0 2596 59 2004-05-10 14:05:57.357
> > SQL:BatchCompleted SELECT * INTO ##TempScrip7301 FROM [FORMSDCBS]..Scrip
> > WHERE 1=2 Visual Basic sa 16 507 1 16 2596 59 2004-05-10 14:05:57.340
> > Audit Logout Visual Basic sa 16 517 1 16 2596 59 2004-05-10
14:05:57.340
> >
> > Can you help me out ?
> >
> > Thanks and Regards,
> >
> > Peri
> >
> > "Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message
> > news:eTtLfN9MEHA.3192@TK2MSFTNGP11.phx.gbl...
> > > I tested the technique using VBScript and got the expected results.
I
> > > assume VB would behave similarly.
> > >
> > > You might run a Profiler trace to ensure the connection is not closed
> > after
> > > the SELECT ... INTO since that will implicitly drop the table.
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > "Peri" <lperi@CSPL.com> wrote in message
> > > news:eMgBTF4MEHA.3292@TK2MSFTNGP11.phx.gbl...
> > > > Hi,
> > > >
> > > > No, it didn't work. Can you suggest some other way.
> > > >
> > > > Thanks and Regards,
> > > >
> > > > Peri
> > > >
> > > > "Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message
> > > > news:unHJ9z2MEHA.1348@TK2MSFTNGP12.phx.gbl...
> > > > > > 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