Solved

Issue with using SSMA Tool to Migrate a table from Access to SQL Sever

Posted on 2006-10-28
14
641 Views
Last Modified: 2008-01-09
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
Comment
Question by:sabrina_spillane
  • 8
  • 6
14 Comments
 
LVL 14

Accepted Solution

by:
Glenn_Moore earned 250 total points
ID: 17826799
Could some of the fields in the first record be null?
0
 

Author Comment

by:sabrina_spillane
ID: 17826809
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
 

Author Comment

by:sabrina_spillane
ID: 17826865
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
 

Author Comment

by:sabrina_spillane
ID: 17828981
Has anyone got any ideas on this matter? Please.
0
 
LVL 14

Expert Comment

by:Glenn_Moore
ID: 17829030
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
 
LVL 14

Expert Comment

by:Glenn_Moore
ID: 17829039
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
 

Author Comment

by:sabrina_spillane
ID: 17829268
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:sabrina_spillane
ID: 17829306
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
 
LVL 14

Expert Comment

by:Glenn_Moore
ID: 17829316
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
 
LVL 14

Expert Comment

by:Glenn_Moore
ID: 17829336
It might be the ' in the record.  For giggles -- remove them from O'Loughlin,  'Sf' and Michael O'Loughlin.  Then try it again.
0
 

Author Comment

by:sabrina_spillane
ID: 17829543
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
 
LVL 14

Expert Comment

by:Glenn_Moore
ID: 17829553
Have you tried to remove the apostraphe?
0
 

Author Comment

by:sabrina_spillane
ID: 17910840
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
 

Author Comment

by:sabrina_spillane
ID: 17961885
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now