PHP + SQL Server + SQL Server Driver = sqlsrv_num_rows serious problem

I have serious problem in my application.

When I make 'CTE' select in my SQL Server database and then I use sqlsrv_num_rows to count rows amount in result object I got strange result: -1.

This value is not even in manual (link: http://msdn.microsoft.com/en-us/library/ee376931(SQL.90).aspx).

Sample code:

W have select 'CTE - Common Table Expression' for instance:

$sql = ";WITH unsorted_cte AS
(
  SELECT
    *
  FROM
    xxx
  WHERE
    yyy
)

SELECT
  u_cte.*
FROM
  [unsorted_cte] u_cte
ORDER BY
    u_cte.[zzzz] ASC";

In sql console the @@rowcount have proper value, but when we use this select in php script it returns -1.

sample:

// make query with proper coursor

$result = sqlsrv_query($link, $sql, array(), array( "Scrollable" => SQLSRV_CURSOR_KEYSET));

// var dump shows us proper resource

var_dump($result):
resource(3) of type (SQL Server Statement)

// sqlsrv_num_rows show us wrong rows count

var_dump(sqlsrv_num_rows($result));
int(-1)

// sqlsrv_errors are empty

var_dump(sqlsrv_errors());
NULL

I tried to fix it in whole coursors http://msdn.microsoft.com/en-us/library/ee376927(SQL.90).aspx and Selecting Rows parameters and I cant fix it.

btw. in my case the only Selecting Rows that works is SQLSRV_CURSOR_FORWARD.

My environ开发者_如何学JAVAment: PHP 5.3 MSSQL 2005 Windows Server SQL Server Driver for PHP 1.1 ( msdn dot microsoft dot com/en-us/library/ee229551(SQL.10).aspx) Database encoding: unicode

I hope, sb know solution of this problem.

The problem described here is one that exposes a bug in the ODBC driver upon which the sqlsrv driver is built. The ODBC driver does some level of parsing of a SQL query that starts with WITH, which it then handles incorrectly. The workaround is to execute the query as a stored procedure, which changes the code path executed by the ODBC driver.

The SQL Server Native Client team (SNAC is Microsoft's implementation of ODBC) is aware of this bug. I'll make sure they are aware of the impact this bug is causing.

Thanks.

Brian Swan, Microsoft