php against sql server 2005 is timing out

Question

I'm running a php script from the command line against SQL server 2005 using the MS driver for PHP and getting time outs. The query takes about 2 minutes from SQL Server Management Studio and returns > 300,000 rows.

There are multiple queries in the script and for each one I do a sqlsrv_connect(), execute the query and then sqlsrv_free_stmt() and sqlsrv_close()

Output from sqlsrv_errors():

Array
(
    [0] => Array
        (
            [0] => 08S01
            [SQLSTATE] => 08S01
            [1] => 258
            [code] => 258
            [2] => [Microsoft][SQL Native Client]Shared Memory Provider: Timeout error [258]. 
            [message] => [Microsoft][SQL Native Client]Shared Memory Provider: Timeout error [258]. 
        )
    [1] => Array
        (
            [0] => 08S01
            [SQLSTATE] => 08S01
            [1] => 258
            [code] => 258
            [2] => [Microsoft][SQL Native Client]Communication link failure
            [message] => [Microsoft][SQL Native Client]Communication link failure
        )
    [2] => Array
        (
            [0] => 08S01
            [SQLSTATE] => 08S01
            [1] => 0
            [code] => 0
            [2] => [Microsoft][SQL Native Client]Communication link failure
            [message] => [Microsoft][SQL Native Client]Communication link failure
        )
)

Answer 1

Couple of things i can think of.

On the database server have you set up MS SQL to allow remote connections? If not you will need to do this.

Second are you using Named Pipes or TCP/IP ?

Answer 2

I never had much luck getting SQL Server 2005 and PHP to talk using the Microsoft driver, but it worked fine with PDO's ODBC driver. It might be worth trying.

Simple connection:

$host     = '1.2.3.4';
$port     = '1433';
$database = 'MyDatabase';
$user     = 'MyDatabaseUser';
$password = 'MyDatabasePassword';

$dsn = "odbc:DRIVER={SQL Server};SERVER=$server,$port;DATABASE=$database";

try {
  // connect
  $conn = new PDO($dsn,$user,$password);
} catch (PDOException $e) {
  // fancy error handling
}

Answer 3

does it time out if you return fewer rows? 300,000 is quite a lot