I am upgrading my web server and have run into an issue trying to connect to sql databases. Some of my queries are giving me "sqlsrv_fetch_array() expects parameter 1 to be resource, boolean given", but not all of them. The following is one that is:
$result = sqlsrv_query($db3,"SELECT * ,convert(varchar, [dbo].[Order].[CreateDate], 0) AS CreateDate,convert(varchar, [dbo].[Order].[LastModifiedDate], 0) AS LastModifiedDate
WHERE [order].[VisitID] = [Visit].[VisitID]
AND [Order].[ReportID] = [Report].[ReportID]
AND [Visit].[PatientID] = [Patient].[PatientID]
AND [Patient].[PersonalInfoID] = [PersonalInfo].[PersonalInfoID]
AND ([order].[OrderStatusID] = '1' OR [order].[OrderStatusID] = '4' )
AND [order].[ReportID] is not NULL
AND [report].[TransferStatusID] != '3'
I did a var dump on $db3 and it is not returning as a boolean so the issue is in the query. Also I use the same connect string for other sql queries to that same db and it works fine, which also leads me to believe the problem is in the query. the new server is running win server 2008, sp2 with iis7 and the 2012 sql native client, php 5.4.15. This exact query is currently working fine on the old server running winserver 2003, iis6 and php 5.3.13. Any help would be great.
Its not the query, the problem lies within your PHP code as the error message states. If it was a problem with the query or the DB the error message would reflect that. Somewhere $db3 is being assigned a boolean value.
I managed to find the issue using "die( print_r( sqlsrv_errors(), true))". It gave a much more detailed out put of the problem. It was the query. For the last logic statement "[report].[TransferStatusID] != '3'" I never put the db [dbo].[Report] in the FROM statement. The interesting part is as I said this query works (though not correctly as the last logic statement is always false) on the old web server. I even went back and checked and the old server (which is still in production) did not have the DB defined in the FROM part of the query either. It apparently just set the last logic statement as false instead of erroring out and halting the script like it does on the new server with the newer version of PHP and sql native client. I also had similar issues with a couple of mysql queries.