Re: Exception - sp_cursorfetch: The cursor identifier value provided (0) is not valid.

[Frank Brouwer]

Hi,

It's always a good idea to close database objects you don't need anymore,
but that's beside the point.

I have seen simulair behaviour caused by a database backup during the night.

During this procedure the database was shut-down, the backup made and after
that de database was started again. All the initialized database objects
became invalid after the database was shut-down, so trying to use those
objects resulted in a lot of different error messages (like yours). As you
indicate it occurs after some day's, this (or something simulair) might be
the case here too.

After changing the backup-procedure (do not shut-down) the problem was
solved.

Regard,
Frank

"Mario Freimann" <anon...@discussions.microsoft.com> wrote in message
news:13CB5098-B985-42C1...@microsoft.com...
> I have a java application (JRE 1.4.1_02), which executes several select
statements in my SQL Server 2000 SP 3 (with Microsoft JDBC driver SP2).
>
> This constellation runs fine for some days, but after that, querying the
DB throws several different exceptions. The exception thrown most is:
>
> java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
JDBC][SQLServer]sp_cursorfetch: The cursor identifier value provided (0) is
not valid.
> at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
> at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
> at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown
Source)
> at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown
Source)
> at
com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.processReplyToken(Unknown
Source)
> at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown
Source)
> at com.microsoft.jdbc.sqlserver.tds.TDSCursorRequest.closeCursor(Unknown
Source)
> at
com.microsoft.jdbc.sqlserver.tds.TDSCursorRequest.completeRowProcessing(Unkn
own Source)
> at com.microsoft.jdbc.sqlserver.SQLServerImplResultSet.close(Unknown
Source)
> at com.microsoft.jdbc.base.BaseResultSet.close(Unknown Source)
>
> Other exceptions are:
>
> SQLException: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
JDBC]Unhandled token type: Unknown token: 0x04
>
> java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
JDBC]Unhandled token type: Unknown token: 0x40
> at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
> at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
> at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown
Source)
> at
com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.processReplyToken(Unknown
Source)
> at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown
Source)
> at
com.microsoft.jdbc.sqlserver.tds.TDSCursorRequest.sendCursorFetch(Unknown
Source)
> at com.microsoft.jdbc.sqlserver.tds.TDSCursorRequest.fetchAbsolute(Unknown
Source)
> at
com.microsoft.jdbc.sqlserver.SQLServerImplResultSetServerSideCursor.position
Cursor(Unknown Source)
> at com.microsoft.jdbc.base.BaseResultSet.first(Unknown Source)
>
> java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
JDBC][SQLServer]sp_cursorclose: The cursor identifier value provided (0) is
not valid.
> at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
> at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
> at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown
Source)
> at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown
Source)
> at
com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.processReplyToken(Unknown
Source)
> at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown
Source)
> at com.microsoft.jdbc.sqlserver.tds.TDSCursorRequest.closeCursor(Unknown
Source)
> at
com.microsoft.jdbc.sqlserver.tds.TDSCursorRequest.completeRowProcessing(Unkn
own Source)
> at com.microsoft.jdbc.sqlserver.SQLServerImplResultSet.close(Unknown
Source)
> at com.microsoft.jdbc.base.BaseResultSet.close(Unknown Source)
> at com.microsoft.jdbc.base.BaseStatement.commonTransitionToState(Unknown
Source)
> at com.microsoft.jdbc.base.BaseStatement.close(Unknown Source)
>
> My java application inits some variables:
>
> //init of Connection
> Connection dbConn;
> dbConn = DriverManager.getConnection(url, username, password);
> //ResultSet variable
> ResultSet queryResultSet = null;
>
> The connection parameters are:
>
> Driver com.microsoft.jdbc.sqlserver.SQLServerDriver
> Url jdbc:microsoft:sqlserver://myDBhost:1433;SelectMethod=cursor
> Username testuser
> Password testpassword
>
> Every executen of a select statements uses the following code:
>
> statement = dbConn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY );
> queryResultSet = statement.executeQuery( "SELECT ..." );
>
> What am I doing wrong, that the exception occurs after some time? Am I
missing to release some variables or something?
>
>
> Regards
>
> Mario Freimann

[Templeton@discussions.microsoft.com Lee Templeton]

Your problem is due to the SelectMethod=cursor setting you have in your JDBC connector setup. SQL Server 2000 has a bug where the connections are not closed and memory leakage occurs with this setup. For more information see Keynote: http://support.microsoft.com/default.aspx?scid=kb;en-us;820773

A work around to this problem is to use the default SelectMethod which is SelectMethod=direct.

Also be sure you are using selectQuery and not selectExecute if you are doing a Select only call and vice versa.

Currently, I am looking for the service pack fix to this problem (which I have not seen release). All they presently have is a hotfix which I am also trying to get a hold of, as our current system has the same problem through using a third party app, and the first work around is only a short-term fix.

Lone Wolf

"Mario Freimann" wrote:

> I have a java application (JRE 1.4.1_02), which executes several select statements in my SQL Server 2000 SP 3 (with Microsoft JDBC driver SP2).
>
> This constellation runs fine for some days, but after that, querying the DB throws several different exceptions. The exception thrown most is:
>
> java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]sp_cursorfetch: The cursor identifier value provided (0) is not valid.
> at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
> at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
> at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown Source)
> at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Source)
> at com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.processReplyToken(Unknown Source)
> at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source)
> at com.microsoft.jdbc.sqlserver.tds.TDSCursorRequest.closeCursor(Unknown Source)

> at com.microsoft.jdbc.sqlserver.tds.TDSCursorRequest.completeRowProcessing(Unknown Source)

> at com.microsoft.jdbc.sqlserver.SQLServerImplResultSet.close(Unknown Source)
> at com.microsoft.jdbc.base.BaseResultSet.close(Unknown Source)
>
> Other exceptions are:
>
> SQLException: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Unhandled token type: Unknown token: 0x04
>
> java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Unhandled token type: Unknown token: 0x40
> at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
> at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
> at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Source)
> at com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.processReplyToken(Unknown Source)
> at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source)
> at com.microsoft.jdbc.sqlserver.tds.TDSCursorRequest.sendCursorFetch(Unknown Source)
> at com.microsoft.jdbc.sqlserver.tds.TDSCursorRequest.fetchAbsolute(Unknown Source)

> at com.microsoft.jdbc.sqlserver.SQLServerImplResultSetServerSideCursor.positionCursor(Unknown Source)

> at com.microsoft.jdbc.base.BaseResultSet.first(Unknown Source)
>
> java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]sp_cursorclose: The cursor identifier value provided (0) is not valid.
> at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
> at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
> at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown Source)
> at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Source)
> at com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.processReplyToken(Unknown Source)
> at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source)
> at com.microsoft.jdbc.sqlserver.tds.TDSCursorRequest.closeCursor(Unknown Source)

> at com.microsoft.jdbc.sqlserver.tds.TDSCursorRequest.completeRowProcessing(Unknown Source)