Afternoon all,
Bit of a shot in the dark really, anyone know PHP? I have a stored procedure that runs fine from the management studio but I'm having real problems running it from PHP.
Management Studio:
EXEC dbo.BookingProctest
PHP:
$outputarray = array();
$serverName = $Server;
$connectionInfo = array( "Database"=>$Database, "UID"=>$Username, "PWD"=>$Password);
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn ) {
$query = "EXEC dbo.BookingProctest";
$stmt = sqlsrv_query($conn, $query);
$result = sqlsrv_execute($stmt);
while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC )) {
array_push($outputarray, $row);
}
sqlsrv_close($conn);
}
else{
die( print_r( sqlsrv_errors(), true));
}
return $outputarray;
And the error i keep getting is:
'sqlsrv_execute() expects parameter 1 to be resource, boolean given'
Anyb ideas....?
Many thanks
Dave
is your back end database, microsoft sql server?
Yes mate, SQL Sever 2008 R2...
oh yes I remember that QuattroDave 
Does your exec BookingProctest take a required parameter?
No parameters required for this stored procedure.
I think sqlsrv_execute() should be used in conjunction with sqlsrv_prepare() (to prepare the statement, natch!), and sqlsrv_query() should be used on its own.
So I think you should be able to replace
$stmt = sqlsrv_query($conn, $query);
$result = sqlsrv_execute($stmt);
while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC )) {
array_push($outputarray, $row);
}
with
$stmt = sqlsrv_query($conn, $query);
/* Need error handler if $stmt === false */
while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC )) {
array_push($outputarray, $row);
}
Hi Kristen,
I've made the suggested changes but still got the same error message....
Thanks
Dave
OK, I've dumbed this right down hoping to find the problem.
Created a new stored procedure.
USE [TestDB]
GO
/****** Object: StoredProcedure [dbo].[ProcTest] Script Date: 06/13/2017 17:41:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ProcTest]
AS
BEGIN
SELECT TOP 10 FName, LName, Address1, Postcode FROM Employees
END
In SQL Management Studio
EXEC ProcTest
Works Perfectly.
In PHP
$outputarray = array();
$namearray = array();
$serverName = $Server;
$connectionInfo = array( "Database"=>$Database, "UID"=>$Username, "PWD"=>$Password);
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn ) {
$query = "SELECT TOP 10 FName, LName, Address1, Postcode FROM Employees";
$stmt = sqlsrv_query($conn, $query);
$result = sqlsrv_execute($stmt);
while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC )) {
array_push($outputarray, $row);
}
sqlsrv_close($conn);
}
else{
die( print_r( sqlsrv_errors(), true));
}
return $outputarray;
Works Perfectly. However:
$outputarray = array();
$namearray = array();
$serverName = $Server;
$connectionInfo = array( "Database"=>$Database, "UID"=>$Username, "PWD"=>$Password);
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn ) {
$query = "EXEC ProcTest";
$stmt = sqlsrv_query($conn, $query);
$result = sqlsrv_execute($stmt);
while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC )) {
array_push($outputarray, $row);
}
sqlsrv_close($conn);
}
else{
die( print_r( sqlsrv_errors(), true));
}
return $outputarray;
Fails with the error 'sqlsrv_fetch_array() expects parameter 1 to be resource, boolean given'
I really don't understand what is happening....
could it be that there is a different syntax for stored procedures vs simple queries?
I think you need to do the fetch on the $result and not on the $stmt?
sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC )
take a look at this
Maybe try the Prepare route then?
$stmt = sqlsrv_prepare($conn, $sql);
sqlsrv_execute($stmt))
while($res = sqlsrv_next_result($stmt))
{
...
Morning guys,
made a bit of progress, I suddenly realised I hadn't given my SQL user account permissions to execute the stored procedure.
GRANT EXEC ON dbo.ProcTest TO dave GRANT EXEC ON dbo.BookingProcTest TO dave GRANT EXEC ON dbo.BookingProc TO dave
So, the stored procedure ProcTest works perfectly now, however the stored procedure BookingProcTest doesn't give an error but output any data either.....
EDIT: BookingProcTest does output data in SQL Management Studio...
EDIT: Ahhhhhhh! Making progress now..... The BookingProcTest stored procedure does a couple of operations. Weirdly PHP will only output data from the first operation even tho all 3 operations are processed. Anyone think of a way around this??
If you have more than one ResultSet (possibly including any "empty" resultsets that maybe don't "show" in SSMS, quite possibly also if your SProc does NOT explicitly SET NOCOUNT ON) you'll have to loop around the resultsets
I don't know anything about PHP specifically, but in my snippet above (from a google search) its the sqlsrv_next_result bit
Ha! You genius, yes SET NOCOUNT ON has sorted it out SQL & PHP now output exactly as I want them to.... 
Glad you've got it sorted out.
Suggest you also check Multiple Resultsets (in your PHP code)., that's going to catch you out sooner or later otherwise ...