Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 676
  • Last Modified:

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.
0
sabrina_spillane
Asked:
sabrina_spillane
  • 8
  • 6
1 Solution
 
Glenn_MooreCommented:
Could some of the fields in the first record be null?
0
 
sabrina_spillaneAuthor Commented:
I can view the data within the SSMA in the access section of it for that table and the first record does have information in it. Now one of the fields in the first record is null but that is not a primary or a foreign key so I don't think it should matter if it would be null or not. What do you think?

Thanks
0
 
sabrina_spillaneAuthor Commented:
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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
sabrina_spillaneAuthor Commented:
Has anyone got any ideas on this matter? Please.
0
 
Glenn_MooreCommented:
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?
0
 
Glenn_MooreCommented:
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.  
0
 
sabrina_spillaneAuthor Commented:
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.
0
 
sabrina_spillaneAuthor Commented:
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
0
 
Glenn_MooreCommented:
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 and see if there are indeed duplicates.  
    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.
0
 
Glenn_MooreCommented:
It might be the ' in the record.  For giggles -- remove them from O'Loughlin,  'Sf' and Michael O'Loughlin.  Then try it again.
0
 
sabrina_spillaneAuthor Commented:
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.
0
 
Glenn_MooreCommented:
Have you tried to remove the apostraphe?
0
 
sabrina_spillaneAuthor Commented:
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.
0
 
sabrina_spillaneAuthor Commented:
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.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now