Schema Change Error

[rahulkav]

Hi,

We are getting following Exception in our Java application:

====================================================

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
JDBC][SQLServer]Could not complete cursor operation because the table schema
changed after the cursor was declared.
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.getRow(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSCursorRequest.getRow(Unknown Source)
at
com.microsoft.jdbc.sqlserver.SQLServerImplResultSetServerSideCursor.positionCursor(Unknown Source)
at com.microsoft.jdbc.base.BaseResultSet.next(Unknown Source)

---------------------------------------------------------------------

We have a Java application which is running for 24 hours and we have
implemented our own connection pooling. We are connected to SQL Server 2000
with Serverice pack 3 installed.

We get the above error when there is a schema change i.e. Creating a new
table, Dropping an existing table. This schema change is done by a stored
procedure which is run in parallel with our Java application.
We have restared our application but sometimes it doesn't make any
difference. The tables created and droped are in tempDB.

This error doesn't occur regularly and all environments. It seems to be an
intermittent error i.e. some bug in SQL Server.

Can anybody plz help ?

rgds,
Rahul

[Robert Klemme]

"rahulkav" <rahu...@discussions.microsoft.com> schrieb im Newsbeitrag
news:F331C7F1-E196-445B...@microsoft.com...

I'd rather suspect it's an error in your application logic: you change
something concurrently that your cursor relies on. You need some kind of
mutual exclusion mechanism. Also, make sure you have closed all
statements and result sets when you return a connection to the pool.

Regards

robert

[Joe Weinstein]

Hi. Whenever something changes in the DBMS that might affect
the query plans of any current connections, such as changing
a table that is being queried (even just changing an index),
or changing a stored procedure being used, a connection will
get that exception if it tries to re-use the stale query plan.
For instance if you create a PreparedStatement to do "select * from foo",
and you later add an index to table foo, the query plan associated
with the PreparedStatement is now defunct. You would have to close
that PreparedStatement and make a new one. Scrollable updateable
result sets are also cursor/query-plan based, so they will also have
to be closed and recreated whenever this happens.

I hope this helps,
Joe Weinstein at BEA