Search
Closed
as External Help for as External

25
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)
19
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 Unregistered User 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... :-(
© 2009 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement