Larry Kooper

I needed to migrate a MySQL database to Microsoft SQL Server for a project I was working on.  (Why, you ask?  Well, one of the users wanted to run Crystal Reports and they were having trouble getting it to work with the MySQL.  Also, most people here at MegaCultureCorp use SQL Server.)   The application is written in Ruby on Rails.

So I found this handy Microsoft article (link).  I downloaded and installed MyODBC from the MySQL site as they recommended, and created a DTS package to copy the tables over from the MySQL database to the newly created SQL Server database.  (One slight gotcha here was that in the DTS wizard dialog box, I had to manually change the name of the destination tables so that [dbo] was displayed rather than my Windows login).

I copied the tables over and everything was fine, right?  Well, not exactly.  One of the users complained that he couldn’t add a new item.  After apologizing profusely, I looked into the problem.  Turns out that the DTS package does not convert the MySQL table structure perfectly.  A couple of crucial items were left out — default values set in MySQL, and MySQL auto_increment columns (called IDENTITY columns in SQL Server).  I added the defaults with SQL statements like this:

ALTER TABLE my_table_name
ADD CONSTRAINT some_made_up_name
DEFAULT 1 FOR my_column

Tried doing the same thing for the IDENTITY columns, but I read a posting that said that couldn’t be done via T-SQL - so I changed the columns to IDENTITY using Enterprise Manager.  All working again.

Welcome to my tech blog. I will be posting tech tips here, valuable lessons learned. When I have a problem, usually I search Google, and often the answer is on a blog. I’m starting this blog so I can give something back.