Minty
AWF VIP
- Local time
- Today, 10:16
- Joined
- Jul 26, 2013
- Messages
- 10,673
I've an found an interesting bug with the SQL Server Migration Assistant for Access (SSMA) recently.
I was trying to import / migrate data to a Azure SQL database, from an Access database that had most of it's date fields set to Date/Time Extended .
SSMA refused to identify them as any type of data:
Original Table:
SSMA Table definition:
In this state it fails to import /convert /migrate any data from the table.
You can't tell it that the field is a specific data type or force it to text etc. etc.
There ae two work arounds, neither are ideal in a large migration;
The latter takes a lot longer, has to be done one table at a time and gives you no control over the destination data types, e.g. bit conversion, only using DateTime in SQL server, not DateTime2 etc.
I cannot find anywhere to report this as a bug.
I have uploaded a small sample if anyone want to prove a point or find a better solution.
I was trying to import / migrate data to a Azure SQL database, from an Access database that had most of it's date fields set to Date/Time Extended .
SSMA refused to identify them as any type of data:
Original Table:
SSMA Table definition:
In this state it fails to import /convert /migrate any data from the table.
You can't tell it that the field is a specific data type or force it to text etc. etc.
There ae two work arounds, neither are ideal in a large migration;
- Manually change all the Date/Time Extended fields to normal DateTime fields in the table design in Access,
- Right click and manually export the table to an ODBC Database.
The latter takes a lot longer, has to be done one table at a time and gives you no control over the destination data types, e.g. bit conversion, only using DateTime in SQL server, not DateTime2 etc.
I cannot find anywhere to report this as a bug.
I have uploaded a small sample if anyone want to prove a point or find a better solution.
