Solved

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

Posted on 2006-10-28
14
645 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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
 

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

860 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