CODEWITHSUNDEEP
phpsql-serverprintingsqlsrv
Dewanand Manandhar
Dewanand Manandhar

Reputation: 334

SQLSRV PRINT function issue

I have recently switch to SQLSRV from ODBC to connect MS SQL 2008 from PHP. There is a strange issue with PRINT function in stored procedure. IF PRINT function display more than approximately 700 characters, "HTTP Error 500.0 - Internal Server Error" message is return.

Following are list of tools I am using:

  1. PHP 5.3.6
  2. MS SQL Server 2008
  3. [PHP_MSSQL]extension=php_sqlsrv_53_nts.dll
  4. IE8 (this issue is with other browsers as well like firefox, chrome)
  5. web server IIS

Following is the code PHP CODE:

<?php

$link = sqlsrv_connect( 'dbname\instance2008', array( "UID"=>'UID',"PWD"=>'PWD',       "Database"=>'Database_name',"CharacterSet" => "UTF-8", "MultipleActiveResultSets" => 0)); 

if(!$link) die( print_r(sqlsrv_errors(), true));
sqlsrv_configure('WarningsReturnAsErrors', 0);

$sql = "EXEC spa_i18n_test";

$result = sqlsrv_query($link, $sql); 

if(!isset($result)){
    error_reporting(2047);
}

while( $row = sqlsrv_fetch_array( $result)){
echo $row[0] . ": " . $row[1] . "<br />"; 
}

?>

SCRIPT FOR stored procedure.

Create proc spa_i18n_test   
AS  
SET NOCOUNT ON   
print '2222222222222111111111111111112dddddddddddddd2222222222
        222111111111111111112dddddddddddddd2222222222222111111
        111111111112dddddddddddddd222222222222211111111111111111
        2dddddddddddddd222222222222211111111111111122222222222221
        11111111111111112dddddddddddddd222222222222211111111111111
        1112dddddddddddddd2222222222222111111111111111112dddddddddd
        dddd2222222222222111111111111111112dddddddddddddd22222222222
        221111111111111112222222222222111111111111111112dddddddddddd
        dd2222222222222111111111111111112dddddddddddddd2222222222222
        111111111111111112dddddddddddddd2222222222222111111111111111
        112dddddddddddddd2222222222222111111111111111222222222222211
        1111111111111112ddddddddd22222wwwwwwwwwwwwwwwwwwwwwwwwwwwwww
        wwwwwwwwwwwwwwwwwwwww'

select '1' a, '2' b

I have tried the solution given in http://support.microsoft.com/kb/269412 but sadly it didn't work for me.

Please share some work around.

Upvotes: 1

Views: 1111

Answers (3)

Simeon Bartley
Simeon Bartley

Reputation: 11

This issue still exists in PHP 7.0.11 using sqlsrv driver 4.1.2 (x64) when the character count is greater than about 5200 characters.

sqlsrv_configure('WarningsReturnAsErrors', 0); is important but doesn't fix the problem.

My workaround was to add a bit parameter to the stored procedure in question to optionally suppress the problem output due to PRINT commands.

Upvotes: 1

paulsm4
paulsm4

Reputation: 121659

I'm not sure what's causing your "700" limit. But even so, "PRINT" still has a limit of 8000.

One workaround is to split the "print" text into smaller chunks. For example:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/45be5ec4-2038-4d06-9831-cb18207a676b/executing-dynamic-sql-larger-than-8000-characters

Upvotes: 1

NavaStha
NavaStha

Reputation: 96

SQLSRV does not allowed to execute the print message result the error. Use 

sqlsrv_configure('WarningsReturnAsErrors', 0);

just before 

sqlsrv_query()

function to suppress the error. ie disabled only, but not ignore at database layer.

Upvotes: 1

Related Questions