18

Normally when I want to change the connection for a Query Editor Window in SQL Server Management Studio, I can select a new connection from the menus at the top - Query > Connection > Change Connection.

However, when I am working on cubes with an analysis services connection - and then open a saved SQL script (such as the underlying SQL view) the Available Databases combobox is blank, and the "Change Connection" and other 3 options in that menu (Connect, Disconnect, Disconnect all queries) are all greyed out.

The only workaround I have found to be able to run the saved SQL script, is to open a "New Query" from the SQL Database I want to access in the Object Explorer, and either copy my script into it, or reopen the script - which now opens as normal, with the new query's database.

It seems to be taking the last Query Editor Window's database connection as the default connection for any new queries - and when your last "query" is browsing a cube then it breaks? Does anyone have a fix to un-grey out the "Change Connection" option properly?

I am using SQL Server 2008 R2, and opening the saved SQL script by the File > Open > File menu

JumpingJezza
  • 5,498
  • 11
  • 67
  • 106
  • 2
    I hadn't even realized that there was a query > connection > change connection option. Thanks! – Hoppe Oct 11 '13 at 18:37

2 Answers2

11

Luckily in SSMS 2008-R2, 2012 There is a change connection function. Placed just before the DB dropdown. Or via menu Query>Connection>Change Connection.

TarasB
  • 2,407
  • 1
  • 24
  • 32
  • SSMS Boost is very good, but now it's become a paid-for product after being free for several years. (I'd buy it if it wasn't so expensive). In free (trial) mode, it starts greying out menu options after 30 days. See the bottom of [this page](http://www.ssmsboost.com/VersionCompare). – Reversed Engineer Nov 01 '17 at 15:43
  • PS: I'm working my way through [this list](http://www.sqlservercentral.com/articles/SSMS/159134) of SSMS plugins looking for a free or cheap alternative (cheap is my middle name). SQLSmash looks quite good, except it doesn't have that one feature of being able to change the active database from the object explorer. – Reversed Engineer Nov 01 '17 at 15:46
5

Well, SSMS does not allow to manage connections easily (particulary, when you want to switch connections between servers). I have developed add-in for SSMS: SSMSBoost - it adds additional combobox to the toolbar with your favorite connections (you have to define them). In your case, after opening the script you will have to pick the right connection from this new combobox and it will be established. Addin is currently free.

Andrei Rantsevich
  • 2,879
  • 20
  • 25