Creating Linked Server To MYSQL from Ms SQL Server and importing tables from MYSQL

One day I am seriously working on one project with Ms SQL server, Suddenly my manager jumped into my cabin and he asked me to import all the tables from MYSQL to SQL server.I never worked on MYSQL, and don't want to install any MYSQL client tools and export data into text files and load into SQL server.I thought of making easy and automate this process.
I started looking for available OLE DB provider for MYSQL, but I didn't find that.So I found an alternative way to connection to MYSQL from MS SQL server.

A provider is needed for any data source that is to be exposed through OLE DB. Microsoft releases providers for several common data sources, including SQL Server, Oracle, and Jet. In addition, Microsoft ships a special provider called the “OLE DB Provider for ODBC Drivers”. This was the very first OLE DB provider ever released, and is also known as “MSDASQL”, due to the fact that it is contained in “MSDASQL.DLL”.
MSDASQL allows OLE DB consumer applications to utilize ODBC drivers to connect to a data source. The provider converts incoming OLE DB calls into ODBC calls, and passes them on to the specified ODBC driver. It then retrieves results from the ODBC driver and formats those into OLE DB specific structures which the consumer then has access to.

To achieve this first you need to install "OLEDB provider for ODBC driver" and "MYSQL ODBC connector".
Download paths for above two components::
OLEDB provider for ODBC driver:::http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=20065
MYSQL ODBC connector::http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=20065
Once you install these two components, please check they are installed or not.
For OLEDB provider for ODBC driver, GO SSMS( Sql server Management studio), expand Server objects,
then expand Linked servers, then Providers, you can see "MSDASQL"

For MYSQL ODBC connector::
goto start--->RUN--->type ODBCAD32 it will open below window.
then go to drivers tab, there you can see installed driver.

Once you have provider and ODBC connector, create Linked server between MSSQL to MYSQL.

EXEC master.dbo.sp_addlinkedserver

@server = N'MYSQL',

@srvproduct=N'MySQL',

@provider=N'MSDASQL',

@provstr=N'DRIVER={MySQL ODBC 5.1 Driver}; SERVER= server name;

DATABASE=db_name; USER=XXX; PASSWORD= XXX_XXX; OPTION=3'

Once you have this successfully configured, Use below SP to import data from MYSQL server.

USE DB_Name

GO

/****** Object: StoredProcedure [dbo].[spGenerating_gene_leveldata_from_reporter_leveldata] Script Date: 01/31/2011 15:44:11 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF OBJECT_ID('[dbo].[sp_import_data_from_MYSQL]','P') is not null

drop procedure [dbo].[sp_import_data_from_MYSQL]

GO

CREATE PROCEDURE [dbo].[sp_import_data_from_MYSQL]

/*

purpose: This stored proc is used import data from MYSQL to MS SQL server

This stored proc produce some chucks, which we execute in different windows to increase the performance.

*/

AS

BEGIN

BEGIN try

SET NOCOUNT ON

declare @procName varchar(100), @dbName varchar(256), @date varchar(20), @ntab varchar(200), @nln varchar(4),@user varchar(56),@rows int

DECLARE @ErrMsg nvarchar(4000), @ErrSev varchar(3), @ErrSt varchar(4), @ErrProc nvarchar(126),@ErrLn nvarchar(10), @ErrNr nvarchar(10), @ErrLoc nvarchar(300)

select @procName=DB_NAME()+'.'+ss.name+'.'+so.name from sys.objects so, sys.schemas ss

where so.object_id = @@PROCID and so.schema_id=ss.schema_id

select @date = cast(getDate() as varchar(20)),@nln=''+char(10)+' ', @dbName=DB_NAME(),@ntab=@procName+'> ', @user=suser_sname(),@rows=0

DECLARE @tableName varchar(256),@databaseName varchar(256),@table_id int,@SQL Nvarchar(1024),@ParmDefinition nvarchar(500),@SQl_TXT varchar(MAX)

SELECT @databaseName='exome',@table_id=1

raiserror('Entering stored procedure "%s" at "%s"',0,1,@procName,@date) with nowait

IF (Cursor_Status('global', 'c_tables') >= 0 or Cursor_Status('local', 'c_tables') >= 0)

begin close c_tables; deallocate c_tables end

declare c_tables cursor for select Tables_in_exome FROM openquery(MYSQL, 'SHOW TABLES IN exome')

open c_tables

fetch next from c_tables into @tableName

while @@FETCH_STATUS=0

BEGIN

      DECLARE @cnt int=0

raiserror('%d.) Loading data from table%s',0,1,@table_id,@tableName) with nowait

SELECT @SQL='

SELECT @cntOUT=cnt FROM openquery(MYSQL, ''SELECT COUNT(*) as cnt FROM '+@databaseName+'.'+@tableName+''')'

SET @ParmDefinition = N'@cntOUT int OUTPUT'

EXECUTE sp_executesql @SQL, @ParmDefinition, @cntOUT=@cnt OUTPUT;

raiserror('Number of rows in %s are %d ',0,1,@tableName,@cnt) with nowait

if @cnt>10000

BEGIN

raiserror('loading in batches (each batch it will load 10000 records)',0,1)

DECLARE @start int, @end int

SELECT @start=0,@end=10000

WHILE((@cnt+10000)>@start)

BEGIN

if @start=0

BEGIN

SELECT @SQl_TXT='SELECT * INTO '+@dbName+'.'+@databaseName+'.'+@tableName+' FROM openquery(MYSQL, ''SELECT * FROM '+@databaseName+'.'+@tableName+' LIMIT '+convert(varchar(20),@start)+' ,'+convert(varchar(20),@end)+''')

GO'

print @SQl_TXT

exec(@SQl_TXT)

END ELSE BEGIN

SELECT @SQl_TXT='INSERT INTO '+@dbName+'.'+@databaseName+'.'+@tableName+' SELECT * FROM openquery(MYSQL, ''SELECT * FROM '+@databaseName+'.'+@tableName+' LIMIT '+convert(varchar(20),@start)+' ,'+convert(varchar(20),@end)+''')

GO'

print @SQl_TXT

exec(@SQl_TXT)

END

select @start=@start+10000

--select @end=@end+10000

END

END ELSE BEGIN

SELECT @SQl_TXT='SELECT * INTO '+@dbName+'.'+@databaseName+'.'+@tableName+' FROM openquery(MYSQL, ''SELECT * FROM '+@databaseName+'.'+@tableName+''')

GO'

print @SQl_TXT

exec(@SQl_TXT)

END

raiserror('%s%s',0,1,@nln,@nln) with nowait

SET @table_id=@table_id+1    

fetch next from c_tables into @tableName

END

CLOSE c_tables

DEALLOCATE c_tables

END TRY

BEGIN CATCH

select @ErrMsg =ERROR_MESSAGE(), @ErrSev =ERROR_SEVERITY(), @ErrSt =ERROR_STATE(),@ErrProc =ERROR_PROCEDURE(), @ErrLn = ERROR_LINE(), @ErrNr = ERROR_NUMBER()

raiserror('Failed to execute procname %s, %s Error nr %s, state %s, sev %s; line %s, %s with msg: %s',16,-1,@procName, @nln,@ErrNr,@ErrSt,@ErrSev,@ErrLn,@nln,@ErrMsg)

END CATCH

END

GO