Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

Thread: DB Conversion in the Studio

  1. #11
    Join Date
    Feb 2009
    Location
    Somewhere in Vermont, USA - unless I'm not
    Posts
    11,085

    Default Re: DB Conversion in the Studio

    It now hates (aka can't find) the MySQL ODBC dll. I can't either, but can log into MySQL fine & the ODBC administrator sees it... Maybe a 64 vs 32 thing? Dunno
    Garret

    Time for an oldie but goodie:

    "If it ain't broke, you're not trying." - Red Green

  2. #12
    Join Date
    Feb 2009
    Location
    Somewhere in Vermont, USA - unless I'm not
    Posts
    11,085

    Default Re: DB Conversion in the Studio

    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;
    Garret

    Time for an oldie but goodie:

    "If it ain't broke, you're not trying." - Red Green

  3. #13
    Join Date
    Nov 2008
    Location
    Round Rock, TX
    Posts
    8,850

    Default Re: DB Conversion in the Studio

    Hi Garret,

    thanks for the update. I'm surprised the migration tool didn't change the dates.

    Can you now connect to the database using the Studio?

  4. #14
    Join Date
    Feb 2009
    Location
    Somewhere in Vermont, USA - unless I'm not
    Posts
    11,085

    Default Re: DB Conversion in the Studio

    Hi Dennis -

    It may have in this version, but I knew past versions did not.

    I have yet to figure out how to connect to MS SQL using managed connections (this system had Mertech to MySQL) - but have had to work on other stuff. I'm sure your help will tell me all I need to know
    Garret

    Time for an oldie but goodie:

    "If it ain't broke, you're not trying." - Red Green

  5. #15
    Join Date
    Nov 2008
    Location
    Round Rock, TX
    Posts
    8,850

    Default Re: DB Conversion in the Studio

    It better!

Page 2 of 2 FirstFirst 12

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •