SSMA, part 3

My previous posts about the SQL Migration Assistant for Access reported generally favorable results with this product which is used to migrate Access data into SQL Server. It does a remarkably better job than the old Access Upsizing Wizard which very often failed, especially when dealing with date fields. When SSMA has completed its work, the system is mostly operational.

However, there are two common Access design flaws that can impact the application - the first is relying on the behavior of Boolean fields to evaluate as -1 = True and 0 = False. Many Access developers will test for "-1" meaning True, but SQL Server returns "1" for bit fields so the comparison logic fails. The fix is simply to substitute "-1" with "True" (without the quotes) and the operation will succeed.

A second design flaw recently noted is that in Access you can subtract one date from another date, and then perform math on the result - for example

24 * ([date_end] - [date_start])

in Access will give you the number of hours between the two date/time values. However, once your data is moved into SQL Server, this expression will result in

Implicit conversion from data type smalldatetime to int is not allowed. Use the CONVERT function to run this query.

The solution is to use the DateDiff function which exists in both Access and SQL Server. In Access you would write

DateDiff("h", [date_start], [date_end])

and when converting this query to T-SQL for SQL Server you might use

DATEDIFF(hh, [date_start], [date_end])