[: GaB :]

If you are running into troubles, because MSSQL doesn't order your query result in the way you expect, remember, that there are strange collation in real life, that SQL implementations can't or won't take into account.

For example in Hungarian 'c' and 'cs' are two separate consonants, but it is also possible, that the consonants 'c' and 's' are next to each other, but does not stand for a 'cs'.
This causes the following problem: CSS < CZAKÓ < CSAK.
(note: CSS isn't a word in the Hungarian language, altough Czakó is a name, csak means but.)

As you can see it is not possible to sort Hungarian words correctly without knowing the meaning of the sorted words. This makes the creation of a perfect sorting algorithm for the Hungarian collation not involving an AI (that knows Hungarian) impossible. Although some collation needs to be applied...

For MySQL see thins topics:
http://bugs.mysql.com/bug.php?id=12519&tha...
http://lists.mysql.com/mysql/204718

MSSQL tried even harder, and created a very confusing one.

If you experience problems in a program, that depends on the sorted input of several SQL queries, try to order the results in a neutral collation.
An example for MSSQL would look like this:

SELECT name
FROM employees
ORDER BY name COLLATE Latin1_General_BIN asc

It is worth mentioning that MSSQL uses double-qoutes to escape quotes (as required by the ANSI SQL standard) over against the majority of SQL implementations that use the backslash as the escape symbol. For example in php you can escape strings or arrays of strings with:

function addslashes_mssql($str){
    if (is_array($str)) {
        foreach($str AS $id => $value) {
            $str[$id] = addslashes_mssql($value);
        }
    } else {
        $str = str_replace("'", "''", $str);
    }

    return $str;
}

function stripslashes_mssql($str){
    if (is_array($str)) {
        foreach($str AS $id => $value) {
            $str[$id] = stripslashes_mssql($value);
        }
    } else {
        $str = str_replace("''", "'", $str);
    }

    return $str;
}

See http://www.php.net/manual/en/function.adds... for more.

If you need to have access to an MSSQL server from UNIX machines, ODBC could be the answer. A couple of programming environments (Perl, PHP) already have a built-in API for that, but even then with ODBC you don't have to change your code when the database migrates. The following configuration makes a MySQL and a MSSQL server available to clients with the DSNs (Data Source Names) mysql and mssql.

First install FreeTDS. This is a library to access MSSQL and Sybase databases. Also install libmyodbc.
Then edit /etc/freetds/freetds.conf:

[mssql_tds]
host = mssql.mydomain.tld
port = 1433
tds version = 8.0

At the end you will be able to test your settings with:

$ isql -v MSSQL username password
SQL>use DATABASE
[ISQL] Error: Could not SQLPrepare

If you experience the above error, try to decrease tds version in freetds.conf.

Get and install unixodbc. This is the framework that connects the ODBC API (which is accessible for the programmer) to the specific databases that are configured to be accessible via appropriate database drivers (like libmyodbc and FreeTDS).

Edit /etc/odbc.ini to set up the data sources:

[mysql]
Description    = mysql database
Driver             = myodbc
Trace              = No
TraceFile       = odbc.mysql.log
Server            = mysql.mydomain.tld
Port                 = 3306
Socket            =
Database       =

[mssql]
Description     = mssql database
Driver              = FreeTDS
Servername  = mssql_tds
Database        =

Now edit /etc/odbcinst.ini to set up the drivers:

[myodbc]
Description  = MySQL driver
Driver           = /usr/lib/odbc/libmyodbc.so
Setup            = /usr/lib/odbc/libodbcmyS.so

[FreeTDS]
Description  = MSSQL driver
Driver           = /usr/lib/odbc/libtdsodbc.so
Setup            = /usr/lib/odbc/libtdsS.so

This should be enough to have a working ODBC setup.