0

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?

  • 2
    Are you running the queries against same database? Are you dropping any database in between the queries? – SqlWorldWide Jan 5 '18 at 20:02
  • Same database. No database dropping. – longbored Jan 5 '18 at 20:09
  • Flaky network connection? – Aaron Bertrand Jan 5 '18 at 20:14
  • Wired corporate network... – longbored Jan 5 '18 at 20:16
  • 2
    That doesn't mean all the connections and all the nodes are reliable. I used to have a domain controller that would flake out occasionally and this caused all kinds of brief network disruptions. – Aaron Bertrand Jan 5 '18 at 20:23
1

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.