15

I'm trying to fill a HTML table with some SQL Server 2008 r2 data, the controller (php_sqlsrv) works fine, the tables are filled very well, but when I try to retrieve a 2000 or more rows (maybe less) it crashes and shows this message:

SQL Error: Array ( [0] => Array ( [0] => IMSSP [SQLSTATE] => IMSSP [1] => -59 [code] => -59 [2] => Memory limit of 10240 KB exceeded for buffered query [message] => Memory limit of 10240 KB exceeded for buffered query ))

How can I fix this? Is this a PHP or a sqlsrv problem? Can I fix this from the SQL Server Management Studio?

halfer
  • 19,824
  • 17
  • 99
  • 186
user3435588
  • 153
  • 1
  • 1
  • 7
  • Can we see your code that does this? Are you trying to hold all of those rows in memory? Can you increase the buffer limit it refers to? I don't know where that is set, but I imagine php.net has docs on it. – halfer Dec 09 '14 at 23:44
  • 1
    Great. Please add a solution in the box below, so that other people can see how to fix this error. – halfer Dec 10 '14 at 00:13

3 Answers3

14

Change the setting in php.ini.

Section: sqlsrv

Directive: sqlsrv.ClientBufferMaxKBSize.

obotezat
  • 1,041
  • 16
  • 20
14

add two lines in php.ini

extension=php_pdo_sqlsrv_55_ts.dll
extension=php_sqlsrv_55_ts.dll
client_buffer_max_kb_size = '50240'
sqlsrv.ClientBufferMaxKBSize = 50240
Sangat
  • 137
  • 1
  • 5
13

You can also change the settings during runtime if you do not require production server to have the php.ini changed (Check if this is applicable with your hosting).

Update the code with following lines:

ini_set('memory_limit','256M'); // This also needs to be increased in some cases. Can be changed to a higher value as per need)
ini_set('sqlsrv.ClientBufferMaxKBSize','524288'); // Setting to 512M
ini_set('pdo_sqlsrv.client_buffer_max_kb_size','524288'); // Setting to 512M - for pdo_sqlsrv

To check if your server supports this, try printing the values after setting the above.

echo ini_get('memory_limit');
echo ini_get('sqlsrv.ClientBufferMaxKBSize');
echo ini_get('pdo_sqlsrv.client_buffer_max_kb_size');

The new values should be the ones we had set in ini_set(). Else, the server does not support runtime configuration changes.

Rony Samuel
  • 301
  • 3
  • 3