SQL Server 2005: Import / Export reset identity keys… no workaround… 2005 sucks period, the end
There is now a solution to this problem although it’s not the greatest. The solution was found in the Microsoft Forums from this post. Another blog (High Hopes) has details on what to do. I’m includes parts of the post here as a reference to everyone finding this page and also so I don’t lost this information if the blog goes down.
STEPS TO ADD DTS WIZARD WITH SQL SERVER MANAGEMNT STUDIO EXPRESS
1. Try “C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe”
If it worked, you already have the DTS Wizard. Start using it right away.
If it will not work, continue to step 2
2. Download the Microsoft SQL Server 2005 Express Edition Toolkit (223.9 MB) from
select all components to install
3. Run “C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe”
Thats it.. Now you can transfer data using the the great DTS wizard.
Follow the steps If you want to integrate it with the Sql server management studio express UI .
1. Open sql server management studio express.
2. Select Tools -> External Tools
3. Add a Title and Browse C:\Program Files\Microsoft SQL Server\90 \DTS\Binn\DTSWizard.exe for the Command field.
3. Click OK
I have personally tried the steps outlined above and they work like a champ. This issue is considered CLOSED(?)
I’m getting a lot of people saying that they are disappointed that this problem isn’t fixed. Although I feel for you all, it doesn’t do any good venting your frustrations here. You need to be telling this to your sales representatives for Microsoft or where ever you bought SQL2K5.
Another thing you can do (if you can do it and your infrastructure allows it) is to down grade to SQL2K and then demand a refund from Microsoft sighting that their product doesn’t live up to your expectation.
This is the only way that Microsoft will listen.
UPDATE: You gotta love Microsoft’s cop out answer for why this doesn’t work and for leaving this bug open. There is no word yet on whether this will ever be fixed.
UPDATE: Seems I’m not the only one who’s pissed off by this.
UPDATE: Digg this thing up!
UPDATE: Reddit me!
I have officially labeled SQL Server 2005 worthless beyond belief. I really have no idea why something as basic as the import / export wizard (IEW) inside 2005 is broken beyond belief. Let me explain.
I have a current database that is a 2000 database with a bunch of tables that use an identity field as the primary key. What I’m trying to do is export the data that is in those tables to the 2005 database using the IEW. I’ve must of done this a thousand times in my career and have never had a problem doing it before. Normally I just run the I generate the scripts first (see my other blog post about the workaround for this with 2005) and then after creating the database on the new server, I just export the data from the old to the new. Easy, quick, never fails.
Now with 2005 there is a BIG bug that when you go to export the data from the old database to the new one; all of your identity values are reset. To put into perspective, let’s say you have a table that has 20 rows and the identity values range from 1-30 because of deletions that have happened on the table. When you go to export this table to the new table in 2005, you identity values are now 1-20 corresponding to the number of rows that are in the tables.
As you can see this is a big problem because it completely corrupts your data and renders it useless.
I’ve tried some workarounds that people have suggested to no avail. I really can’t believe that a bug like this has gone unnoticed and still isn’t even addressed within SP2 for 2005. Seems that Microsoft really screwed the pooch on this one and it make 2005 a piece of shit. Futhermore there isn’t an offer on my hosting plan to downgrade SQL Server to 2000; so I’m screwed.
I have found though that this isn’t the case if you use GUID for your primary keys, which I do now but in this database I use identity columns since I didn’t know any better at the times.
If anyone out there knows a workaround or a patch for this please speak up. Also voice your concerns to Microsoft about this since this is a bug that should of never existed in the first place. Until this problem is fixed, I’m officially calling 2005 shit and I’m recommending to all my clients to not upgrade.