Table fails to upsize

[David Anderson]

I am using Microsoft's Sequel Server Migration Assistant for Access (SSMA) to migrate a back end Access 2003 database to an MDF file (in SQL Server Express 2005). All but one of my tables convert to SQL Server format, complete with all data. The report for the problem table only lists a bunch of information messages, none of which look very important. Despite there being no errors or warnings, no data is transferred to the new table.

What might be going wrong here?

David

 

[MSAccessRookie]

Some possible reasons

Failure to migrate issues are very often related to one of three things:

Data Types

Column Names

Linked Table Issues

Verify that you have valid SQL Server Column Names and table structures (linked table issues). If you are sure, post the Design of the table and we can see if particular Data Types are creating problems.

 

[David Anderson]

The table design details are shown below. Apologies for the poor formatting, but I failed to find any better way to do it. EntrantID is the primary key.

Table: Entrants

# FieldName DataType Size
1 EntrantID Number, Long Integer 4
2 TitleID Number, Long Integer 4
3 FirstName Text 20
4 Initials Text 5
5 LastNameA Text 25
6 LastName Text 25
7 CountryofOriginID Number, Long Integer 4
8 Distinctions Text 100
9 ShortDistinctions Text 100
10 OtherDistinctions Text 50
11 Address1 Text 35
12 Address2 Text 35
13 Address3 Text 35
14 City Text 35
15 County Text 20
16 Postcode Text 12
17 CountryID Number, Long Integer 4
18 Email1 Text 50
19 Email2 Text 50
20 CreditAmount Number, Double 8
21 SCCMemberNow Yes/No 1
22 PersonComments Text 120
23 CreatedbyUserID Number, Long Integer 4
24 CreationDate Date/Time 8
25 ChangedbyUserID Number, Long Integer 4
26 ChangeDate Date/Time 8
27 Deceased Yes/No 1
28 EntrySummaryComplete Yes/No 1
29 Banned Yes/No 1
30 TestData Yes/No 1

 

[David Anderson]

In case it is relevant, here is a summary of the information messages reported by SSMA for the Entrants table,

A2SS0005: The Zero-length-not-allowed flag on the column 'FirstName' (and 4 other fields) was converted to a check constraint.
A2SS0020: New timestamp column 'SSMA_TimeStamp' created.
A2SS0029: Primary key name 'PrimaryKey' was changed during conversion.
A2SS0029: Index name 'ChangedbyUserID' was changed during conversion.
A2SS0029: Index name 'CreatedbyUserID' was changed during conversion.
A2SS0029: Index name 'EntrantID' was changed during conversion.
A2SS0029: Index name 'EntrantsCountryID' was changed during conversion.
A2SS0029: Index name 'NationalityID' was changed during conversion.
A2SS0029: Index name 'Postcode' was changed during conversion.
A2SS0029: Index name 'SalutationID' was changed during conversion.

 

[MSAccessRookie]

The table design details are shown below. Apologies for the poor formatting, but I failed to find any better way to do it. EntrantID is the primary key.

Table: Entrants

# FieldName DataType Size
1 EntrantID Number, Long Integer 4
2 TitleID Number, Long Integer 4
3 FirstName Text 20
4 Initials Text 5
5 LastNameA Text 25
6 LastName Text 25
7 CountryofOriginID Number, Long Integer 4
8 Distinctions Text 100
9 ShortDistinctions Text 100
10 OtherDistinctions Text 50
11 Address1 Text 35
12 Address2 Text 35
13 Address3 Text 35
14 City Text 35
15 County Text 20
16 Postcode Text 12
17 CountryID Number, Long Integer 4
18 Email1 Text 50
19 Email2 Text 50
20 CreditAmount Number, Double 8
21 SCCMemberNow Yes/No 1
22 PersonComments Text 120
23 CreatedbyUserID Number, Long Integer 4
24 CreationDate Date/Time 8
25 ChangedbyUserID Number, Long Integer 4
26 ChangeDate Date/Time 8
27 Deceased Yes/No 1
28 EntrySummaryComplete Yes/No 1
29 Banned Yes/No 1
30 TestData Yes/No 1

Not to worry about the formatting, I was able to read it OK.

I see nothing unusual about the column names or the data structures. What is the conversion error message that you are getting?

Sometimes SSMA will fail due to data if the table contents violate restraints on the design of the table. For instance a NULL value in a table that does not allow NULLS, or Duplicated IDs in a table with a unique constraint (Yes, I have encountered BOTH of the ABOVE, and had to have the users repair the data before we could continue).

Let us know what you find

MS Access tends to be much more forgiving is this area.

 

[David Anderson]

There are no error messages, only the 'information' messages listed in my last post.

David

 

[David Anderson]

