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

Copying structure of table form one database to another

I am wondering if there is a simple way, like a stored procudure or
using some type of table object, to copy a table form one database to
another. Just the structure , index, triggers, I don't want the data.kevin.jonas@.gmail.com wrote:
> I am wondering if there is a simple way, like a stored procudure or
> using some type of table object, to copy a table form one database to
> another. Just the structure , index, triggers, I don't want the data.
You can generate a script for the whole schema with EM and execute it on
the target db. Maybe there's also a solution involving DTS - I'm not sure.
robert|||Using DMO library also is possible and using DTS
--
Current location: Alicante (ES)
"Robert Klemme" wrote:

> kevin.jonas@.gmail.com wrote:
> You can generate a script for the whole schema with EM and execute it on
> the target db. Maybe there's also a solution involving DTS - I'm not sure
.
> robert
>

Copying DB in SQL 2005

I used the Copy Database Wizard in SQL 2005 and I selected SQL Management
Object method to copy one DB onto a new one on the same server, it looks like
it worked but the Identity Seed and Identity increment properties are missing
on some of the columns on the new DB. Has anyone else experienced this
behavior?Same here... It also occurs with the Transfer Object task in SSIS, the
Identity attribute is dropped from tables. Currently I am using Dump/Restore
to copy DBs. My preference is to use Transfer Object to transfer only tables
between DBs, but the droppped Identity prevents that solution.
"Garios" wrote:
> I used the Copy Database Wizard in SQL 2005 and I selected SQL Management
> Object method to copy one DB onto a new one on the same server, it looks like
> it worked but the Identity Seed and Identity increment properties are missing
> on some of the columns on the new DB. Has anyone else experienced this
> behavior?

Copy table column names from SSMS Object Browser to use in a query

I thought I saw this done once before. So today I hunted around in
Books OnLine and did a Google search. So far I have found nothing
close. So if you know how to do it, please tell me or if cannot be
done, I'd appreciate know that too.

Thanks in advance,
IanOOn Tue, 06 Nov 2007 18:17:05 -0000, iano wrote:

Quote:

Originally Posted by

>I thought I saw this done once before. So today I hunted around in
>Books OnLine and did a Google search. So far I have found nothing
>close. So if you know how to do it, please tell me or if cannot be
>done, I'd appreciate know that too.

Hi IanO,

Open Object Explorer with F8 (of you don't have it open yet), expand
"Databases", then the name of your DB, then "Tables", then the name of
your table. Now, drag the "Columns" map to the editor window and a list
of all columns will automagically appear.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||iano (IanONet@.gmail.com) writes:

Quote:

Originally Posted by

I thought I saw this done once before. So today I hunted around in
Books OnLine and did a Google search. So far I have found nothing
close. So if you know how to do it, please tell me or if cannot be
done, I'd appreciate know that too.

Find the table in Object Explorer and expand the note. Select Columns
and drag it to the place where you want it.

My own take on this is that I simply run a SELECT * with TOP 1 or
WHERE 1 = 0 in text mode. I copy and paste into window in Textpad
where I have a macro that replaces the multiple spaces with comma +
space. Sounds kludgy? Well, navigating in an explorer tree takes time too.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx