How to connect to MSSQL 2000 from PHP 5.3 and up

sql-server-2000 upgrade compatibility php-5.3 sqlsrv
Lorenz Meyer picture Lorenz Meyer · Nov 5, 2013 · Viewed 8.6k times · Source

I have a legacy business application built on MS SQL Server 2000. I have some webbased utilities that access this database using PHP 5.2 with mssql extension.

I need to reinstall the web server, and I looked forward to upgrade to PHP 5.4. Unfortunately, the mssql extension is not supported on PHP 5.3 and newer. There is the sqlsrv extension available form Microsoft, but the description says that it is only supported for accessing SQL server 2005 and up.

How can I connect to my SQL Server 2000 from PHP 5.4 ? Did anyone already solve this issue ?

Answer

Lorenz Meyer picture Lorenz Meyer · Nov 5, 2013

This is a really complicated issue. Here are the details of (in)compatibilities so someone else might spend less time searching and trying.

PHP extension sqlsrv from Microsoft

sqlsrv exists in two (+ an unofficial) versions, they are only compatible with 32-bit PHP. There is currently no version for 64-bit PHP.

  • sqlsrv version 2.0 is compatible with PHP 5.2.4 to 5.3.x and SQL Native client 2008 R2 to connect to Microsoft SQL Server 2000, 2005, or 2008.
  • sqlsrv version 3.0 is compatible with PHP 5.3.0 to 5.4.x and SQL Native client 2012 to connect to Microsoft SQL Server 2005, 2008, 2008 R2, and SQL Server 2012.
  • no official version currently supports PHP 5.5
  • There is an unofficial version of SQLSRV 3.0 on Rob's Area that does not require SQL Native client 2012 but should run with previous SQL Native clients. I didn't yet try this one.

ODBC extension of PHP

An other way to access SQL Server 2000 with PHP 5.4 is through the odbc extension. It is possible to connect using three ODBC drivers for SQL Server 2000:

  • SQL Server ODBC client version 6.00 that comes preinstalled with Windows 2008 R2
  • SQL Server Native Client 9.0 (SQL Server 2005 feature pack)
  • SQL Server Native Client 10.0 (SQL Server 2008 R2 feature pack)

The code for the connection for each of those ODBC Drivers:

$connection_string = 'DRIVER={SQL Server};SERVER=mbsql;DATABASE=vg1';
$connection_string = 'DRIVER={SQL Native Client};SERVER=mbsql;DATABASE=vg1';
$connection_string = 'DRIVER={SQL Server Native Client 10.0};SERVER=mbsql;DATABASE=vg1';
$connection = odbc_connect( $connection_string, $user, $pass );

These connections work with PHP 32 bit and 64 bit. I din't yet test which one is the best.

Related questions

Add a column with a default value to an existing table in SQL Server

How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?

Read More
Check if table exists in SQL Server

I would like this to be the ultimate discussion on how to check if a table exists in SQL Server 2000/2005 using SQL Statements. When you Google for the answer, you get so many different answers. Is there an official/backward …

Read More
How to Join to first row

I'll use a concrete, but hypothetical, example. Each Order normally has only one line item: Orders: OrderGUID OrderNumber ========= ============ {FFB2...} STL-7442-1 {3EC6...} MPT-9931-8A LineItems: LineItemGUID Order ID Quantity Description ============ ======== ======== ================================= {098FBE3...} 1 7 prefabulated amulite {1609B09...} 2 32 spurving bearing But occasionally there …

Read More