August 8, 2007 at 11:53 am
Hello all,
Novice DBA here (actually not DBA, more of a systems guy). Anyway, I created a test box with Windows 2003 R2 SP2 with SQL 2005 installed. Using the SQL SSIS Export wizard, I connected the old server with a 2000 database with my new clean server having 2005. The export/import went fine....no errors. However, I have noticed that I no longer have Primary Keys in the newly converted database. The compatibility level for the database is 90, so that seems fine. Additionally, I am unable to INSERT or ADD any webpage driven data into the new database.
This is a test environment...thankfully, but I am still puzzled why I lost the primary keys. I cannot seem to find any good references that talk about this specific problem. One of the error messages returned on my webpage is 80040e2f.
Any assistance and guidance would be greatly appreciated.
Kavic
August 9, 2007 at 12:04 am
Kavic -
The defaults for the export wizard (and a transfer objects SSIS task) do not include primary keys, indexes, triggers, etc. You need to be sure to specify that primary keys, etc. are included in your settings.
Joe
August 9, 2007 at 5:53 am
And how is this done? This is a setting within the wizard? Will the SSIS wizard allow me to do the same thing over again (with the settings you spoke of) on top of the newly migrated database, or do I need to start from scratch?
Here is one of the errors I am receiving:
Microsoft OLE DB Provider for ODBC Drivers error '80040e2f'
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column 'VID', table 'Visit.dbo.testvisits'; column does not allow nulls. INSERT fails.
August 9, 2007 at 7:43 am
Rather than using the import / export wizard to perform your upgrade - have you considered backing up the db and restoring it to your test environment. SQL Server will automatically perform an upgrade of the db - you can then change the compatability level to 90, update statistics and test from there. This will ensure you don't loose any objects and provides a much simpler upgrade path. Your other option would be to script out all of the objects from the source db so you create just the schema in your 2005 Db then use export wizard or just build a SSIS package to transfer the data from source to target.
August 9, 2007 at 12:49 pm
Would you recommend then to delete the existing databases on my new test 2005 server, then restore them again from the production 2000 SQL server? Or would I need to uninstall 2005 altogether, then reinstall and restore? Thanks
August 9, 2007 at 11:30 pm
Delete the databases on your SQL 2005 instance, backup from the 2000 server and then restore on the 2005 instance and all will be good - watch out for file paths along the way. You do not need to uninstall your SQL 2005 instance.
Joe
August 10, 2007 at 8:58 am
Also, you might want to visit the other forum posts discussing the upgrade to SS2k5 from either SS2k or SS7
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply