By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,265 Members | 1,343 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,265 IT Pros & Developers. It's quick & easy.

Correct access to TempDB?

P: n/a
uff... I've another problem....
In this loop I've same ADO 2.7 error (number: -2147217865,
description: Invalid object name '#tabella_temp') at STEP 3:

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

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

'STEP --READ DATA--
sSql = "SELECT IdRow from TabellaIn"
rs.Open sSql, m_cn, adOpenForwardOnly, adLockReadOnly

Do While Not rs.EOF
'--STEP 3--
sSql = "insert into #tabella_temp (row) values (" & rs("IdRow") &
")"
m_cn.Execute sSql, RowAff, adExecuteNoRecords
rs.MoveNext
Loop
rs.CLose
why , why, why???
Jul 23 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
zMatteo (or****@edpsistem.it) writes:
uff... I've another problem....
In this loop I've same ADO 2.7 error (number: -2147217865,
description: Invalid object name '#tabella_temp') at STEP 3:

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

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

'STEP --READ DATA--
sSql = "SELECT IdRow from TabellaIn"
rs.Open sSql, m_cn, adOpenForwardOnly, adLockReadOnly

Do While Not rs.EOF
'--STEP 3--
sSql = "insert into #tabella_temp (row) values (" & rs("IdRow") &
")"
m_cn.Execute sSql, RowAff, adExecuteNoRecords
rs.MoveNext
Loop
rs.CLose
why , why, why???


Seems to be the same problem again. Your connection is busy with getting
data from TabellaIn, so ADO opens second connection for you, and then
the temp table is not there.

Two ways to address this:

o Use a client-side cursor. (Connection.CursorLocation = adUseClient)
o Explicitly use two connection, ond for data in and one for
data out.

(Actually I am not entirely sure that using a client-side cursor is
enough. But it's a good thing anyway.)

And of course, if all you do is copy data, it is much more effective
to do it down in SQL Server and not get the data forth and back over
the network.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.