Rip's Domain

SQL Server 2005: Import / Export reset identity keys… no workaround… 2005 sucks period, the end

Posted in SQL, TechSupport by rip747 on October 1, 2007

BEGIN SOLUTION

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

http://go.microsoft.com/fwlink/?LinkId=65111
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(?)

END SOLUTION

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.

38 Responses

Subscribe to comments with RSS.

  1. Bob said, on October 2, 2007 at 6:37 am

    Why don’t you generate the script for DB from 2k and execute it in 2005. I think this work for structure but data cannot be moved.

  2. rip747 said, on October 2, 2007 at 8:31 am

    @Bob,

    Yes, I can do that. Really the point of this post is to point out the failure of Microsoft to fix bugs in a very useful part of SQL Server. There is no reason why something like this shouldn’t have been addressed ages ago or even why the product was shipped with this not working.

    Last night I even found out a way around the problem I’m having by just doing a standard backup and restore. This work around however, still doesn’t help the fact that the import / export wizard is broken and hasn’t been fixed. I mean for God’s sake, 2005 is on SP2 already!

  3. Dan Wilson said, on October 2, 2007 at 12:05 pm

    I have found that making sure “Optimize for many tables” remains unchecked is the solution.

    Of course you have to click on each table, wait for the screen to come up, then select Enable Identity Insert.

    The secret to not getting upset about bugs like this is remembering the many other ‘Sub-Optimal’ applications put out by Microsoft Corp.

  4. rip747 said, on October 2, 2007 at 1:14 pm

    @dan

    actually that isn’t solution or a workaround as per the link in the post:

    http://blogs.msdn.com/chrissk/archive/2006/06/24/645968.aspx

    This is an ongoing issue that Microsoft will not address, which is what is ticking me and many others off.

  5. Cozmo said, on October 2, 2007 at 2:54 pm

    I ran into this on the developer.com newsletter today. I don’t know if this will be of any help (Just skimmed it) but it might. Who knows with computers. http://www.developer.com/net/asp/article.php/3702826

  6. Dana K said, on October 10, 2007 at 3:46 pm

    We ended up doing this a couple ways to see which worked better for us etc.

    The first we ran a full backup and then restore to a fresh 2k5 database without issues. It was a bit time consuming, but in the end the identities and data was on point.

    The second we simply took the db offline in 2k0 and then copied the mdb and ldb over to the 2k5 machine. We then attached it, and put it over into 2k5 compatibility mode. It ran a couple tools on itself and sent it on its way. I wasn’t a big fan of this one as I’m sure it will lead to ‘weird’ things down the road, but I dunno. It seemed to work ok.

    The final way was to use replication. We had setup a remote replication between the two boxes and had the 2k0 machine be the witness/publisher etc. We ended up having to put a call into MS , but in the end the data replicated fine over to the new 2k5 db’s without problem.

  7. Fred said, on November 18, 2007 at 10:47 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

  8. Fred said, on November 24, 2007 at 9:45 pm

    Made some much needed enhancmenets to the Simple SQL Bulk copy app. 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

  9. Laurie Brown said, on January 7, 2008 at 9:18 am

    At last someone telling it how it is! This bug has caused me so many problems. It is a major bug and renders SSIS almost useless.

  10. Suresh said, on January 12, 2008 at 5:07 pm

    Fred , Thank you so much, your tool saved me a big time. Shame on MS for not responding this issue in a right manner for long time

  11. Mark W. Breneman said, on January 23, 2008 at 9:40 am

    Thanks guys, you confirmed what I had experienced. I just assumed that I was missing something and did not have time to dig deeper into SQL2k5 land to figure how to do import / exports correctly. Though I had already spend hours and hours trying to get the import / export to work. This issue caused us to lose lots of development time/$. SQL2k5 came with a new VPS that we migrated to. I can say with out reservation, moving to SQL2k5 was the worse IT choice we every made. I have yet to see any major advantage or new feature that we used in our web development over the last year.

  12. rip747 said, on January 23, 2008 at 10:31 am

    @Mark,

    You might want to contact your VPS company and demand that they switch you to a VPS with SQL2K installed on it. Tell them the reason and have them complain to Microsoft about the problem.

  13. Peter Bates said, on January 30, 2008 at 1:11 am

    As above, I encountered this while trying to merge two databases. “Am i insane”? I thought. Surely it can’t be a bug?

    WTF are they thinking leaving stuff like this unfixed?

  14. rip747 said, on January 30, 2008 at 10:07 am

    @Peter,

    I feel for you my brother… I feel for you.

  15. dany said, on February 4, 2008 at 3:35 am

    I read the blowing informative thread. can anyone let me know why am i not able to change the identity column to yes. when i import it. its tiring.

    thanks
    dan

  16. CPP said, on February 14, 2008 at 11:50 am

    Hey Fred,

    I have tried to download and run from the site but unsuccessfully. Really need your tool to work around SQL 2005, can you repost it on the web. That would be very appreciated.

    CPP

  17. Graeme Black said, on February 20, 2008 at 6:12 pm

    Overall the 2005 Import / Export wizard is a MAJOR step backwards.

    Field types and length no longer default to the correct values when importing or exporting, forcing you to redefine each and every field. Exporting varchar field to Access will default to LongText. This results in all the field in your access database being set to Memo.

  18. rip747 said, on February 20, 2008 at 8:02 pm

    Hey everyone, don’t complain here, complain to Microsoft. They need to be told that 2005 is a piece of shit.

  19. Alaric Schenck said, on March 19, 2008 at 2:23 pm

    If you wish to preserve identity column values when importing data to SQL Server 2005 then try the following:

    When you use the SQL 2005 Import and Export Wizard and you reach the screen titled “Select Source Tables and Views” click the button at the bottom labeled “Edit Mappings”. Check the checkbox labeled “Enable Identity Insert”. This seems to work for me.

  20. KTF said, on March 19, 2008 at 5:00 pm

    Aleric,

    Edit mappings>Enable Identity Insert worked for me as well.

  21. Roger said, on April 4, 2008 at 12:37 pm

    I’m using a SQL 2005 database now and importing data using the wizard from one db to another and need the PKs (Identity columns) to be the same in both. I hope the Enable Identity Insert option will work. I found that you don’t have to uncheck optimize for many tables. You can highlight all your tables and edit settings for all of them at once (including Enable Identity Insert). If this works, then it seems that all the complaints above about a bug in SQL 2005 are bogus??? I hope this works, if not I hope the tool posted above works and is available. I’ll report my results.

    Roger

  22. KO said, on April 29, 2008 at 6:06 pm

    I was experiencing a similar problem – the identity not inserting, the delete existing records not working. I edited the mappings and removed the optimize check but I still seemed to have the problem. I was not able to use the BulkCopy because I’m trying to import to a hosted website. My solution was to keep the import to no more than 20 tables at a time. It seems to automatically do the optimize if you have more than 20 tables – even if you removed the checkbox. You get a visual clue to this when you look at the details on the final screen of the wizard – it gives you each table listed separately and how many rows it inserted. If you aren’t getting this then I think it is still optimizing the import and you’ll probably get errors/bad data.

  23. David Levin said, on July 9, 2008 at 12:47 am

    KO, thanks for the tips but unfortunately if your db has table constraints the import/export will need to be run in a single transaction. As far as I know you can’t run the import/export in a single transaction unless you optimize for many tables.

  24. Web Designer Kent said, on July 21, 2008 at 7:13 pm

    Ive been working on CSS based sites for sometime but still get the odd error in firefox ! wish they just played ball the same when designing, would make life a whole lot easier for everyone involved… but then that would be to simple and obvious…lol

  25. Valerie said, on August 4, 2008 at 12:14 pm

    Thanks for the ideas and leads! It worked for me to choose “Transform…”, click on the “Edit SQL…” button and then manually type “identity” after the data type. I was moving from 2000 to 2005.

  26. Trevor said, on September 4, 2008 at 9:21 am

    Thanks for that amazing tool! I had to do the import from a remote server that I couldn’t get backups from, and didn’t want to go through the ‘click every damn enable identity insert’ process that they designed when they obviously weren’t thinking clearly or at all

  27. John said, on September 11, 2008 at 11:21 pm

    wow,
    thanks for all the comments guys.
    this totally saved my ass!
    woooohooooo!

    i had go to transform, select the enable identity box and then edit the sql and manually type “IDENTITY(1,1)” after the datatype specification, which in my case was “int”.

    Thanks a million!

  28. sam said, on September 26, 2008 at 12:21 pm

    What happened to this bug on connect.microsoft??? No links or reference number exist!

  29. dorson said, on October 3, 2008 at 5:16 am

    I have created tables in sql 2005 but now i am comfortable working in 2000.can anyboby tell me how to import the tables in 2000 from 2005.

  30. MickeyDIng said, on October 8, 2008 at 11:57 pm

    SQL2005 import has a gotcha if you pay for your bandwidth by the megabyte. Make sure you run the import from SQL management studio (SQL-MS) running on the machine where the destination database resides. I have just had the misfortune of trying to run an import from a database on a remote server to another database on the same server using SQL-MS on my own desktop machine. After 6 hours of chugging away and only getting 1/2 way through the 6 gig transfer I canned the import. – trashed the tables – rebuilt and re-imported by remoting into the remote server and re-ran the import using SQL-MS on the remote machine in ten minutes flat.
    We are expecting a hefty bill for the data that seems to have travelled from the remotemachine to my destop and back to the remotemachine. The old SQL2000 enterprisemanager used to do it without routing the data through the client application. (packet sniffer tells all) This is called progress ?

  31. Intrepreneurs said, on November 20, 2008 at 12:27 am

    I figured out how to work around this in C#. For those of you who have access to VS 2003 or VS 2005, this will do the trick. It assumes a few things:

    1) You first copy the source database tables and data using SQL Import to a blank database. You will call this the LOCAL SOURCE database. You dont care about relationships, just the data and let the import wizard create the tables.

    2) Your target database is local also. The username for this script must have read access to the LOCAL SOURCE and be the owner of the LOCAL TARGET database.

    I made my own SQL Provider from the standard SQLClient, so you can substitute that. Since this enumerates sysobjects and syscolumns, you only have to supply the connection string and source and target db names. You can modify this to your hearts content.

    Its not very clean, but it was late and I was a very angry camper when I found out about this bug in SQL hosing my database keys. Grrrrr!

    Enjoy:

    // Used for creating SQL Queries
    const string SourceDbName = “SourceDbName”;
    const string TargetDbName = “TargetDbName”;
    int exec = 0;
    SqlProvider dbProvider = new SqlProvider();
    dbProvider.ConnectionString = “server=YOURSERVERNAME;uid=USERID;pwd=PASSWORD;Trusted_Connection=no;database=SOURCEDBNAME”;
    //
    // Get all user tables.
    string tableSql = “select sysobjects.name as TableName from sysobjects where sysobjects.xtype = ‘U’ order by sysobjects.name”;
    //
    // Turn off constraints and triggers.
    exec = dbProvider.Execute(“exec sp_MSforeachtable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL'”);
    exec = dbProvider.Execute(“exec sp_MSforeachtable ‘ALTER TABLE ? DISABLE TRIGGER ALL'”);

    DataSet tableData = dbProvider.GetData(tableSql);
    foreach (DataRow tableRow in tableData.Tables[0].Rows)
    {
    //Wipe Out Table Data;
    string tableName = tableRow[“TableName”].ToString();
    string sqlTableName = tableName;
    //Some tables have dots (.) in them, messes up query, so we escape it.
    if (tableName.Contains(“.”))
    {
    sqlTableName = “[” + tableName + “]”;
    }
    string tableDeleteSql = “DELETE FROM ” + sqlTableName;
    int rows = dbProvider.Execute(tableDeleteSql);
    //
    // Create Cross-Database insert queries
    string identitySql = “SELECT IDENT_CURRENT(‘” + sqlTableName + “‘)”;
    object identity = dbProvider.GetScalar(identitySql);

    int identityValue = 0;
    if (identity != System.DBNull.Value)
    {
    identityValue = Convert.ToInt32(identity);
    }
    //
    //
    string insertSql = “INSERT INTO ” + TargetDbName + “.dbo.” + sqlTableName + ” ( “;
    string selectSql = “SELECT “;
    string fieldSql = “select syscolumns.name as ColumnName, syscolumns.iscomputed as IsComputed from syscolumns, sysobjects where sysobjects.id = syscolumns.id AND sysobjects.name = ‘” + tableName + “‘ order by ColumnName”;
    DataSet sourceFields = dbProvider.GetData(fieldSql);
    foreach (DataRow fieldRow in sourceFields.Tables[0].Rows)
    {
    //Ignore computed columns
    string columnName = fieldRow[“ColumnName”].ToString();
    int isComputed = (int)fieldRow[“IsComputed”];
    if (isComputed == 0)
    {
    insertSql += columnName + “,”;
    selectSql += columnName + “,”;
    }
    }
    // Clean up the sql
    insertSql = insertSql.TrimEnd(‘,’);
    selectSql = selectSql.TrimEnd(‘,’);
    string bulkSql = insertSql + “) ” + selectSql + ” FROM ” + SourceDbName + “.dbo.” + sqlTableName;
    //
    //
    if (identityValue > 0)
    {
    bulkSql = “SET IDENTITY_INSERT ” + sqlTableName + ” ON;”
    + bulkSql + “;”
    + “SET IDENTITY_INSERT ” + sqlTableName + ” OFF;”;
    }

    rows = dbProvider.Execute(bulkSql);
    bool stop = true;
    }
    //
    exec = dbProvider.Execute(“exec sp_MSforeachtable ‘ALTER TABLE ? CHECK CONSTRAINT ALL'”);
    exec = dbProvider.Execute(“exec sp_MSforeachtable ‘ALTER TABLE ? ENABLE TRIGGER ALL'”);

  32. Kelly said, on December 20, 2008 at 5:44 pm

    Does this work in Binary? I get the error “Invalid Object name information.schema.tables” when I try to connect to a Binary database.

  33. DMz said, on April 5, 2009 at 11:59 pm

    Yes I totally agree I really REALLY wish that 3rd option in dts 2k was present in 2k5, “copy objects and data”. Seems rediculous to remove this feature that 2k users have been enjoying for many years. Ive tried many methods suggested here and nothing seems to work for me except importing via 2k5 and manually resetting keys…. Doing the primary keys isnt that bad I suppose but the biggest hassle for me is the database defaults I have to go through many columns in many tables resetting column defaults😦😦😦

  34. Patrick said, on April 29, 2009 at 3:57 am

    Work-around for identity inserts
    – disable identity seed.
    – import (identity insert on)
    – enable identity seed again.

  35. Biko said, on July 9, 2009 at 9:29 am

    I find it is easier to backup the database to a file then restore that file to the new location. that’s if you’re doing the entire database and making no modifications.

  36. Kirby L. Wallace said, on September 23, 2009 at 10:31 am

    “Why don’t you generate the script for DB from 2k and execute it in 2005. I think this work for structure but data cannot be moved.”

    Yeah… that sounds like a good solution. Besides, you don’t need that data stuff anyway. It’s the table structure that counts. As long as you get a good table struct, who cares if the data is missing… 😛

  37. Channelwhitsunday.com said, on May 27, 2010 at 7:53 am

    Thanks for the tip off about IDENTITY(1,1). Its appears to be working for me. I host at webcentral in Australia and have upgraded by being lured to cheaper db hosting plans on 2005 db servers…. or was it 2008. Anyway, using manager express, I went through each of the tables I am moving for the upgrade, check the box for Enable identity insert, and drop destination table. I then used “edit mappings” to change the sql for the table creation to include the IDENTITY(1,1) on each identity field in each table. Long job, but looks ok so far.🙂


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: