Sunday, February 27, 2011

Resolving your PHP, MSSQL, msdblib, FreeTDS headache on Linux

The last thing I searched for was "Adaptive Server connection failed" because I kept getting that without ever telling it that I was trying to make an "Adaptive Server" connection.

James K. Lowden explains this in http://lists.ibiblio.org/pipermail/freetds/2008q2/023506.html:  When FreeTDS is unable to find whatever you entered as the "$Servername" parameter to mssql_connect in the freetds.conf file (which I found in /usr/local/freetds/etc - as in "--with-mssql=/usr/local/freetds" from my PHP configuration string), it falls back to it's default settings, which happen to include TDS Version 5.0.  Apparently, that TDS version isn't supported by SQL Server 2005 (unless it was something else that caused my connection to consistently report "unable to connect").

Since I had already added a [TDS] section to my freetds.conf file, my Servername is now "TDS".  Here's the code that gives me a workable connection:

$con = mssql_connect('TDS','user','password')
    or die('Could not connect to the server!');
echo "con: "; print_r($con);
// Select a database:
mssql_select_db('Database')
    or die('Could not select a database.');

// Example query: (TOP 10 equal LIMIT 0,10 in MySQL)
$SQL = ...

I hope this helps someone out there.  It took me about 4 hours to figure it out.