Transfering Microsoft server SQL data?

sql to mysql

[astronaut]

Hi, I am trying to transfer SQL data from a Microsoft ASP server with Enterprise Manager and I need to transfer the data to my Linux server. I use phpMyAdmin for all mySQL queries and management.

I tried exporting to an excel file, but phpMyAdmin creates errors when I try to import the file.

any thoughts, experience or ideas to help me out greatly GREATLY appreciated!

:)

[Dreamquick]

If you have a relatively simple data format try CSV or even tab delimited as that's pretty generic as file formats go.

- Tony

[gergoe]

If you can access your mysql box from the machine where the Enterprise Manager is running then you can use the dts (data transformation service), by exporting the data to any other odbc compliant database (like mysql). See the SQL Server books installed with the Enterprise Manager

[astronaut]

Thanks Dreamquick and Gergoe, I did export the files from Enterprise Manager as CVS data and it appears the data may have improper syntax, so it is having a rough time importing. I find this error message generic and not very helpfull, but this is what I recieved after attempting to import the file ->

MySQL said:

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near

I will try the dts! New learning experience, thanks!

[gergoe]

I'm not really into mysql but this error message sounds as if the phpmyadmin were issuing wrong sql commands, maybe it is not the right version for your mysql?

[timster]

right syntax to use near

Did that come through correctly -- it just "trails off"?

It could be your CSV files have Windows-style CRLF line endings, and the Linux is expecting Unix-style LF. So MySQL chokes on the unexpected whitespace character and gives you that "invisible" error message.

You can specify an LF line ending in Enterprise manager, or do like gergoe said and use ODBC to import directly, skipping the text file.

[coopster]

A good read on the site...

Migrating from Microsoft SQL Server and Access to MySQL [dev.mysql.com]

[astronaut]

MySQL said:

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near

No, I snipped that bit before it went into the specific line that was generating the errors. I think it deffinately has to do with

Windows-style CRLF line endings, and the Linux is expecting Unix-style LF

Is there a way to format the text file to replace the CRLF with LF style line endings? I haven't been successful in using ODBC to do a direct transfer. Totally new to all this, so thank you all for helping me out!

[digitalv]

Not to start a flame war, but is there any particular reason you're moving to mySQL? Microsoft SQL has numerous advantages over MY including stored procedures, linked servers, clustering capabilities, better performance for large (100GB - 2 TB) databases, etc.

If you're transferring to mySQL because you think it's "better", I would stick with Microsoft. I know you said you wanted to move it to your Linux server, but if this is for production I strongly recommend keeping it on Microsoft. I can go on and on about the advantages of MS over MY, but only if you want to hear them. Of course, if you're moving for another reason or it's just something to tinker with, then carry on :)

[sun818]

Most advanced text editors let you save in Unix text mode (LF). I know MetaPad, a tiny text editor, will do this. You can also use General Search and Replace (GSAR) [gnuwin32.sourceforge.net].

I'd try installing a mySQL ODBC driver on the Windows server and connecting directly to it from your Linux box.

[coopster]

Since I missed it last time, welcome to WebmasterWorld, astronaut!

Another issue I run into often when migrating data like this is escaped (or non-escaped) characters in the data. Also, if you are working with a large data set, try chunking it into manageable rows to determine where the data issue lies. If you can't get past record number 1 or 2, you are more than likely dealing with a LF/CRLF issue.

---

OT:

Common misconceptions/misunderstandings regarding MySQL:

stored procedures [dev.mysql.com]
linked servers, clustering capabilities [mysql.com]
better performance [dev.mysql.com] for large (100GB - 2 TB) databases, etc. [dev.mysql.com]

[timster]

Is there a way to format the text file to replace the CRLF with LF style line endings?

You could download jEdit to save your files with the desired line ending. Being a Mac guy (gasp!) I don't know much about Windows text editors.

If you can just re-export the text files, Enterprise manager lets you select the line endings (if memory serves, it's listed under "Record Separator").

Maybe easiest of all would be to download a MySQL front end e.g., Navicat, which makes importing files with CRLF simple. (Though, in the long run, you might be happier getting Enterprise Manager connected to MySQL.)

[astronaut]

After further research I found some really good articles on importing data if anybody is still having trouble with this issue.

[dev.mysql.com...]

and

[modwest.com...]

Now I need to build a custom search to use all this data (that will be a different thread though)

:-)