Jun 302010

Often while I’m in the bathroom, getting ready for work, my two year old son will wander in, and start playing with stuff he finds lying around.  Often, he’ll pick up a little can of hairspray, and attempt to rub it under his arms as though it were deodorant. Highly cute, and highly amusing, but with potentially devastating consequences if he ever figures out how to take the cap off. 

Over the past year, I, and a few others have discovered a similar circumstance with Management Studio.  Very occasionally, say, once every 6 months, Management Studio will confuse the hairspray and deodorant, with potentially devastating consequences.

The situation is that on three occasions in the past year, I have opened a new window, usually with Control-N, and then used the “Change Connection” (right click, “Connection”, “Change Connection…”) option to switch to a different server.  The first time I did this, I was flicking between a test server and a production server to compare data in both environments to determine the differences.  The problem arose when the data in both servers looked identical.  Am I going crazy?  I just proved that the data was different.  What?  Eventually I ran SELECT @@SERVERNAME on both windows, and discovered that both windows were on the production server even though the “test” window was displaying the name of the test server in the status bar, and the tab name.  SSMS was reporting that I had changed the connection from production to test, but @@SERVERNAME did not agree.  Scary.

It occurred again today, again, while I was comparing differences in data between two environments.  It only appears to occur when using “Change Connection” when opening new windows.  Jason Jarret discovered a reproduction for this issue a while back.

There is a Connect item available, but until then, I recommend the following, especially if you’re about to truncate tables on a test system:

  1. If changing to a different server, wait a period of time before changing the connection, just to let the new window finish loading.  And before running anything that could modify the database, double-check your connection.
  2. Alternatively, only ever use Control-N to get a new window for the server you’re connected to.  If you need a window on the other box, select it in the Object Explorer and click “New Query”.

It’s only a twice-a-year kind of bug, but I don’t want it to pop up on me when I’m dropping tables from test. 

Although I’m sure that a good dose of hairspray under the arms could make for some very interesting styles!  I’m just glad that I don’t have Management Studio barging in on me while I’m in the bathroom.