locked
Referencing derived fields when migrating from Access to SQL RRS feed

  • Question

  • Hi

    I am migrating a database from Access 2013 to SQL Server 2014 using SSMA 5.2.  Within the Access database is a quite complex query that creates derived/calculated fields from within its own fieldset and then refers to them to create further derived/calculated fields.  For example:-

    Table1 has

    FieldA
    FieldB

    The query might do something like:-

    select FieldA, FieldB, FieldA+FieldB as 'FieldC', FieldC*2 as 'FieldC Doubled'

    Access is fine with this - but when SSMA attempts to upsize this query into a view it fails as SQL doesn't understand the reference to FieldC.

    This is obviously a really simple example to demonstrate my issue - the real query has hundreds of fields (don't ask!). I can convert it into a view manually using common table expressions - my problem is the query will be updated periodically by a 3rd party in its original Access form and I don't want to have to convert it manually each time its updated.

    Is there any method I've missed in SSMA to convert this query properly into a view?

    Thanks in advance,

    Mike.

    Tuesday, June 17, 2014 4:06 PM

Answers

  • Hi

    For the most part, SQL Server Migration Assistant (SSMA) just handles data type incompatibilities, index incompatibilities and so on when you migrate data from Access to SQL Server. If there is the alias in your Access queries, you need to covert the Access query with alias to common table expressions of SQL Server manually, for example, replace FieldC*2 to (FieldA+FieldB)*2, and then covert it to a SQL Server view.

    About how to update the original Access query to common table expressions with the third party tools, you can use the DataFast Utility and so on. However, Microsoft cannot make any representations regarding the quality, safety, or suitability of any the third party software or information.

    If you need the SSMA to process Access query with alias to common table expressions of SQL Server automatically, you could provide Microsoft a feature request at https://connect.microsoft.com/SQLServer  so that we can try to modify and expand the product features based on your needs.

    For more details, please refer to the following link:
    10+ tips for upsizing an Access database to SQL Server: http://www.techrepublic.com/blog/10-things/10-plus-tips-for-upsizing-an-access-database-to-sql-server/

    Thanks
    • Marked as answer by Sofiya Li Thursday, June 26, 2014 8:35 AM
    Thursday, June 19, 2014 6:16 AM