How to Ignore T-SQL Print Statements when Using PHP SQLSRV_QUERYs

We are in the process of converting some PHP 5.3 internal API code to PHP 7.2. Part of the conversion process is switching from using mssql_* DB APIs to using sqlsrv_* DB API. One thing we have found is that the old MSSQL_EXECUTE statement ignores any T-SQL Print statements we have in our stored procedures but the new SQLSRV_QUERY statement does not.

For example, I have the following sample stored procedure:

create procedure spTestPrintStmt
  @var1 varchar(50),
  @var2 varchar(50),
  @var3 varchar(50),
  @rtn1 varchar(50) out,
  @rtn2 varchar(50) out
as
begin

  print 'Starting spTestPrintStmt...';

  print 'Vars: ' + @var1 + ',' + @var2 + ',' + @var3;

  select @rtn1 = @var1, @rtn2 = @var2 + @var3;

  print 'Returned: ' + @rtn1 + ',' + @rtn2;

  return 0;
end;
go

I use the following PHP page to test the above stored procedure:

<html>
  <head>
    <title>Stored Proc Param Test</title>
  </head>
  <body>
    <p>
<?php
  $result = 0;
  $var1 = 'abc';
  $var2 = 'def';
  $var3 = 'ghi';
  $rtn1 = '';
  $rtn2 = '';

  $connectionInfo = array("Database"=>'database', "UID"=>'username', "PWD"=>'password');
  $conn = sqlsrv_connect("dbserver", $connectionInfo);
  if( $conn === false ) {
    echo "Couldn't connect to SQL Server on dbserver.<br />";
    die( print_r( sqlsrv_errors(), true));
  } else {
    echo "Connected!";
  }
  echo '<br/>'; 


  $tsql = "{ ? = call spTestPrintStmt(?,?,?,?,?) }";

  $params = array(
    array($result, SQLSRV_PARAM_INOUT),
    array($var1, SQLSRV_PARAM_IN),
    array($var2, SQLSRV_PARAM_IN),
    array($var3, SQLSRV_PARAM_IN),
    array($rtn1, SQLSRV_PARAM_INOUT),
    array($rtn2, SQLSRV_PARAM_INOUT)
  );

  $stmt = sqlsrv_query($conn, $tsql, $params);
  if ($stmt === false) {
    echo "Error in executing stored proc.";  
    die( print_r( sqlsrv_errors(), true));  
    echo "<br/>";
  }
  sqlsrv_next_result($stmt);  
  echo "Result: " . $result . "<br/>";
  echo "Rtn1: " . $rtn1 . "<br/>";
  echo "Rtn2: " . $rtn2 . "<br/>";

  sqlsrv_free_stmt($stmt);
  sqlsrv_close($conn);
?>
    </p>
  </body>
 </html>

When I run the above, I get the following results:

Connected!
Error in executing stored proc.Array ( [0] => Array ( [0] => 01000 [SQLSTATE] => 01000 [1] => 0 [code] => 0 [2] => [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Starting spTestPrintStmt... [message] => [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Starting spTestPrintStmt... ) )

Is there any way around this? We use print statements extensively in our T-SQL code to aid with debugging.

Solution

If you have a look at the PHP docs for sqlsrv_errors(), it states:

By default, warnings generated on a call to any SQLSRV function are treated as errors. This means that if a warning occurs on a call to a SQLSRV function, the function returns FALSE. However, warnings that correspond to SQLSTATE values 01000, 01001, 01003, and 01S02 are never treated as errors. For information about changing this behavior, see sqlsrv_configure() and the WarningsReturnAsErrors setting.

Therefore, your print statements (which seem to be treated as warnings) are being returned as errors. If you go to the linked sqlsrv_configure() function docs, you'll see an option.

I added the following line just before the connection is made:

sqlsrv_configure("WarningsReturnAsErrors",0);

Now when I run your code, I no longer get an error. I get the following:

Connected!
Result: 0
Rtn1: 
Rtn2:

Hope this helps.