Unable to connect sqlsrv php call stored procedure

PHP
1

how to achieve php + sqlsrv + SP.


$query = "{CALL call_user_name}";

if ( ($res = sqlsrv_query( CONNSTR, $query)) )
{
   do
   {
      if ( sqlsrv_num_fields($res) ) // one way to check if results returned.

      {
         while( ($row = sqlsrv_fetch_array( $res, $type)) )
         {
            $data[] = $row;
         }
      }else{echo "sqlsrv_num_fields not working";}
   } while ( sqlsrv_next_result($res) ) ;
   sqlsrv_free_stmt($res); // not essential, but good form if your script does lots of other stuff.
}else{
       echo "SQL SRV QUERY NOT RAN ";die( print_r( sqlsrv_errors(), true));
}


Array ( [0] => Array ( [0] => IMSSP [SQLSTATE] => IMSSP [1] => -28

=> -28 [2] => The active result for the query contains no fields. [message] => The active result for the query contains no fields. ) )

2

From a google search

=>

Hi All,

I found a solution. The problem is when the stored procedure does not use ‘SET NOCOUNT ON;’. All the operations in the stored procedure have results and maybe, the first one not have fields and it print something like ‘(1 row(s) affected)’.
For example, I wrote a stored procedure with many insert commands and at the end, it printed OK or an error code. From my model, I ran the sql statement using queryAll method. For each insert mssql return result saying ‘(1 row(s) affected)’ and no fields. I used ‘SET NOCOUNT ON;’ in the stored procedure and the only one result was OK or the error code.

Thank you all,

Diego

Not tested but you may try.

3

As per above suggestion, i just added this " SET NOCOUNT ON; " before the SELECT QUERY command. but still not working.

4

also looks that thread specking with MSSQL function, here i am looking solution using sqlsrv. please share your exp.

5

problem fixed . i dont added any command in my stored procedure

php sqlsrv stored procedure

$serverName = “DB-HOST”; //serverName\instanceName
$connectionInfo = array( “Database”=>“DB-NAME”, “UID”=>“DB-USERNAME”, “PWD”=>“DB-PASSWORD”);
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn ) {
echo “Connection established.<br />”;
}else{
echo “Connection could not be established.<br />”;
die( print_r( sqlsrv_errors(), true));
}

//Here my SP required one argument. so i passed one argument. this @param and stored procedure argument name should be same(case sensitive)
$sql = " { call myspworks ( @param=? ) } ";
$param1 = ‘TABLE02-01’;
$params = array(array(&$param1, SQLSRV_PARAM_IN));
$stmt = sqlsrv_prepare($conn,$sql,$params);

if ($stmt===false) {
// handle error
print_r(sqlsrv_errors,true);
}else{
if (sqlsrv_execute($stmt)===false) {
// handle error. This is where the error happens
print_r(sqlsrv_errors,true);
}
else
{
$resultsetarr = array();
while($row = sqlsrv_fetch_array($stmt)){
$resultsetarr = $row;
}
print_r($resultArray);
// success! It never gets here, though.
}
}