Friday, 8 June 2012

SQL Server Management Studio Reconnecting

As I run several virtual machines, I usually save their state overnight when I shut my laptop down. If I'm running SQL Server Management Studio it will get disconnected as suspending a virtual machine will temporarily disconnect it from the network.

Upon restoring the VM, if I go to run a query in a window that's already open, I will get the following error.

Msg 10054, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

This was quite frustrating as the "reconnect" button was greyed out so I thought it wise to reconnect to the server before running the query again. This presented two problems:
1) I don't always remember to reconnect
2) Reconnecting logged me onto the "master" database and the query might not work very well there or accidentally create an array of unnecessary objects in the wrong database.

I have since found though, that if you run the query and get the above error, simply running the query again will automatically reconnect you to the server and the correct database.