I needed to move several medium size MySQL backend apps to SQL server 2005 and did not have a cool tool for doing it. I found a way that was suprisingly easy... To do this you will need to install the ODBC drivers for your version of MySQL; I'm using MySQL 5.

Then in your SQL Management Studio, modify and run the following code.

EXEC master.dbo.sp_addlinkedserver
    @server = N'MYSQL',
    @srvproduct=N'MySQL',
    @provider=N'MSDASQL',
    @provstr=N'DRIVER={MySQL ODBC 5.1 Driver}; SERVER=127.0.0.1; DATABASE=db_name; USER=user; PASSWORD=pass; OPTION=3'

Then write seperate scripts that contain an instance of the following pieces of code per table in your MySQL db. In my case I had about 40 tables, so I wrote a ColdFusion job to do it smartly and added SQL to drop the target table if it existed.

SELECT * INTO [target_mssql_db].dbo.[target_mssql_table]
FROM openquery(MySQL, 'select * from mysql_db_name.mysql_tablename')

One of my apps had some tables with LOTS of rows, so I also set my script up to only run 5 conversions at a time to avoid timing out MSSQL locks. This should also work on MSSQL 2000 and I'm sure at least a few versions back of MySQL.

I was having some issues doing a standard install of SQL Server 2005\r\nSP2 (Standard). No matter what I did, the install complained about\r\nversioning issues. I knew you could blindly run a full install from\r\ncommand promt but I had never needed/wanted to. Out of desperation I\r\ndecided to give the install a try from command the prompt and had great\r\nsucccess.

setup.exe /qb INSTANCENAME=EGPS\r\nADDLOCAL=SQL_Engine SAPWD=***** AGTACCOUNT=ComputerName\\Services\r\nAGTPASSWORD=***** SQLACCOUNT=ComputerName\\Services SQLPASSWORD=*****\r\nSQLAUTOSTART=1 SECURITYMODE=SQL

I've been asked how to do this about a dozen times by developers who\r\nwere not very entrenched with SQL and I've had to do it my self a\r\nhandful of times.

I know the column name (or at least part of it) but I have no idea what table in this massive DB it lives in; how do I find it?

A simple query will net you the results you need:

SELECT
\r\n    so.name AS [Table], sc.name AS [Column],
\r\n    so.xType AS [Table Type], st.name AS [Data Type]
\r\nFROM dbo.syscolumns AS sc
\r\n    LEFT JOIN dbo.sysobjects AS so
\r\n        ON so.id = sc.id
\r\n    LEFT JOIN dbo.systypes AS st
\r\n        ON st.xtype = sc.xType
WHERE sc.name LIKE '%Partial Column Name Here%'
ORDER BY so.xType, so.name, sc.name

I ran across an interesting blog entry from Peter DeBetta where he shows us how to parse the iTunes library with T-SQL. I'm not really sure what to do with this information yet, but I like it non the less...