Sometimes SSMA will fail due to data if the table contents violate restraints on the design of the table. For instance a NULL value in a table that does not allow NULLS, or Duplicated IDs in a table with a unique constraint (Yes, I have encountered BOTH of the ABOVE, and had to have the users repair the data before we could continue).

I didn't think it was possible for the data in an Access table to contravene the data constraints of its design. In what circumstances can this happen?

David

 

[MSAccessRookie]

In case it is relevant, here is a summary of the information messages reported by SSMA for the Entrants table,

A2SS0005: The Zero-length-not-allowed flag on the column 'FirstName' (and 4 other fields) was converted to a check constraint.
A2SS0020: New timestamp column 'SSMA_TimeStamp' created.
A2SS0029: Primary key name 'PrimaryKey' was changed during conversion.
A2SS0029: Index name 'ChangedbyUserID' was changed during conversion.
A2SS0029: Index name 'CreatedbyUserID' was changed during conversion.
A2SS0029: Index name 'EntrantID' was changed during conversion.
A2SS0029: Index name 'EntrantsCountryID' was changed during conversion.
A2SS0029: Index name 'NationalityID' was changed during conversion.
A2SS0029: Index name 'Postcode' was changed during conversion.
A2SS0029: Index name 'SalutationID' was changed during conversion.

I missed this post or I would not have made my last one the way that I did. The good news is that there may actually not be any problems being reported here. Run the Migration again and when the "Convert Selected Objects" is completed, check the status (How many Errors, Warnings, and Informational you find).

An Error Message indicates a problem that cannot be ignored. I currently have no errors, and therefore no examples.

A Warning Message indicates a problem that might be able to be ignored, but should be evaluated because it could cause issues when running queries against the table. An example of this is a table that is missing a Primary Key.

An Informational Message indicates a problem that was encountered and was corrected by SSMA. You are given the message so that you can evaluate the correction that was made and make your own correction if SSMA did not do what you intended. These will usually not cause issues when running queries against the table.

 

[MSAccessRookie]

I didn't think it was possible for the data in an Access table to contravene the data constraints of its design. In what circumstances can this happen?

David

I am not sure how, but I had a table of Institutions that had an FK of InstitutionTypeID (NOT NULL, No Duplicates) that had 21 records containing NULL values. We had to replace them with valid values before we were able to go into production. Not sure how it happened (probably a legacy BUG from the past), but pretty sure it cannot happen any more.

 

[MSAccessRookie]

There are no error messages, only the 'information' messages listed in my last post.

David

Informational messages are a good thing (see my post above). the only three that I see that will give you headaches might be the first three:

A2SS0005: The Zero-length-not-allowed flag on the column 'FirstName' (and 4 other fields) was converted to a check constraint.

Normally this is not an issue. verify that the check constraint is enough to meet your needs.

A2SS0020: New timestamp column 'SSMA_TimeStamp' created.

ONLY ONE? In 41 tables, I got 29 'SSMA_TimeStamp' Columns added.

They are added at the end of the data, are ignored by all of your queries until you choose to use them, and will not create any issues.

A2SS0029: Primary key name 'PrimaryKey' was changed during conversion.

Verify if any query actually uses the PrimaryKey, and act accordingly. I would change that column name in Access to prevent any unexpected problems.

That being said, If you have VB Code or Macros that address your indexes by name when using them, they could also have issues.

I am curious about one thing. Was this the only table with Indexes? I would have expected a lot more Index name modification Messages

 

[David Anderson]

Problem now resolved! It was indeed a case of null values in two fields that did not allow nulls. I replaced these nulls with real data and SSMA was then able to complete the conversion.

With hindsight, I realise that I must have introduced the no-nulls constraint after populating much of the data in the table. Access does not appear to flag such issues.

Thanks very much for your rapid assistance on this matter. It's much appreciated!

David

 

[MSAccessRookie]

Problem now resolved! It was indeed a case of null values in two fields that did not allow nulls. I replaced these nulls with real data and SSMA was then able to complete the conversion.

With hindsight, I realise that I must have introduced the no-nulls constraint after populating much of the data in the table. Access does not appear to flag such issues.

Thanks very much for your rapid assistance on this matter. It's much appreciated!

David

The pleasure is always mine. Good luck in your conversion project and feel free to write back if there are any further issues. I am truly Just a Rookie compared to some of the Experts online here, and We remain here to assist.

 

[David Anderson]

With regard to your comments about the info messages, I only quoted the messages relating to the Entrants table. I don't yet understand the messages about changing the name of the primary key and all the indexes. Why will they have been changed and what will they have been changed to?

David

 

[MSAccessRookie]

I myself am not too sure about the PrimaryKey thing unless it has something to do with the Index (see below), but the Indexes on the server do have a specific Naming Convention that is different from the one that Access Uses, and EVERY single one of mine was changed. The index Convention is something like

{YourTableName}${ YourIndexName } (Default appears to be "PrimaryKey")