Search

Mappings settings not working in Export Data wizard by devl42

Closed
as External Help for as External

26
Sign in to vote
0
Sign in to vote
Sign in
 to vote
Type: Bug
ID: 135905
Opened: 6/15/2006 11:46:45 AM
Access Restriction: Public
1
Workaround(s)
20
User(s) can reproduce this bug
Option to "Delete rows from existing tables" always ignored. "Enable identity insert" also ignored in certain circumstances.
Details (expand)
Product Language
English
Version
SQL Server 2005 Service Pack 1 (X64)
Category
Management Tools
Operating System
Windows Server 2003
Operating System Language
US English
Steps to Reproduce
1) Connect to 2 remote database servers (in my case, both are Sql 2000)
2) Run Export Data wizard via the Tasks drop down when right-clicking one of the databases.
3) specify source and destination credentials
4) select all tables (22 in my case), most of them have Identity columns
5) "Optimize for many tables" gets selected automatically. Leave that.
6) highlight all tables, and click Edit Mappings (for the group settings options). Select "Delete rows in existing destination tables" and "Enabled identity insert".
7) Hit OK, Next, then Finish
Actual Results
Rows in existing destination tables are NOT deleted.
New rows are inserted after existing ones. Identity values are all changed to following sequential numbers.
Expected Results
Desination rows should have been deleted.
Newly inserted rows should have same Identity values as source tables.

Retried without the "Optimize for Identity tables", first bug still exists, rows in destination tables were not deleted. But but insert of new rows failed due to PK conflict when inserting Identity values.

So it looks like when Optimize is checked, it suppresses the behavior or the Identity Insert.

Looks like the bug fixed in SP1 relating to this has mutated and needs to be refixed and expanded to include the row deletes.
Platform
 
File Attachments
0 attachments
Sign in to post a comment.
Posted by da9l on 7/4/2006 at 8:31 AM
Enable identity insert is ignored when "optimize for multiple tables" is enabled. Unfortunately that option ensures that the import operation observes referential integrity between foreign key connected tables according to this blog: http://blogs.msdn.com/chrissk/archive/2006/06/24/645968.aspx.

Very, very, annoying.
Posted by Microsoft on 8/28/2006 at 12:06 PM
Unfortunately this problem is due to an issue in SMO which should hopefully be resolved in a later release. As I am sure you are aware, "Optimize for Many Tables" feature imports tables one-at-a-time in a Foreach Loop within the SSIS package. If you need to import tables and Keep Identity, then turn off "Optimize for Many Tables". This will import as many tables at a there are SSIS threads. To limited server overhead, limited the number of SSIS threads in the package properties.
Posted by Nick Lucas on 5/8/2007 at 3:11 AM
I worked until 10 o'clock at night and then from 5am the next morning because of this problem. I was at my wit's end. What a terrible bug to leave in the system.
Posted by fcsobel on 11/18/2007 at 7:16 PM
After spending a day fighting with this I learned about SqlBulkCopy from a post on David Hayden’s blog and created this quick app to get around the problem.

It can copy data and keep identity and nulls intact. Used with the generate script wizard you can make a complete database copy as long as you have sql access.

It’s like having the SQL 2000 wizard back:)

You can get it here:

http://projects.c3o.com/files/3/plugins/entry11.aspx
Posted by fcsobel on 11/24/2007 at 5:48 PM
Made some needed enhancements to SimpleSQLCopy. Changed checkbox list to DataGrid to display errors. Changed call to asynchronous for better feedback. Added user settings to remember source and destination.

You can get it here:

http://projects.c3o.com/files/3/plugins/entry11.aspx
Posted by DBartholomew on 2/12/2009 at 4:03 PM
I too have invested significant time into discovering this problem. Please Microsoft, this one will affect a lot of folks. Should I have purchased SQL 2008 instead? I expected 2005 would be more stable and reliable... :-(
Posted by Jollygreenguy on 12/1/2009 at 9:01 AM
We, too, are having problems with "Delete rows from existing table" as well as the export wizard "skipping" records that should be exported. I am not using the “optimize for multiple tables” as I have had problems with that in the past. If I recall, the problem with using this option is that my identity fields would not be recreated in the proper order or there would be an error. I have since not used that option and can not recall the error/problem.

After reading the post from Microsoft dated on 8/28/2006 this confirms the problem I had with the identity value not retaining correctly.

We have three very large databases: A production DB, and End of Month Database which is simply a copy of the previous month’s production database used for reporting and other purposes, and a staging database.

All databases are SQL Server 2005 with latest service packs.

I am attempting to refresh the staging database with data from the End of Month Database using the Import/Export Wizard.

When I use this wizard I enable identity insert and chose to delete rows in the destination database/delete rows from existing tables. Many times, the Import/Export will NOT delete the existing rows but simply append them. At first I thought that I was doing something wrong such as forgetting to place a check in the option to delete rows so I then decided to do a screen shot of every action I did for every table. This was VERY time consuming but I wanted to be sure that I wasn't forgetting to click that checkbox.

I would use an additional tool called SQL Compare to compare the record count of all tables before and after using the SQL Server import/export wizard. I would compare it against the source and destination databases.

Some times would import fine but many would not and I would have to do it a few times. I would sometimes have to manually truncate the destination table and run it again but the counts would be off in some cases. I would try one table at a time or many tables at a time.

I even ran the SQL Import/Export Wizard from different computers and had the same problem.

I normally don't have the need to transfer this much data but the last time I had this problem was the last time I had to do this which was earlier this year. I remember reading somewhere that sometimes the temp files would fill up and then start to randomly remove records. I do not know how true this is and I can't recall where I found it.

My only work around that I have is to limit the number of tables that I am going to import/export at a time and repeat several times until all the records are finally in the destination table. I have a few tables where I simply can't get all the data inserted and is off by 1 to 7 records no matter what I do. So this is really not even a work around.

To me, it seems there is a big problem with the SQL Server Import/Export Wizard and I am going to try the one that was suggested from here: http://projects.c3o.com/files/3/plugins/entry11.aspx

Any advice from Microsoft or anyone else would be greatly appreciated!
John
© 2010 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement