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 environment: 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.