I run various queries against a SQL Server 2012 instance, and occasionally, the following error message will pop up:
The connection must be in an opened state.
Parameter name: connection (SQLEditors)
Although it usually happens while I've left that particular tab idle in SSMS for a while (~10-45 minutes), it also sometimes happen in between running queries nearly back-to-back. The connection state at the bottom will always say "Connected" before I run the query (and obviously immediately change to "Disconnected" when that error shows up).
The database's AutoClose option is disabled, and I also have the "Disconnect after the query executes" option disabled locally. There aren't any scheduled jobs I can find that might be routinely disconnecting queries. Help? Ideas?
Best Answer
This is a very interesting error. Please note there is no error code of any kind associated with it. This means it has to do with SSMS rather than SQL Server.
And this specific error is telling you that the SSMS no longer has active connection to the server. It is possible that this might be the case of faulty switch or similar, but I doubt it.
My recommendation is to check settings pertaining to power saving options of the computer you use in general or your NIC in particular.
In other words - Windows powers off you network adapter while on idle to save power. Might want to check adaper settings as well - my had all sorts of options like "EEE" or "Green Gigabit" enabled...
Addendum
Having similar issues lately when working via wireless connection we - my IT department and I - tracked the issue down to wireless adapter on my laptop and speculated that reason might be some undocumented or inaccessible setting linked to power consumption. It improved only after relentlessly updating drivers to latest possible version using both Intel and Dell updating tools.
Until then same symptoms: connection says it's live, but running queries randomly generated above error. Windows says connection is live, SSMS concurs, no - literally: zero - entries in Windows logs even remotely touching on networking. All is well when using wired LAN.