MySQL vs MS SQL Server 1

[axslinger]

Is MySQL compatible with M$ SQL server? I am thinking of converting an Access 2000 data base to SQL and want to run an SQL Server on my Caldera 2.3 server. I'm not a data base expert and was also wondering if it is a complicated process to get a Linux SQL server running. Any input is appreciated.

Brian

 

[gerald]

MS SQL and MySQL are not compatible.

The query language is pretty much similar, but a database created in MS SQL will not work on a MySQL server.

However you can probably find some way to convert from one to the other.

Setting up a MySQL server is fairly simple. You can download the source from

http://www.mysql.com

There is an excellent book by O'Reilly Press on MySQL (dont have the book in front of me now so I cant be more specific right now) that you should definitely get if you are considering running a MySQL server.


Regards,
Gerald

 

[rycamor]

No database created on one database program will just 'work' on the other. Asking whether two SQL database systems are compatible is kind of a mistaken question to ask. How would one expect two SQL's to be compatible?

1. By being able to transfer data from one to another?
2. By each being able to do the same or similar operations on the data?
3. By being able to simply copy the raw data files on your disk over to the other system and run them exactly the same?

For the first two questions, you're (mostly) in luck. For the third question, no two database systems ever do that.

To get data from one database to another, you just have to export and import it, and there are several ways to do that. Access is more different from MSSQL than MSSQL is from MySQL. Mainly transfers from one SQL database to another can be accomplished by outputting text data in a CSV (comma-separated) format. This is only for transferring data, not table struncture, queries, etc... To transfer structure, you need to dump the SQL commands used to create tables, edit them as needed to function on the other system, and then run them on the other system. There is a lot to learn in this area, and you have to know the differences in features supported by the databases, but a lot of it can be word-for-word copying.

This page,

http://www.cynergi.net/exportsql/,

has a script that create an Access module that helps export a database from Access to MySQL.

http://www.mysql.com/downloads/contrib.html,

on the mysql main site, has a list of other contributed software that halps connect MySQL to other databases. One interesting method is to use ODBC, so Access can be sort of a 'front end' to MySQL.

Please feel free to ask any further questions. I have set up and configured Linux/FreeBSD and MySQL so many times I could do it in my sleep.

 

[axslinger]

Rycamor,

Thanks for the detailed response. As I mentioned, I don't really have any experience with data bases, other than creating basic ones in Access. I guess when I asked about compatibility, it was sort of out of ignorance because I don't know how data bases work. Basically, I have an Access data base. It's not big, but since I have a Linux server sitting here that isn't under alot of load, I thought I would expand my horizons and attempt to set up an SQL server. I know there are programs out there "compatible" with Word and Excel. Without importing. They can directly open those files. And that's what I was referring to with MS SQL and MySQL being compatible. But to sum up what I would like to do, I want to get my current database into an SQL format and utilize the Linux server for that. And since Access can connect to MS SQL Server, I was hoping to use Access as the client end. Does this sound like something that could happen? If not, what client applications are available for the Windows platform that can access a MySQL database?

Thanks,
Brian

 

[rycamor]

As I mentioned above, once you create an ODBC connection to MySQL, Access can be a front-end to MySQL, the same way you would use it as a front-end to MSSQL: just create a DSN on your Linux box, connect to it with Access on your Windows machine, and you're in. (It will require some serious reading of the documentation) Actually, once you set up ODBC for MySQL, any ODBC-aware Windows application can connect to it, from any part of your network, including Excel or applications written in Vsual Basic.

I would also recommend, as long as you are putting MySQL on your Linux box, you learn how to set up Apache and PHP to interact with MySQL. Then you can have a web-based connection to MySQL. phpMyAdmin, at

http://www.phpwizard.net/projects/phpMyAdmin/,

is a very easy web-based administration kit for MySQL.