JDBC running out of memory when calling a stored procedure..

Hi,

I am using Java 1.6.0 to call a stored procedure on SQLServer 2005. The
stored procedure iterates a server side cursor, and then inserts into a
table its status. However, I am getting a SQLException :
2008-12-15 16:26:26,624 ERROR [main] sqlRunner.SqlRunner (SqlRunner.java:66)
- 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:192,431,171. JVM total memory size:94,154,752.

When as far as I am aware, I _am_ using a server side cursor, so why is my
jdbc client receiving this error message?
Can someone explain what is going wrong?...Should I be passing a parameter
such as selectMethod=cursor or responseBuffering=adaptive to the driver?....
Even though the stored proc returns no data?

TIA,

-James


The rough outline of the code is:
jdbc call stored proc using connection.prepareCall
Stored proc opens a cursor to a large resultset
cursor is iterated
cursor completed
stored proc enters a status message in a table

The result of this is that the cursor is completed and the row is seen in
the status table, however on larger resultsets (and I have varied the result
set size) the out of memory error is seen.



Example stored proc:
===================
create proc sp_bare_bones_cursor_test
as

truncate table status

declare @cleaned_assignee varchar(350)

DECLARE test_CURSOR CURSOR FOR
SELECT correspondent_name from tb_rf_transactions
--3930271 7464412
--where patent_id < '7930271'

OPEN test_CURSOR
FETCH NEXT FROM test_CURSOR into @cleaned_assignee
WHILE @@FETCH_STATUS = 0
BEGIN

FETCH NEXT FROM test_CURSOR into @cleaned_assignee

END --WHILE @@FETCH_STATUS = 0

CLOSE test_CURSOR
DEALLOCATE test_CURSOR

insert into status values ('Finished cursor : ['+convert(varchar(200),
getdate())+']')



Example JDBC call:
=================

connection.prepareCall("sp_bare_bones_cursor_test");

url:http://www.ureader.com/gp/1148-1.aspx

Please use selectMethod=cursor. By default any resultset generated is
created in the client memory.

Please post back if this does not help .

--
Bindesh Vijayan
MSFT- SQL Developer Support

"James Bowkett" wrote:

> Hi,
>
> I am using Java 1.6.0 to call a stored procedure on SQLServer 2005. The
> stored procedure iterates a server side cursor, and then inserts into a
> table its status. However, I am getting a SQLException :
> 2008-12-15 16:26:26,624 ERROR [main] sqlRunner.SqlRunner (SqlRunner.java:66)
> - 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:192,431,171. JVM total memory size:94,154,752.
>
> When as far as I am aware, I _am_ using a server side cursor, so why is my
> jdbc client receiving this error message?
> Can someone explain what is going wrong?...Should I be passing a parameter
> such as selectMethod=cursor or responseBuffering=adaptive to the driver?....
> Even though the stored proc returns no data?
>
> TIA,
>
> -James
>
>
> The rough outline of the code is:
> jdbc call stored proc using connection.prepareCall
> Stored proc opens a cursor to a large resultset
> cursor is iterated
> cursor completed
> stored proc enters a status message in a table
>
> The result of this is that the cursor is completed and the row is seen in
> the status table, however on larger resultsets (and I have varied the result
> set size) the out of memory error is seen.
>
>
>
> Example stored proc:
> ===================
> create proc sp_bare_bones_cursor_test
> as
>
> truncate table status
>
> declare @cleaned_assignee varchar(350)
>
> DECLARE test_CURSOR CURSOR FOR
> SELECT correspondent_name from tb_rf_transactions
> --3930271 7464412
> --where patent_id < '7930271'
>
> OPEN test_CURSOR
> FETCH NEXT FROM test_CURSOR into @cleaned_assignee
> WHILE @@FETCH_STATUS = 0
> BEGIN
>
> FETCH NEXT FROM test_CURSOR into @cleaned_assignee
>
> END --WHILE @@FETCH_STATUS = 0
>
> CLOSE test_CURSOR
> DEALLOCATE test_CURSOR
>
> insert into status values ('Finished cursor : ['+convert(varchar(200),
> getdate())+']')
>
>
>
> Example JDBC call:
> =================
>
> connection.prepareCall("sp_bare_bones_cursor_test");
>
> url:http://www.ureader.com/gp/1148-1.aspx
>