I hope someone out there can shed some light on what is going on here.
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 SP3 installed.
The problem is that from time to time (usually only when a stored procedure has run wich creates and deletes temporary tables) suddenly one of the queries that is run many times (not always the same query) fails every time it is executed. The error is as follows:
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.
We have restarted our app (recreting connections) but sometimes it doesn't make any difference, and the only way to surely remove the error is to restart the db.
This error doesn't occur regularly, or in every environments. It seems to be an intermittent error i.e. some bug in SQL Server.
We managed to get around this problem by running the following system store procedures but that results in Memory Leak in our java application.
Any help would be much appreciated