From vb6 i try to do this:

'STEP 1
sSql = "IF OBJECT_ID('tempdb..#tabella') IS NOT NULL DROP TABLE #tabella"
m_cn.Execute sSql, RowAff

'STEP 2
sSql = "SELECT top 0 * INTO #tabella_temp FROM tabella" & vbCrLf
m_cn.Execute sSql, RowAff

'STEP 3
sSql = "insert into #tabella_temp select * FROM tabella" & vbCrLf
m_cn.Execute sSql, RowAff

but at step 3 i've this ADO 2.7 error:
number: -2147217865
description: Invalid object name '#tabella_temp'.

why?

"zMatteo" <origma@edpsistem.it> wrote in message
news:dd31d194.0501140852.3e6d5188@posting.google.c om...
> From vb6 i try to do this:
>
> 'STEP 1
> sSql = "IF OBJECT_ID('tempdb..#tabella') IS NOT NULL DROP TABLE #tabella"
> m_cn.Execute sSql, RowAff
>
> 'STEP 2
> sSql = "SELECT top 0 * INTO #tabella_temp FROM tabella" & vbCrLf
> m_cn.Execute sSql, RowAff
>
> 'STEP 3
> sSql = "insert into #tabella_temp select * FROM tabella" & vbCrLf
> m_cn.Execute sSql, RowAff
>
> but at step 3 i've this ADO 2.7 error:
> number: -2147217865
> description: Invalid object name '#tabella_temp'.
>
> why?

It works fine for me in VBScript, but I guess you're doing something extra
between steps 2 and 3 (if not, then you could just remove "TOP 0" from step
2 and forget about step 3) - is the connection being closed at some point?

You could also use Profiler to check what's going on and see exactly what
commands are being sent to the server.

Simon

[posted and mailed, please reply in news]

zMatteo (origma@edpsistem.it) writes:
> From vb6 i try to do this:
>
> 'STEP 1
> sSql = "IF OBJECT_ID('tempdb..#tabella') IS NOT NULL DROP TABLE
> #tabella" m_cn.Execute sSql, RowAff
>
> 'STEP 2
> sSql = "SELECT top 0 * INTO #tabella_temp FROM tabella" & vbCrLf
> m_cn.Execute sSql, RowAff
>
> 'STEP 3
> sSql = "insert into #tabella_temp select * FROM tabella" & vbCrLf
> m_cn.Execute sSql, RowAff
>
> but at step 3 i've this ADO 2.7 error:
> number: -2147217865
> description: Invalid object name '#tabella_temp'.

ADO is trying to be helpful, but gets it all wrong. What it should
have done - in my opinion - is to yell and scream and shout that
you cannot execute any commands on that connection right now, because
you have unfetched results. This you have because you have not consumed
all your recordsets by looping with .NextRecords set. Neither did you
pass adExecuteNorRecords to execute, to inform that you don't care about
the result set. A third option would be to have run SET NOCOUNT ON
prior to the operation so that were would be no result sets. (The
result sets are really the row counts.)

What ADO does of yelling, is to open a second connection to SQL
Server behind your back. Sometimes this saves the show. In this
case it doesn't.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp