MySQL -> MS SQL

Hi there, I'm trying to migrate from MySQL to SQL Server 2000. I found
a white paper on the Microsoft web site that explains how to do this:
http://www.microsoft.com/technet/pro...oy/mysql.mspx.
The MySQL 3.51 ODBC driver was installed. In the ODBC controls if we
hit "Test" the connection checks out okay. I ran the Import and Export
Data tool. When the MySQL db is selected as the data and I try to
connect, a confusing error pops:
"A serious error occurred when connecting to the provider."
The connection seems to work because if we enter the wrong username and
password we get an "Access denied for user" message.
Using the mysqldump utility, we created a *.SQL file of our tables.
Then we tried to use the Query Analyzer tool to import the data. When
the .SQL file is opened, we get another error: "File.sql contains long
line over 64k limit".
Any suggestions as to what we could do to make this work?
If we try to re-import the data back into MySQL using mysql -uuser
-ppassword dbname < file.sql it works fine.
Thanks!
Consider using the BCP utility to bulk import the data, not only is it
faster than Query Analyzer, it can support far more data...
Steve
"Bill Smith" <bsmith@.nospam.com> wrote in message
news:O9xE8mCBFHA.1524@.TK2MSFTNGP09.phx.gbl...
> Hi there, I'm trying to migrate from MySQL to SQL Server 2000. I found
> a white paper on the Microsoft web site that explains how to do this:
> http://www.microsoft.com/technet/pro...oy/mysql.mspx.
> The MySQL 3.51 ODBC driver was installed. In the ODBC controls if we
> hit "Test" the connection checks out okay. I ran the Import and Export
> Data tool. When the MySQL db is selected as the data and I try to
> connect, a confusing error pops:
> "A serious error occurred when connecting to the provider."
> The connection seems to work because if we enter the wrong username and
> password we get an "Access denied for user" message.
> Using the mysqldump utility, we created a *.SQL file of our tables.
> Then we tried to use the Query Analyzer tool to import the data. When
> the .SQL file is opened, we get another error: "File.sql contains long
> line over 64k limit".
> Any suggestions as to what we could do to make this work?
> If we try to re-import the data back into MySQL using mysql -uuser
> -ppassword dbname < file.sql it works fine.
> Thanks!
|||Hi,
in order to import data from mysql you'll need the followind:
- DSN to MYSQL Server using MyODBC-3.51.06 drivers
- Linked Server to the DSN
the following script:
--sp_tables_ex 'linked_server_name'
use [dababase]
dbcc traceon (8765)
if object_id ([table to import]) is not null drop table dbo.[table to
import]
select * into dbo.[table to import] from openquery ([linked_server_name],
'select * from [table to import] )
- after importing all mysql tables you create additional keys, etc...
Tudor
"Steve Thompson" <stevethompson@.nomail.please> wrote in message
news:%2319us4KBFHA.3140@.TK2MSFTNGP15.phx.gbl...
> Consider using the BCP utility to bulk import the data, not only is it
> faster than Query Analyzer, it can support far more data...
> Steve
> "Bill Smith" <bsmith@.nospam.com> wrote in message
> news:O9xE8mCBFHA.1524@.TK2MSFTNGP09.phx.gbl...
>