SQL Server cursor memory rrror

[anil.kumar]

Exception Trace:

com.microsoft.sqlserver.jdbc.SQLServerException: The system is out of memory. Use server side cursors for large result sets:Java heap space. Result set size: n. JVM total memory size:m
at com.microsoft.sqlserver.jdbc.SQLServerException.ma keFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.DBComms.receive(Unkno wn Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStat ement.doExecutePreparedStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStat ement$PreparedStatementExecutionRequest.executeSta tement(Unknown Source)
at com.microsoft.sqlserver.jdbc.CancelableRequest.exe cute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.e xecuteRequest(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStat ement.executeQuery(Unknown Source)

Reason for this is use of buffer by SQL Server Driver which is by default set to full.

To fix it in connection URL append:
responseBuffering=adaptive
Example URL:

jdbc:sqlserver://localhost:1433;SelectMethod=cursor;Database=Northw ind;responseBuffering=adaptive


123


This will change driver behavior from full buffering to adaptive.

If this doesn’t work then we need to change SelectMethod=cursor to SelectedMethod=direct

For more information see this link..

http://blogs.msdn.com/jdbcteam/archive/2007/05/02/what-is-adaptive-response-buffering-and-why-should-i-use-it.aspx

[mary123]

If you are using AWE (Address Windowing Extensions) on 32-bit SQL Server 2000, the cursor occupies available memory from the pool used by locks, cached procedure plans and user connections, which may cause more memory pressure in the memory space. This is not a problem in SQL Server 2005 or in 64-bit SQL Server 2000.