Getting Started with Adobe After Effects - Part 6: Motion Blur
A collection of quick technology learning tips from what people around you learn every day

Connecting back to the SQL Server Instance from a disconnected query window

Jan 31 2012 12:00AM by Jacob Sebastian   

From a disconnected query window, we can connect back to the SQL Server instance from the Query > Connection > Connect menu option. This may sound simple, but I found this interesting.

I usually press F5 (or execute button) twice to get connected and execute my queries. At first execution attempt SSMS will complain that connection is broken and second attempt it will connect automatically.

I was hoping to find an even an easier option such as right click on the status bar that says Disconnected to connect back, but could not find. May be there is a undocumented keyboard short-cut?

Read More..   [32134 clicks]

Published under: SQL Server Tips ·  ·  ·  · 


Jacob Sebastian
1 · 100% · 32220
22
 
15
 
 
0
Incorrect
 
0
Interesting
 
0
Forgotten



Submit

12  Comments  

  • Interesting will check it out for sure. Thanks Jacob.

    commented on Jan 31 2012 11:19AM
    Sudeep Raj
    12 · 13% · 4302
  • I knew about the Query menu option and of course about the pressing F5 twice trick. I am pretty sure there is no keyboard shortcut that's documented, but there may be one that's undocumented. Will check tomorrow and update if I find one.

    commented on Jan 31 2012 12:47PM
    Nakul Vachhrajani
    4 · 36% · 11521
  • There is a difference between both the options. When you press F5 key, it re-connects to the same DB, which you were in earlier. But when you connect to query menu option, it connects to the default database provided in user authentication.

    commented on Feb 3 2012 4:17AM
    kr.roopesh
    779 · 0% · 39
  • @Roopesh,

    I dont see it is connecting to the same DB always. I have seen that it connects sometimes and sometimes it connects to the default database (such as master) and then I have to explicitly select my database.

    The pattern I observed is that if I attempt to run the query after a short period of time, it reconnects to the same DB automatically. But if I try after leaving the query window in disconnected state for several hours and retry, it does not connects to the default database.

    I dont know if that is happening to everyone, but I have been seeing this for the last several years on several different computers :-)

    commented on Feb 3 2012 4:49AM
    Jacob Sebastian
    1 · 100% · 32220
  • @kr.roopesh: I agree with Jacob. I too have seen it reconnecting to the default database, and not the one I am using.

    commented on Feb 3 2012 1:24PM
    Nakul Vachhrajani
    4 · 36% · 11521
  • I may not have checked it for disconnected state for hours. I checked this scenario only in 20-30 mins max.

    commented on Feb 6 2012 10:48AM
    kr.roopesh
    779 · 0% · 39
  • Right click inside the query window, select Connection, then select either Connect or Change Connection.

    On the F5 double-tap, I've seen it reconnect to both the database I was working on and the default database for the Login I'm using.... I can't figure out the pattern. I just start every script with USE [db_name] and don't worry about it from there. It's a PITA when switching from DEV to TEST to PRODUCTION, but nothing is perfect.

    commented on Feb 6 2012 11:15PM
    Marc Jellinek
    95 · 2% · 586
  • I think I have a basic pattern now. Here is how it goes.

    Scneario 1

    1. I am locating a query window (one of the several dozens open on my SSMS :-)) which was not accessed for several hours. The message on the status window shows connected.
    2. Right click on the query window shows context menu with sub-menu connection, with connect option greyed and disconnect enabled.
    3. When executing a query from this window, SSMS shows a message that says "A transport error occurred..blah.."
    4. Executing again will succeed, SSMS will connect to the same server and database.

    Scenario 2

    1. Locating another window where the status on SSMS window says disconnected
    2. Executing a query will open the connect dialog again.
    3. Connection will be established to the default database.

    So in the first scenario, it looks like connection is still open (therefore the login credentials, current database context etc are still valid) but some how a heart-beat-type-of-communication between the client and server did not occur for a longer period than desired.

    Anyone agree?

    commented on Feb 8 2012 7:48AM
    Jacob Sebastian
    1 · 100% · 32220
  • If something is explicitly disconnected from the client side, you'll get the Connect dialog.

    If a session has disconnected (timeout KILL command), you may be able to reconnect hitting F5 a second time.

    I think the "hit F5 a second time" has something to do with client-side connection pooling. If there is still a connection in the pool, I think that hitting F5 a second time will work. If not, you have to use the Connect dialog. But I don't know how to look into the .NET Provider for SQL Server connection pool to confirm

    commented on Feb 8 2012 8:45AM
    Marc Jellinek
    95 · 2% · 586
  • For reconnection, instead of F5, I use Ctrl + F5 (Query > Parse).

    commented on Feb 8 2012 9:30AM
    Bogdan Sahlean
    361 · 0% · 114
  • In my experience the "transport error" is caused when the server is rebooted or for any reason closes its clients' connections without sending any "close connection" message. I expect it ultimately comes from the client attempting to reuse an instantiated connection object that is only discovered, upon use, to no longer be valid.

    commented on Feb 8 2012 1:21PM
    ErikEckhardt
    65 · 3% · 898
  • Hi Jacob,

    Till now i have been using F5 twice trick only. And also i will agree with your Scneario 1 and Scneario 2.

    there r many reasons to disconnect like VPN issues, client side issues or our Local network issues.so it depends on connection lost.

    Thanks for original post.

    commented on Jan 24 2013 2:03AM
    Bala Krishna
    83 · 2% · 678

Your Comment


Sign Up or Login to post a comment.

"Connecting back to the SQL Server Instance from a disconnected query window" rated 5 out of 5 by 22 readers
Connecting back to the SQL Server Instance from a disconnected query window , 5.0 out of 5 based on 22 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]