Category Archives: Databases

Import a MySQL 5 database into SQL Server 2005

*Update* 8-28-2009

After trying this out, I’ve found that this does not work… at all. MSSQL compatibility mode must of been built back before 2000. I will continue searching the internet for a solution, but my feeling is that I will have manually create the tables and then add the insert statements into the database.

This is how I imported a database using MySQL 5 running on Ubuntu 8 LTS to MS SQL Server 2005.

First export the database by typing in a Ubuntu shell:

‘mysqldump -p –compatible=mssql databasename > mssql.sql’

The ‘-p’ switch prompts for a password, while the ‘mssql.sql’ file is the exported database file. If you need to specify a username, add ‘-u username’ as the first parameter or before the -p switch.

Transfer the file over to the Windows machine and  create a database for the import. Make sure you have permissions to transfer data via Windows Authentication. If you don’t have permissions, you can specify credentials using the -U and -P switches.

Run the following command line:

“C:\Program Files\Microsoft SQL Server\90\Tools\Binn\sqlcmd.exe” -S localhost -d databasename -i “mssql.sql”

Before running, verify that the ‘sqlcmd.exe’ file path is correct. The -S switch is the host name, server name, or location. You may have to use the machine name depending on the name of your instance. The -d switch specifies the database to import the data.

PostgreSQL and its History

As I’ve played around with PostgreSQL, I’ve found it to be quite a capable database. I’ve begun to wonder how common PostgreSQL is used in production systems. After a little bit of research, apparently quite a few organizations use this database.

PostgreSQL was originally developed at the University of California at Berkley by Profesor M. Stonebraker in 1986. Postgres, as the database was originally called, was later commercialized by the company Informix and renamed Illustra. Informix was later purchased by IBM for 1 billion dollars. In 1996, Postgres95 – the open source version of Postgres – was created. This was later renamed to PostgreSQL 6.0.

After over a decade of development, I think its safe to say that this database is mature and commands a large install base due to corporate support by established companies (Sun, Fujitsu, Afilias, etc.) and the large development community behind it. I just find it weird that I don’t see a whole lot of PostgreSQL fanboys running around telling everyone and their mothers about this software. This is how obscure OSS projects gets well known – kind of like how MySQL and Linux got popular.

If you would like more information on the topic, please check the postgresql.org’s history page. I’ve also put up an article on PostgreSQL pertaining to domains.

New Articles

Database vs Database and the Linux PATH variable

Last night I was doing some research on PostgreSQL and got as far as configuring the server, getting a Schema up, and creating my first functions. In terms of capability between SQL Server 2005 and PostgreSQL, so far PostgreSQL seems to offer a comparable alternative, at least with the requirements for our system. A lot of features that were recently introduced into SQL Server 2005 such as custom data types, language support outside of T-SQL, and support for new SQL syntax has been around in Postgre for awhile, but that doesn’t mean PostgreSQL is better. As usual, you have to look at your requirements for your system before deciding which database servers out there are a better match for your project.

For our project, the two crucial requirements that counted out SQL Server 2005 were the ability to run on Linux and low licensing fees. The licensing costs of SQL Server 2005 does not scale well. Even with volume license deals, the licensing costs still encourage companies to scale up their hardware to save on licensing fees. Part of our strategy involves scaling out over cheap redundant boxes, its the exact same strategy that most successful tech startups have chosen because its more economical in the beginning of the system’s or company’s life.

Last night while I was configuring Postgre on my local Ubuntu machine, I ran into a small problem regarding editing the ‘system’ PATH in the bash shell. The PATH variable in Bash is similar to the PATH entry in the Window’s System Variables. This variable allows a user to execute files in any location located in the directories referenced by the PATH variable. Like the Windows counter part, there is a global file that contains PATH entries for all users and a local file for the current user. The global file is located at ‘/etc/environment’. You may have to login as ‘su’ in order to access the file. The local file is located at ‘~/.bashrc’, which is actually in the user’s home directory.

To add an entry to the PATH variable for the local user, just add the following lines

First – Type: gedit ~/.bashrc

PATH=$PATH:/path_to_program_is/bin
export PATH

To add an entry to the PATH variable for the environment, just add your entry with a colon and the new entry to the end of the PATH definition.

First – Type:  sudo gedit  /etc/environment

PATH="XXXXX:XXXXX/XXXX:XXXX:XXX/XXXX/XXXX:XXXXX:/path_to_program_is/bin"

For more information, consult this thread in the ubuntuforums.