Correct access to TempDB?

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?zMatteo (origma@.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, esquel@.sommarskog.se

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

Copy Wizard failed?

I tried to use Copy Database wizard but it keeps returning this error:
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:Unspecified error
Step Error code: 80004005
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100
How do I resolve this?
Both the source database and destination database have valid
authentications.It's likely related to permissions. For permissions
requirements, service account requirements, refer to the
following article:
INF: Understanding and Troubleshooting the Copy Database
Wizard in SQL Server 2000
http://support.microsoft.com/?id=274463
-Sue
On Wed, 3 Dec 2003 18:21:41 -0500, "Ed"
<eddiemarino@.hotmail.com> wrote:
>I tried to use Copy Database wizard but it keeps returning this error:
>Step Error Source: Microsoft Data Transformation Services (DTS) Package
>Step Error Description:Unspecified error
>Step Error code: 80004005
>Step Error Help File:sqldts80.hlp
>Step Error Help Context ID:1100
>
>How do I resolve this?
>Both the source database and destination database have valid
>authentications.
>|||Thanks, Sue.
Could a database be copied without using this wizard?
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:ii01tvo98f6qoe1rspo94u3i2lrm3mssne@.4ax.com...
> It's likely related to permissions. For permissions
> requirements, service account requirements, refer to the
> following article:
> INF: Understanding and Troubleshooting the Copy Database
> Wizard in SQL Server 2000
> http://support.microsoft.com/?id=274463
> -Sue
> On Wed, 3 Dec 2003 18:21:41 -0500, "Ed"
> <eddiemarino@.hotmail.com> wrote:
> >I tried to use Copy Database wizard but it keeps returning this error:
> >
> >Step Error Source: Microsoft Data Transformation Services (DTS) Package
> >Step Error Description:Unspecified error
> >
> >Step Error code: 80004005
> >Step Error Help File:sqldts80.hlp
> >Step Error Help Context ID:1100
> >
> >
> >How do I resolve this?
> >
> >Both the source database and destination database have valid
> >authentications.
> >
>|||Hi
It is not clear why you want to use this but maybe the way around it is to
use a different method to copy the database:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b224071
John
"Ed" <eddiemarino@.hotmail.com> wrote in message
news:%23weAzjnuDHA.1060@.TK2MSFTNGP12.phx.gbl...
> I tried to use Copy Database wizard but it keeps returning this error:
> Step Error Source: Microsoft Data Transformation Services (DTS) Package
> Step Error Description:Unspecified error
> Step Error code: 80004005
> Step Error Help File:sqldts80.hlp
> Step Error Help Context ID:1100
>
> How do I resolve this?
> Both the source database and destination database have valid
> authentications.
>|||Yes...you can copy a backup file to the destination server
and restore the backup. You can use sp_detach_db and
sp_attach_db.
-Sue
On Fri, 5 Dec 2003 11:00:32 -0500, "Ed"
<eddiemarino@.hotmail.com> wrote:
>Thanks, Sue.
>Could a database be copied without using this wizard?
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:ii01tvo98f6qoe1rspo94u3i2lrm3mssne@.4ax.com...
>> It's likely related to permissions. For permissions
>> requirements, service account requirements, refer to the
>> following article:
>> INF: Understanding and Troubleshooting the Copy Database
>> Wizard in SQL Server 2000
>> http://support.microsoft.com/?id=274463
>> -Sue
>> On Wed, 3 Dec 2003 18:21:41 -0500, "Ed"
>> <eddiemarino@.hotmail.com> wrote:
>> >I tried to use Copy Database wizard but it keeps returning this error:
>> >
>> >Step Error Source: Microsoft Data Transformation Services (DTS) Package
>> >Step Error Description:Unspecified error
>> >
>> >Step Error code: 80004005
>> >Step Error Help File:sqldts80.hlp
>> >Step Error Help Context ID:1100
>> >
>> >
>> >How do I resolve this?
>> >
>> >Both the source database and destination database have valid
>> >authentications.
>> >
>