Re: Import Data succeeds but doesn't copy data?
- From: "geek-y-guy" <noone@xxxxxxxxxxx>
- Date: Mon, 28 Aug 2006 12:35:48 -0400
<egg-on-face>
I went back to the import data script and looked at it carefully, and I
realized that the tables were being copied, but under a different schema, so
I ended up with a duplicate set of tables that DID have the data in them :(
</egg-on-face>
My question is, is there any way to alter the schema identity of a table?
IOW: the application is looking for a table with a schema of "dbo" like:
[mydatabase].[dbo].[mytable]
but when I'm copying the database, the tables are being created as:
[mydatabase].[myschema].[mytable]
is there any way to "map" those tables with [myschema] to [dbo]? If I run
the app's script to create the tables, it creates them under [dbo], and if I
try to copy directly to those tables, all SSMS will let me do is create new
tables under schema [myschema]...help?
--
"Arnie Rowland" <arnie@xxxxxxxx> wrote in message
news:OGg7Y0WyGHA.4392@xxxxxxxxxxxxxxxxxxxxxxx
Look closely at the messages...
Copy rows from [myDataBase].[myDataBase].[carthead] to
[myDataBase].[myDataBase].[carthead]
Looks like you are copying from a database [MyDatabase] to the SAME
[MyDatabase]
I still suspect that you are using the same source and destination
servers.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"geek-y-guy" <noone@xxxxxxxxxxx> wrote in message
news:%2383%23QrWyGHA.3552@xxxxxxxxxxxxxxxxxxxxxxx
If a CONSTRAINT was involved with preventing the data from beingThat's what I assumed...
imported, there would be error messages, and you would not receive a
"copied xxx rows" message.
I'm wondering about the location of the resulting table. Are you sure
that you did NOT use the same server for both the source and
destination?
That would be a likely mistake for me to make :o
... but in this case I'm sure that isn't what's happening...I'm copying
from the remote server and targeting the dB on the local server (I've
tried it numerous times). In each case, I'm logging in to the remote
server with a SQL Login/pwd and to the local server with a trusted
connection. I'm selected "delete rows in target table" and "enable
identity insert". Here's my transaction:
//--begin transaction
Click Finish to perform the following actions:
Copy rows from [myDataBase].[myDataBase].[carthead] to
[myDataBase].[myDataBase].[carthead]
Any existing rows in the target table will be deleted.
Copy rows from [myDataBase].[myDataBase].[cartrows] to
[myDataBase].[myDataBase].[cartrows]
Any existing rows in the target table will be deleted.
Copy rows from [myDataBase].[myDataBase].[CartRowsOptions] to
[myDataBase].[myDataBase].[CartRowsOptions]
Any existing rows in the target table will be deleted.
Copy rows from [myDataBase].[myDataBase].[Categories] to
[myDataBase].[myDataBase].[Categories]
Any existing rows in the target table will be deleted.
Copy rows from [myDataBase].[myDataBase].[Categories_Products] to
[myDataBase].[myDataBase].[Categories_Products]
Any existing rows in the target table will be deleted.
The package will not be saved.
The package will be run immediately.
The execution was successful
//--end transaction
And here's the report upon completion:
//--begin report
- Create a TransferProvider. (Success)
- Create a temporary file for destructive SQL. (Success)
- Create a temporary file for constructive SQL. (Success)
- Create a temporary XML file for table metadata. (Success)
- Configure the TransferProvider with files and Source/Destination
connection info. (Success)
- Execute the transfer with the TransferProvider. (Success)
- Execute the destructive SQL. (drop tables) (Success)
- Execute the constructive SQL. (create tables) (Success)
- Create a temporary table transfer package for [myDataBase].[carthead]
(Success)
- Execute transfer package for [myDataBase].[carthead] (Success)
- Copying to [myDataBase].[carthead] (Success)
* 45 rows transferred
- Create a temporary table transfer package for [myDataBase].[cartrows]
(Success)
- Execute transfer package for [myDataBase].[cartrows] (Success)
- Copying to [myDataBase].[cartrows] (Success)
* 65 rows transferred
- Create a temporary table transfer package for
[myDataBase].[CartRowsOptions] (Success)
- Execute transfer package for [myDataBase].[CartRowsOptions]
(Success)
- Copying to [myDataBase].[CartRowsOptions] (Success)
* 0 rows transferred
- Create a temporary table transfer package for [myDataBase].[Categories]
(Success)
- Execute transfer package for [myDataBase].[Categories] (Success)
- Copying to [myDataBase].[Categories] (Success)
* 3 rows transferred
- Create a temporary table transfer package for
[myDataBase].[Categories_Products] (Success)
- Execute transfer package for [myDataBase].[Categories_Products]
(Success)
- Copying to [myDataBase].[Categories_Products] (Success)
* 13 rows transferred
//--end report
So, it looks like everything executes...but when I open any table in the
dB, the data is not there!
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"geek-y-guy" <noone@xxxxxxxxxxx> wrote in message
news:OSWC9ovxGHA.4232@xxxxxxxxxxxxxxxxxxxxxxx
I have a dB on a remote SQL2000sp4/win2k server I'm trying to import
into SQL2005x64 (sp1) on Server2003x64. I have a schema that I didn't
write that created the database on both servers.
During config on the import wizard I select to delete existing data and
enable identity insert, and when I run the copy all the steps complete,
including "copied xxx rows", and there are no errors in the report.
But when I check the table, there is no data in it! Would a constraint
on the table prevent the data from actually being inserted, even though
it reports the rows were copied successfully?
.
- Follow-Ups:
- Re: Import Data succeeds but doesn't copy data?
- From: geek-y-guy
- Re: Import Data succeeds but doesn't copy data?
- References:
- Import Data succeeds but doesn't copy data?
- From: geek-y-guy
- Re: Import Data succeeds but doesn't copy data?
- From: Arnie Rowland
- Re: Import Data succeeds but doesn't copy data?
- From: geek-y-guy
- Re: Import Data succeeds but doesn't copy data?
- From: Arnie Rowland
- Import Data succeeds but doesn't copy data?
- Prev by Date: Re: ANSI Standards
- Next by Date: Re: Import Data succeeds but doesn't copy data?
- Previous by thread: Re: Import Data succeeds but doesn't copy data?
- Next by thread: Re: Import Data succeeds but doesn't copy data?
- Index(es):
Relevant Pages
|
