Well - SSMA worked - but it wasn't tons of fun. Unfortunately it doesn't have a way to create a script to run for the migration, though it does create one for creating the schema.
Some pointers should anyone else need to do this:
Get the latest! https://www.microsoft.com/en-us/down....aspx?id=54257
When running it, it has a known issue of crashing if run on a multi-core processor. I had to do 2 things to keep it from crashing (thank you - once again - StackExchange). https://dba.stackexchange.com/questi...rate-data-step
The above link describes 2 things that worked for people - I had to do both to get it to run - limit to one core in Task Manager & limit to one thread in SSMA.
Like any migration, dates will need to be cleaned up. It will not handle '0000-00-00' dates (which MySQL can). They must be changed to '0001-01-01' (while it can be changed, it defaults DateTime fields to DateTime2(0) - so no '1753-01-01" BS). It may not like null dates either - but I don't know because I'd already changed nulls to '0001-01-01' for the studio tool. I will try to test this again soon & report back.
Here's a script that lists all DateTimes & Dates in the MySQL DB & creates another that will change all from '0000-00-00' to '0001-01-01':
Code:
select concat('UPDATE ',TABLE_NAME,' SET ',COLUMN_NAME,'= ''0001-01-01'' WHERE ',COLUMN_NAME,'=0;')
FROM information_schema.COLUMNS
where TABLE_SCHEMA = 'YourDBName' and DATA_TYPE in ('datetime', 'date');
Replace 'YourDBName' with (surprise! ) your DB name.
It produces (in my case about 800) lines like this:
Code:
UPDATE alertcat SET createddate= '0001-01-01' WHERE createddate=0;
If you want to change nulls - change it to produce:
Code:
UPDATE alertcat SET createddate= '0001-01-01' WHERE createddate=0 OR createddate IS NULL;