locked
Problems migrating from Access to SQL RRS feed

  • Question

  • Hey all, we were very excited when we used SSMA and it reported that everything worked fine when converting an access db to SQL server.

    We started noticing problems with data though where text was truncated during the migration.

    We figured out:

    • Access doesn't care what the 'Field Length' parameter is set to in the access table designer. It will let you exceed that number of characters without any problem up to the maximum program limit for the field.
    • When converting access to SQL SQL does care what the field length is set to and it truncates data to make it fit.

    For example if you have a field configured as 'Field Length 10' and in access you have 'abcdefghijklmnopqrstuvwxyz' when you migrate over to SQL you will have 'abcdefghij' and the worst part is there are no warnings given in SSMA.

    Is there some kind of script I can use to detect any fields that have data larger than it's own 'Field Length'?

    Is there any way to configure SSMA to not do this when migrating?

    Why does 'field length' exist if it isn't enforced?

    Wednesday, January 31, 2018 1:12 PM

All replies

  • Hi aweaver6142,

    Could you please tell us if what you mentioned is the field size of Text referred as below picture?


    If it is, it should truncates all the values in the field that exceed the specified field size as mentioned in this document: https://support.office.com/en-us/article/set-the-field-size-ba65e5a7-2e6f-4737-8e72-36b93f966a33.

    The truncation is operated by Access rather than SSMA, for SSMA, it can only get the truncated data from Access. As a result, it will not return any error. 

    >>Access doesn't care what the 'Field Length' parameter is set to in the access table designer. It will let you exceed that number of characters without any problem up to the maximum program limit for the field.

    This may be caused by a problem or configuration of Access, I suggest you opening a case in Access forum at https://answers.microsoft.com/en-us/msoffice/forum/msoffice_access

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, February 1, 2018 7:10 AM
  • Yes, that is the field I am referring to. In Access you can blow past that limit all day long but once you migrate to SQL everything is truncated. Regardless of what the docs indicate.
    Thursday, February 1, 2018 10:54 PM
  • Hi,

    SSMA relies on the metadata that authors of the source database provided in Access, if column is said to contain X characters - that's what SSMA will reproduce on the target. You can manually change the Data Type for the column in SQL Server. To do this select a table in the target objects tree, on "Table" tab double-click a cell in the Data Type column corresponding to the table column in question and update it to NVARCHAR(MAX).

    Regards,
    Alex.

    Monday, February 5, 2018 5:45 PM