The SQL Server Migration Assistant for Access assessment report displays the results from converting a Jet based Microsoft Access solution to use SQL Server. The report displays errors, warnings, and information settings based on either the Default Project Settings or the Project settings you have set when you run the report. For information regarding the project settings, see “Access to SQL Server Migration: Understanding SSMA Project Settings”. The Error List user interface provides a convenient way to navigate to the specific object that has the error, warning, or informational message. This blog builds on Access to SQL Server Migration: How to Use SSMA using the Access Northwind 2007 template.
There are two ways of viewing the assessment report in SSMA. The first way is to access the report after running the Migration Wizard as shown below.
When you run the report via the wizard, you’ve already completed the migration so there is no opportunity to correct any errors before perform the Convert Schema and Migrate Data commands. There is a nice feature within the Migration Wizard dialog that allows you to click on the hyperlink total of messages to display the Error List dialog.
The second way of launching the error report is to not use the wizard and follow these steps:
For the Northwind 2007 database, your assessment report will look like this.
When you manually run the report by selecting all objects, you may get more errors reported because SSMA reports against potential issues when attempting to migrate Queries into SQL Server View statements. In general, you will want to take a linked table approach when migrating your solution. This approach keeps the Query definition in Access as is and relies on using the linked table references in the query to process the results using the Jet engine.
The recommended approach is to uncheck Queries in the Access Metadata Explorer before running the report. This approach also prevents SSMA from creating Views on SQL Server when you eventually issue the Convert Schema and Synchronize with Database commands.
Assuming that you converted Northwind 2007 using Migration Wizard with the Linked Tables option, you will see and Error List when you close the Assessment Report and the Migration Wizard as shown below.
By clicking on the 245 Warnings and 233 Information controls highlighted above, you can show just the 6 Errors reported after the conversion.
By double clicking on the error, SSMA takes you to the object associated with the message in both the Access Metadata Explorer and SQL Server Metadata Explorer and displays the definition of the objects as shown below.
You can then see how SSMA mapped the unsupported data type in SQL Server from Access by scrolling through the Access and SQL Server table definitions.
It’s been our observation that Access solutions that need migration to SQL Server very seldom use the problem data types used for the Northwind2007 sample database. Here are some things to consider if you do need to migrate these types.
The Attachment type stores the file in Access database. In SQL Server, you have several options to consider. You will want to extract the files from the Access database and then consider storing links to the files in your SQL Server database or using the FILESTREAM feature to maintain the attachments. In the upcoming SQL Server code name “Denali” release, you can use the new FileTable feature.
The Hyperlink data type ends up getting mapped to a nvarchar(max) type by SSMA. You may want to alter the result using the table editing feature in SSMA’s SQL Server Metadata Explorer or using SQL Server Management Studio to use a smaller value. In your Access solution, you can still use the hyperlink behavior in Forms and Reports if you set the Hyperlink property for the control to true.
The ComplexLong data type is the result of using a lookup query and choosing the multi-selection option for the Long data type. Underneath the covers in Access, there is a junction table that records the results of the multiple selection. In SQL Server, you can emulate the same behavior with a junction table based on the query and then reference the junction table in your Access solution.
As you can see, SSMA provides two mechanisms to help you identify potential issues that you may need to deal with for migrating an Access database to SQL Server. The Assessment Report and Error List user interfaces are also part of SSMA for MySQL, Oracle and Sybase and work in a similar way. The time estimates included in the Assessment Report are just a guideline to help in estimating the scope of the changes needed and can vary greatly in your approach to solving a specific issue.
For specific details on each of the project options, please refer to the Project Settings help topics for SSMA for Access.
Optimizing Microsoft Office Access Applications Linked to SQL Server http://msdn2.microsoft.com/en-us/library/bb188204.aspx describes in detail many of the techniques that were only mentioned briefly in this blog post.
The UtterAccess forum has a discussion group dedicated to SQL Server questions http://www.utteraccess.com/forum/Microsoft-SQL-Server-f32.html. You can post questions and get answers from Access experts who work with SQL Server on a daily basis.
The FMS Upsizing Resource Center contains links to various resources http://www.fmsinc.com/MicrosoftAccess/SQLServerUpsizing/index.html.
JStreet Technology's developer downloads section http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp has useful tools and presentations to help you get up to speed.
You mentioned options for ATTACHMENT datatype as links to the attached file or use FILESTERAM option , how can we migrate the attachment file into a table in SQL server database where filestream has been enabled ?
To migrate attachment file, you need to migrate them manually via tool such as SSIS. We advise that you understand the overall design and implementation of the target environment to determine how blob data relates to the set of structured data. Please follow the FileStream usage provided here:
How do you print or export the error list after the conversion - on my first run I have over 230 info & warnings -- way to many to review on the one inch window at the bottom of the screen