SQL Server JDBC - How to use server side cursors?

Author

How to use server side cursors?

Mark

2006-03-17, 3:23 am

With SQL Server 2000 and the previous jdbc driver, we used the
SelectMethod=cursor on the jdbc url and everything worked fine. We upgraded
to SQL Server 2005 and the new jdbc driver (sqljdbc.jar) and have run into
problems where using the SelectMethod=cursor causes batch updates to fail
with the following:

Exception: com.microsoft.sqlserver.jdbc.SQLServerException:
sp_cursoropen/sp_cursorprepare: The statement parameter can only be a single
select or a single stored procedure.

If we remove the SelectMethod=cursor we get the jdbc client running out of
memory when we query large tables with the following exception:

The system is out of memory. Use server side cursors for large resultsets

So how can we use server side cursors with the new driver and still have
batch updates working? Is there another way to enable server side cursors?

Angel Saenz-Badillos[MS]

2006-03-17, 8:23 pm

We are actively working on this issue. I will post here as soon as I hear
more.

Thank you,
Angel

--
Angel Saenz-Badillos [MS] DataWorks
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging: http://weblogs.asp.net/angelsb/

"Mark" <Mark@discussions.microsoft.com> wrote in message
news:1E035388-E550-4CBF-A7AD- 09C52C1B0F0B@microso
ft.com...
> With SQL Server 2000 and the previous jdbc driver, we used the
> SelectMethod=cursor on the jdbc url and everything worked fine. We
> upgraded
> to SQL Server 2005 and the new jdbc driver (sqljdbc.jar) and have run into
> problems where using the SelectMethod=cursor causes batch updates to fail
> with the following:
>
> Exception: com.microsoft.sqlserver.jdbc.SQLServerException:
> sp_cursoropen/sp_cursorprepare: The statement parameter can only be a
> single
> select or a single stored procedure.
>
> If we remove the SelectMethod=cursor we get the jdbc client running out of
> memory when we query large tables with the following exception:
>
> The system is out of memory. Use server side cursors for large resultsets
>
> So how can we use server side cursors with the new driver and still have
> batch updates working? Is there another way to enable server side cursors?
>

David Olix

2006-03-28, 8:23 pm

Hello all,

The selectMethod=cursor bug has been fixed and released as a hotfix.
Please contact CSS to obtain the fix. You will need to ask for the hotfix
for KB article 917054.

As of this writing, only the Windows (zip) package is available. We are
still working on making the tar.gz package available.

Thank you,
--David Olix
JDBC Development

Sponsored Links