Difference between PHP SQL Server Driver and SQLCMD when running queries

Question

Hello all,

Why is that the SQL Server PHP Driver has problms with long running queries?

Every time I have a query that takes a while to run, I get the following errors from sqlsrv_errors() in the below order:

Shared Memory failure, Communication Link Failure, Timeout failure

But if I try the same query with SQLCMD.exe it comes back fine. Does the PHP SQL Server Driver have somewhere that a no timeout can be set?

Whats the difference between running queries via SQLCMD and PHP Driver?

Thanks all for any help

Typical usage of the PHP Driver to run a query.

function already_exists(){

    $model_name = trim($_GET['name']);

    include('../includes/db-connect.php');

    $connectionInfo = array('Database' => $monitor_name);

    $conn = sqlsrv_connect($serverName, $connectionInfo);

    $tsql = "SELECT model_name FROM slr WHERE model_name = '".$model_name."'";

    $queryResult = sqlsrv_query($conn, $tsql);

    if($queryResult != false){

        $rows = sqlsrv_has_rows($queryResult);

        if ($rows === true){

             return true;

        }else{

            return false;
        }

    }else{

           return false;         
    }

    sqlsrv_close($conn);

}

Answer 1

SQL Server 2005 limits the maximum number of TDS packets to 65,536 per connection (limit that was removed in SQL Server 2008). As the default PacketSize for the SQL Server Native Client (ODBC layer) is 4K, the PHP driver has a de-facto transfer limit of 256MB per connection. When attempting to transfer more than 65,536 packets, the connection is reset at TDS protocol level. Therefore, you should make sure that the BULK INSERT is not going to push through more than 256 MB of data; otherwise the only alternative is to migrate your application to SQL Server 2008.

From MSDN Forums

http://social.msdn.microsoft.com/Forums/en-US/sqldriverforphp/thread/4a8d822f-83b5-4eac-a38c-6c963b386343

Answer 2

SQLCMD has no query execution timeout by default. PHP does. I assume you're using mssql_query? If so, the default timeout for queries through this API is 60 seconds. You can override it by modifying the configuration property mssql.timeout.

See more on the configuration of the MSSQL driver in the PHP manual.

If you're not using mssql_query, can you give more details on exactly how you're querying SQL Server?

Edit [based on comment]
Are you using sqlsrv_query then? Looking at the documentation this should wait indefinately, however you can override it. How long is it waiting before it seems to timeout? You might want to time it and see if it's consistent. If not, can you provide a code snippet (edit your question) to show how you're using the driver.

If MSDTC is getting involved (and I don't know how you can ascertain this), then there's a 60-second timeout on that by default. This is configured in the Component Services administration tool and lives in a different place dependent on version of Windows.

Answer 3

PHP itself has several different timeout settings that you can control via php.ini. The one that often causes problems like you're seeing is max_execution_time (see also set_time_limit()). If these limits are exceeded, php will simply kill the process without regard for ongoing activities (like a running db query).

There is also a setting, memory_limit, that does as its name suggests. If the memory limit is exceeded, php just kills the process without warning.

good luck.