sabrina_spillane
asked on
Issue with using SSMA Tool to Migrate a table from Access to SQL Sever
Hi All,
I am using the SSMA - SQL Sever Migration Assistanct for Access to migrate my Access 2003 database to SQL Sever. Everything is working perfectly and so far have no problems and I am migrating multiple database. However I noticed that one table called tblPersonnel from one of the access database converted its schema to SQL, loaded to the target database but will not migrate its data. In the log report that appears after i go to migrate the table shows no errors only that the row count completed is 0 instead of the correct row count. Normally there is an error and what it causing the problem in the log file but nothing.
This is the only issue i am getting. All the other tables migrated perfectly. Has anyone got any ideas as to why this might be happening?
Thanks in advance for all the help.
I am using the SSMA - SQL Sever Migration Assistanct for Access to migrate my Access 2003 database to SQL Sever. Everything is working perfectly and so far have no problems and I am migrating multiple database. However I noticed that one table called tblPersonnel from one of the access database converted its schema to SQL, loaded to the target database but will not migrate its data. In the log report that appears after i go to migrate the table shows no errors only that the row count completed is 0 instead of the correct row count. Normally there is an error and what it causing the problem in the log file but nothing.
This is the only issue i am getting. All the other tables migrated perfectly. Has anyone got any ideas as to why this might be happening?
Thanks in advance for all the help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I just found another table that didn't migrate the data over either but this time to log specified some type of error:
"Cannot insert duplicate key row in object 'dbo.AA210AirPrefTests' with unique index 'AA210AirPrefTests$MainKey
Looking at the data in this table there is no null values either in the first record. What do you think might be the problem with this one. At least it gave more of a details error.
For the tblPersoonel the detailed error : "Error occured during data migration" and that was it.
Any ideas, thanks again for all the help.
"Cannot insert duplicate key row in object 'dbo.AA210AirPrefTests' with unique index 'AA210AirPrefTests$MainKey
Looking at the data in this table there is no null values either in the first record. What do you think might be the problem with this one. At least it gave more of a details error.
For the tblPersoonel the detailed error : "Error occured during data migration" and that was it.
Any ideas, thanks again for all the help.
ASKER
Has anyone got any ideas on this matter? Please.
Try to export from Access to excel. If there is a duplicate key, access will give you an error message. Is there an import routine in SSMA for excel or comma separated value files?
To export from Access, highlight the table, File Export, save as a text file *.csv. If you open this file in Excel, you should be able to see all of the data.
ASKER
Hi Glenn,
I export the file to a .cvs from file with the table that is giving me the error: Occured during data migration.
Here is the first record and its column titles. I got no error while trying to export the data. There is two fields that have null values but neither of them are primary or foreign keys.
Here is the record for tblPersonnel:
PersonnelID CompID PeopleId FistName MI Lname Title PrintH Codes CodeLabel LocID PriLoc Name
1895828853 913 1608 Michael O'Loughlin 1 'Sf' Sf 230 1 Michael O'Loughlin
The MI filed is empty and the Title field is empty. But neither of them are a primary key or a foreign key.
The for the error: Cannot insert duplicat key row in object 'dbo.tablename' with unique index 'tablename$MainKey' this is this table and here is its first record from the .csv file. I didn't get an error exports this either from Access.
PersonnelID CompID PeopleId FistName MI Lname Title PrintH Codes CodeLabel LocID PrimaryLoc Name
1895828853 913 1608 Michael O'Loughlin 1 'Sf' Sf 230 1 Michael O'Loughlin
The MI filed is empty and the Title field is empty. But neither of them are a primary key or a foreign key.
Any ideas. Thanks for working with this issue.
I export the file to a .cvs from file with the table that is giving me the error: Occured during data migration.
Here is the first record and its column titles. I got no error while trying to export the data. There is two fields that have null values but neither of them are primary or foreign keys.
Here is the record for tblPersonnel:
PersonnelID CompID PeopleId FistName MI Lname Title PrintH Codes CodeLabel LocID PriLoc Name
1895828853 913 1608 Michael O'Loughlin 1 'Sf' Sf 230 1 Michael O'Loughlin
The MI filed is empty and the Title field is empty. But neither of them are a primary key or a foreign key.
The for the error: Cannot insert duplicat key row in object 'dbo.tablename' with unique index 'tablename$MainKey' this is this table and here is its first record from the .csv file. I didn't get an error exports this either from Access.
PersonnelID CompID PeopleId FistName MI Lname Title PrintH Codes CodeLabel LocID PrimaryLoc Name
1895828853 913 1608 Michael O'Loughlin 1 'Sf' Sf 230 1 Michael O'Loughlin
The MI filed is empty and the Title field is empty. But neither of them are a primary key or a foreign key.
Any ideas. Thanks for working with this issue.
ASKER
By the way they are two different tables for each error message. Sorry baout that i didn't make that clear.
First error message: tblPersonnel
Second Error: tblAAAir219PerfTest
First error message: tblPersonnel
Second Error: tblAAAir219PerfTest
Sabrina,
The first question is do you possibly have a duplicate key? How many keys does the table AA210AirPrefTests have?
Were they set to autonumber? Open the table, and sort on 'AA210AirPrefTests$MainKey
Regarding the null field in the first record, look at the tables that did transfer and see if any has a null field.
I would also highlight a table, File, Export, Textfile, *.csv, and then look at this information in excel. The real test is can you successfully reimport the table, and if so are their any errors.
The first question is do you possibly have a duplicate key? How many keys does the table AA210AirPrefTests have?
Were they set to autonumber? Open the table, and sort on 'AA210AirPrefTests$MainKey
Regarding the null field in the first record, look at the tables that did transfer and see if any has a null field.
I would also highlight a table, File, Export, Textfile, *.csv, and then look at this information in excel. The real test is can you successfully reimport the table, and if so are their any errors.
It might be the ' in the record. For giggles -- remove them from O'Loughlin, 'Sf' and Michael O'Loughlin. Then try it again.
ASKER
Both Primary Keys for both tables are autonumbers
So for tblPersonnel - Primary Key Personnel ID is an AutoNumber
This table has two foreign keys - CompanyID Number and PeopleID Number
Then for AA210AirPrefTests - Primary Key ID is an AutoNumber and this table has no Foreign Keys.
So for tblPersonnel - Primary Key Personnel ID is an AutoNumber
This table has two foreign keys - CompanyID Number and PeopleID Number
Then for AA210AirPrefTests - Primary Key ID is an AutoNumber and this table has no Foreign Keys.
Have you tried to remove the apostraphe?
ASKER
Sorry it has taken me so long to get back to this issue?
in the case of the tblPersonnel error it was a null value in a field the didn't allow null but it wasn't a primary key or a foreign key, so thanks for that.
however the other error I was getting:
"Cannot insert duplicate key row in object 'dbo.AA210AirPrefTests' with unique index 'AA210AirPrefTests$MainKey
I didn't find out till now that the MainKey stood for two other fields with in the table 'AA210AirPrefTests' called AppNum and TestID. Both these have access properties set for them with in the access database and they are as follows and I was wondering if this would cause errors:
AppNum
Required: Yes
Indexed: Yes (Duplicated OK)
TestID
Required: No
Indexed: Yes (Duplicated OK)
For SQL Sever, do you know if it allow duplicates? Would I have to change these fields to not allow duplicates? Both fields have duplicate data in them. And the TestId has null values in them, but as they are set up in Access at the moment all this is allowed but I was wondering when I went to migrate it to SQL Sever as these are considered a mainkey would this throw the above error that I am getting and how would you recommend me getting around this if at all possible.
Thanks again so much for all the help, and again sorry for taking so long to get back to you on this issue.
in the case of the tblPersonnel error it was a null value in a field the didn't allow null but it wasn't a primary key or a foreign key, so thanks for that.
however the other error I was getting:
"Cannot insert duplicate key row in object 'dbo.AA210AirPrefTests' with unique index 'AA210AirPrefTests$MainKey
I didn't find out till now that the MainKey stood for two other fields with in the table 'AA210AirPrefTests' called AppNum and TestID. Both these have access properties set for them with in the access database and they are as follows and I was wondering if this would cause errors:
AppNum
Required: Yes
Indexed: Yes (Duplicated OK)
TestID
Required: No
Indexed: Yes (Duplicated OK)
For SQL Sever, do you know if it allow duplicates? Would I have to change these fields to not allow duplicates? Both fields have duplicate data in them. And the TestId has null values in them, but as they are set up in Access at the moment all this is allowed but I was wondering when I went to migrate it to SQL Sever as these are considered a mainkey would this throw the above error that I am getting and how would you recommend me getting around this if at all possible.
Thanks again so much for all the help, and again sorry for taking so long to get back to you on this issue.
ASKER
Thanks very much for the help - One of the fields was required but also had nulls in it and this wasn't being acepted in SQL Sever so I set the default value of the fields to 0 and this made sure that none of the fields were null. It was 5 records in total that I think orginally they were test fields that could have been deleted but i didn't want to mess with it.
So thanks again.
So thanks again.
ASKER
Thanks