SQL Server Management Studio - disconnect from Object Explorer whenever a new database is queried

Question

Hi all

I am having a very odd issue with my local SQL Sever Management Studio installation.  

Until recently I have been connecting to a server using a Remote Desktop, and having no issues at all.  I can still do this without any issue.  However, we decided to install Management Tools and ever since I've had issues if I connect via that.

Firstly, I had to change the timeout settings to '0' actually connect as it kept timing out.

Secondly, whenever I create a new query, I get disconnected, then automatically reconnected, and then the query itself runs.  If I run subsequent queries in the same query window, there are no issues, but as soon as I open a new one the same thing happens.

Can anyone cast light on what may be causing this?

I am fairly new to this so if you need logs etc please let me know how to get them and I will post them here.

Thanks in advance

Answer 1

I gather you mean that you've installed SSMS on your local PC and no longer connect to the server using Remote Desktop and use SSMS in the remote desktop environment?

The problem sounds like a network related issue, not a SQL Server issue.  If it was a SQL Server issue, you would experience the same problems using SSMS in the Remote Desktop environment.

---

Jeff Wharton
MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCITP, MCDBA
Blog: Mr. Wharty's Ramblings
MC ID: Microsoft Transcript

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 2

Hi NBanbury,

Since you could connect a server using Remote Desktop, it may not be problem in network.
If it is something with SQL Server, you could reinstall SQL Server Management Studio.
How to configure SQL Server to allow remote connections: http://support.microsoft.com/kb/914277.

 

Thanks,
Maggie

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

Answer 3

Hi NBanbury,

Since you could connect a server using Remote Desktop, it may not be problem in network.
If it is something with SQL Server, you could reinstall SQL Server Management Studio.
How to configure SQL Server to allow remote connections: http://support.microsoft.com/kb/914277.

 

Thanks,
Maggie

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

The user can already connect to SQL Server so "allow remote connections" must already be enabled.

Connecting to SQL Server via a Remote Desktop session is completely different than trying to use SSMS to connect to the server remotely. Given the OP can connect however they keep getting disconnected intermittently, the issue appears to relate to connectivity between the client machine running SSMS and SQL Server.

Reinstalling SSMS may fix the issue.

 

---

Jeff Wharton
MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCITP, MCDBA
Blog: Mr. Wharty's Ramblings
MC ID: Microsoft Transcript

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 4

Thanks for your help guys.  

 

Unfortunately, a reinstall of SSMS has not helped the issue.  All other users who conenct to the same server via SSMS also have no issues.  It's really weird.  Are there user-specific settings somewhere which I may need to adjust?

Answer 5

I'm guessing I'm out of luck on this.  Very weird!

Answer 6

Just wanted to update this - I installed SQL Server 2012 to see if this fixed the issue.  It didn't.  I can only think it must be a network related issue.  

Anyone else seen anything like this?

Answer 7

You seem to be running SQL Server Express Edition. it happens in Express Edition only

Check AUTO_CLOSE option of a database.

Answer 8

Sorry I missed this reply.  I have SQL Server 2008 R2 installed.  No express editions are present on my machine.  If I log into another machine on the same network I get the same issue.

Is it possible it is something to do with my Network account?

Answer 9

Can you check if SSMS 's  SQL Server's Query Execution Options has "Disconnect after the query executes"?

Thanks

Sethu Srinivasan [MSFT]

SQL Server

Answer 10

CONCAT_NULL_YIELDS_NULL is a database option as well.

Is this an override in SSMS for all databases? Thanks.

PS. I don't like this option set. It causes developers confusion. The new CONCAT() function simple uses '' (empty string) instead of NULL.

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Answer 11

Kalman,

Could you please report CONCAT_NULL_YIELDS_NULL issue to http://connect.microsoft.com/SQLServer/feedback

Thanks

Sethu Srinivasan [MSFT]

SQL Server