Get Data Using Openquery() From Mysql To Sql 2005

I'm trying to use linked server to import big amount of data(around 9 million rows) from mysql to a sql 2005 table.

I plan to create a stored procedure and use job agent to run it at off-peak time everyday. The SP is like:

.....

Truncate table sqltblA;

Insert Into sqltblA(col1, col2, col3)
Select col1, col2, col3
From OpenQuery('mysql', 'Select col1, col2, col3 From mytblB')

.....

But when I tried to CREATE the above SP from management studio, seems the sql server was trying to get all data from table mytblB and ended up with failure after several minutes hang. The error message is sth. like "running out memeory". BTW, during that several minutes hang, I can't query mytblB using mysql's tool as well. it seems to me that table got locked.



However if i try to change the returned result to 100,000 rows by changing script to

Insert Into sqltblA(col1, col2, col3)
Select col1, col2, col3
From OpenQuery('mysql', 'Select col1, col2, col3
From mytblB Limit 100000')

The SP could be created without any problem and running the SP could get the data in table sqltblA.

But that's not what I want, I need all rows instead of a certain amount of rows.

Does anyone know what's going on there when I try to CREATE that SP and any solution to it?


Plus I tried to use SSIS as well. When I'm configuring the DataReader source, which uses ADO.NET's ODBC option to connect to mysql, if its sqlcommand is "select * from mytblB" without the Limit key word, then the configuration page will hang there as well and table mytblB is not accessible by using mysql tool. I'm just sick of wasting time on that and that's why I chose to use SP istead of SSIS. But same result. :-(