We help IT Professionals succeed at work.

MSSQL 2008 R2 Express: Invalid Cursor State error 24000

IT_Architect
IT_Architect asked
on
Situation:  I recently converted a customer from SQLAnywhere to MSSQL 2008 R2 Express.  The app has worked fine for years with SQLAnywhere.  I've made these conversions many times before with 2005 with no issues.  The clients are Windows 7 Home Premium, Windows XP professional, and SBS 2008.  All clients are patched up to the latest.  I also installed the latest MSSQL 2008 R2 client drivers.

Behavior:  When running the app with my development computer, XP, or the SBS 2008 computer, the app runs without error.  However, running it from other XP clients on the network or another development machine running Windows 7, I consistently encounter the Invalid Cursor State 24000 error in the same areas of the app.

Solution required:  Determine how to enable the other computers to be able run the app without error.
Comment
Watch Question

Maybe this KB can help you ? http://support.microsoft.com/kb/925227
It's about catalog (database), schema or table names with an underscore or % in them...

Author

Commented:
That seems to apply to SQL Server 2005.  I did check one of the tables giving trouble and there are no underscores and I don't use percent signs.  Thank you for your reply.  The odd thing is it works perfectly with an XP development machine and the SBS 2008 server.  It has the problem on a Windows 7 development machine and the XP machines on the network.  They are all accessing the same SQL 2008 R2 Express database running on the same SBS 2008 server during the same time period.
It would be interesting to now how the app is connecting to the SQL server; I mean : ODBC, OLE DB, ADO...  What do you have installed on your development machine that is _not_ on the production XP machines (related to database connections of course).

There is a tool which allows to determine what MDAC version you actually have on a machine; it's explained here : http://support.microsoft.com/kb/301202

Also : what is the application doing (in the database) at the point where you get the error ?

Author

Commented:
That was a good thought.  I didn't know how to find that out.  However, I ran it on my development XP machine and another XP client on the network that doesn't work, and it displays the same version for both, MDAC 2.8 SP1 on Windows XP SP3
I finally figured it out.  I tried installing the 2008 R2, and 2008 native clients, but neither helped.  Then I installed the 2005 native client and bingo!  Everything worked fine on every machine.  Then I thought through the history.  SBS 2008 comes with 2005, and I used to work with 2005 which is why those two machines worked and the others did not.