Microsoft SQL Server Migration Assistant for Access Fails to Migrate Data

I attempted to use SSMA to migrate a 2000 file format .mdb into SQL Server Express (and, as I've just purchased VS2005 Pro, into SQL Server Developer Edition).

The file is actually a backend, so nothing to migrate other than tables.

After several runs during which I received (and fixed up) some errors, the process runs smoothly with no errors or warnings, until it comes to the last step; migrating the data itself.

Data migration into 'parent' tables works fine. However, wherever I have data in a table with a foreign key relationship to any of the aforementioned 'parent' tables, it refuses to migrate it. The text of the error message will be very smiliar to the following (from the log):

[Datamigrator: Error] [464/7] [2007-09-23 14:18:54]: Exception: The INSERT statement conflicted with the FOREIGN KEY constraint "Branch$CompanyBranch". The conflict occurred in database "DPMTest", table "dbo.Company", column 'CompanyID'.

There is nothing wrong, so far as I can determine, with the relationships involved.

I can insert data into the tables using any of the following methods:

1. Directly in Access, in the source backend.

2. Using the original Access frontend application, attached to the source backend.

3. Using VB.Net forms I am developing, in the 'upsized' database.

4. Directly in the Management Studio, in the 'upsized' database.

None of these four methods complains in the least about the relationships which SSMA balks at.

I would be grateful for any ideas....thanks.

The error is self explanatory...

You are inserting companyID that are not there in the master table. Just run a select some thing like this

Select *from ThisTableName where CompanyID not In (select companyID from ParentComapnyTableName)

You will get few rows. this needs to be deleted otherwise you have to correct the data.

Madhu

|||

As I said, I can insert data via all other methods. I am not inserting CompanyID where it does not exist in the master table. There is only one CompanyID at this point, whose ID is '1'. If I add a Branch with CompanyID of '1' in the Access source database which, again, doesn't complain about the relationship - because there is no problem with the relationship, or the data I'm inserting - the Migration Assistant throws the abovementioned error.

I appreciate your response, but I feel you didn't comprehend my original post. Thanks, anyway.

|||I have "successfully" migrated MDB files only to find out that SQL and Visual Studio balks at them. Problems with constraints, views, and other stuff.