Invalid Cursor State error Pass Thru Query

thebrepp
thebrepp used Ask the Experts™
on
Microsoft Access (2016)  ALL Pass Thru queries to SQL Server 2017 suddenly (starting today) failing for about 15% of users.  Rest of app is fine but when these particular users hit a pass thru they get ODBC All Failed ODBC Driver manager Invalid Cursor State (#0).   All users on Windows 2010, ODBC 17, all same patch/update level of both Office and Windows. No changes to app and many of the sprocs are very simple such as passing one parameter to a SQL View to populate a now filtered form.

Nothing shows up in terms of patches, updates or difference form the majority that do work fine vs the ones now failing.  Many of these pass thru queries have run for years. All other network functions are fine as is the rest of the app where sprocs are not called via the pass thru.  Problem is strictly pass thru.  95% of the sprocs do not even use cursors.

This is a puzzle to me.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Commented:
If you can replicate the issue probably you can nail it...its a bit "strange" error in the concept that it can be "fired" under more than one case....it can be due to empty or one row Recordset and you try to skip.
You probably have to debug it.
Uninstall the latest Windows and Office updates for one of the users and test again. The you should try older ODBC driver.

Author

Commented:
Thanks for comments.
Last Night or earlier I did the below on one “bad” pc.  Please note that problem showed up on 6 pc’s (different Dell models) on Monday morning where the Friday before all were fine.  By “All”  or “None” below I mean both working fine and those not. Also rest of app works fine.  It is only when a bad user, uses part of app where a sproc is called via a pass thru.  Other users – no problem.
1)      All were at same Windows 10 patch level.  Latest was prior to problem by several work days so all worked initially after the patch.
2)      There was an Office 16 update that came in for some over the weekend and for some a day earlier but patch is in place for both working and non-working so mentally I ruled that out.
3)      Temporarily suspended anti-virus (NOD32) and Firewall.
4)      For testing, I used simplest sproc called by pass thru.  A simple select * from a view with one parameter for the Where.  No cursor here or in most sprocs.  I also remade the pass thru.
5)      PC’s had not received any Dell patches.  I did verify none were needed.  No parallel of issue can be tied to one specific model Dell.  Within a model, some good, some bad.
6)      Checked bad and some good to see if any new software was loaded or other updated – none.
7)      I even temporarily made the one bad pc ODBC connection with sa privileges.  All connect with SQL Authorization.  Balance of ODBC works fine.
8)      All users are members of same Group.
9)      Tried earlier ODBC levels (11 and 13).
10)      Uninstalled and reinstalled Office 2016 on the one bad test pc.
11)  Ruled out data as good use same data as bad and on that one test sproc/pass thru.

I keep thinking - "what changed" but so far cannot tell.  Maybe a SQL Server patch?  Maybe a patch I do not know where to look for?
What happens if "good" user logins to the "bad" user's computer and use the app? Then please test opposite configuration. User profile can contain unexpected contents so you should try to delete the user profile from the PC.

Author

Commented:
Just found a guy that posted an hour ago with same issue that just started yesterday as well.  Some work, some don't.  We have gone over findings, attempts, etc and now both convinced Microsoft Office update caused this.  Trying to isolate which one as it appears whatever day matters.  Friday vs Monday, etc.

Author

Commented:
Issue has been solved.  In turns out there were two Office 2016/365 updates that occurred in a 2 or 3 day time frame.  If you got the earlier one, it did not do the second one. and you were fine.  (1911).  But if your update time was on the second one (1912) you hit a newly introduced Microsoft but which caused the problem.   Reverting any 1912 back to 1911 solved the problem. Also for now all of the pcs had their office update turned off.
Commented:
Revert Office update 1912 back to 1911.  Turn off updates for now.
Hmm… Didn't we recommend "Uninstall the latest Windows and Office updates for one of the users"?

Author

Commented:
It was thought all users received the same Office update as they were within 2 days of each other and based on if PC was turned on - Monday vs weekend.   No Windows updates had come in.  I had ruled that out - mistakenly as it was the first place I looked.  I should of looked beyond Windows programs dialog as it let me to believe they were all the same - all users got the same update. In reality they were a day or two apart and if you got the first one you were immune to the second bad one.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial