SSMS : Completely locked up after right-clicking a table by aaronbertrand

We produce an in-house addin that was ported to R2 from non-R2. Some users are reporting this exact problem when our addin is installed, but I am unable to reproduce it. Does anyone know whether this was fixed in SSMS itself, or is there a common idiom used in addin code that is incorrect and needs changing in our code?

I'm *STILL* getting this bug. VERY ANNOYING.

Server and client SSMS are both 2008 R2 CU 4. Server was upgraded to R2 from 2008 while Client SSMS was fresh 2008 R2 install. Client SSMS has both Red Gate SQL Search 1.0.0.321 and SSMS Tool Pack 1.9.0.0 - both latest versions.

(I just received the error and lockup again today for the first time in several weeks -- it may be redgate is an issue, but I'll try to retrace my steps.)

To sum up a possible workaround for those who are still coming across this: on win7 x64 (and sql 2008 R2 x64), I haven't seen the problem since uninstalling the "SSMS Tools" third-party utility and reinstalling the newest version (1.9) of it; I also did the same with Redgate third-party tools. Also I have cumulative update 3 for 2008 R2 installed. (A workaround that might work is uninstalling, not "repairing," ONLY the "Management Studio" and then re-installing it and cumulative update 3--fortunately that did not touch any of my databases or database settings.)    
--It may help if those still running into the problem list third-party tools installed (or not installed).
(The only other things I can think of that could be relevant, apart from what I listed before: Have office 2010 x64 and vs 2010 pro installed; and this installation of sql 2008 R2 was an upgrade over sql 2008.)

Hi,
We couldn't get a repro of this issue on our machines. But after analyzing all the stack traces sent by you, we have attempted to fix this issue. Please keep an eye on this one and if you can send us more details on this with consistent repro steps, that will be really helpful for us.

Regards,
Sumesh

Hi,

I'm having the same problem.

This is the trace per sql server management studio...

It hangs and gives me the following error;

I have windows 7 x64 Pro with microsoft sql server management studio working in
compatibility mode (without it it hangs way more often)
===================================

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

------------------------------
Program Location:

at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.FullTextMenuHandler.GetMenuItems()

===================================

Failed to connect to server EDUARDO-PC\G2IT. (Microsoft.SqlServer.ConnectionInfo)

------------------------------
Program Location:

at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
at Microsoft.SqlServer.Management.Common.ConnectionManager.PoolConnect()
at Microsoft.SqlServer.Management.Common.ConnectionManager.get_DatabaseEngineType()
at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.UpdateConnectionInfoIfCloud(Object& connectionInfo, Urn urn, Boolean& isUpdated)
at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)

===================================

Not allowed to change the 'ConnectionString' property. The connection's current state is connecting. (System.Data)

------------------------------
Program Location:

at System.Data.SqlClient.SqlConnection.ConnectionString_Set(String value)
at System.Data.SqlClient.SqlConnection.set_ConnectionString(String value)
at Microsoft.SqlServer.Management.Common.ConnectionManager.get_SqlConnectionObject()
at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect(WindowsIdentity impersonatedIdentity)
at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()

My Error Technical Details:

===================================

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

------------------------------
Program Location:

at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.FullTextMenuHandler.GetMenuItems()

===================================

Failed to connect to server 192.168.71.40. (Microsoft.SqlServer.ConnectionInfo)

------------------------------
Program Location:

at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
at Microsoft.SqlServer.Management.Common.ConnectionManager.PoolConnect()
at Microsoft.SqlServer.Management.Common.ConnectionManager.get_DatabaseEngineType()
at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.UpdateConnectionInfoIfCloud(Object& connectionInfo, Urn urn, Boolean& isUpdated)
at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)

===================================

Not allowed to change the 'ConnectionString' property. The connection's current state is connecting. (System.Data)

------------------------------
Program Location:

at System.Data.SqlClient.SqlConnection.ConnectionString_Set(String value)
at System.Data.SqlClient.SqlConnection.set_ConnectionString(String value)
at Microsoft.SqlServer.Management.Common.ConnectionManager.get_SqlConnectionObject()
at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect(WindowsIdentity impersonatedIdentity)
at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()


Environment:
OS: Win 7 Enterprise
Processor: Intel(R) Core(TM)2 CPU 6600 @ 2.40GHz 2.40 GHz
Memory: 4.00 GB
MS SQL Server 2008 R2 x64
MS Office Professional Plus 2010 x64
SSMS Tools Pack 1.9.0.0 (error 2x today on previous version, 1x today version 1.9.0.0)
BIxPress (installed not started)
Working with remote server (not local)


Thanks,
Cam

ps. 3rd time in 4 hours.

@MladenP,
Thank you--I temporarily uninstalled ssms tools pack and will wait for an update of your excellent add-in. I'll post back if I run into this bug again.

I have experienced this two times today. It is rather nasty for me as it also causes a BSOD. I lose everything and there is nothing I can do but kill the process. When I try to kill the process it asks me if I want to kill the process and lose everything. I can check the box to enable the submit button and when I do I get a BSOD. I was running through a sequence of changes on a remote server where I was changing 15 to 20 views in row. I would select one and script ALTER to new query window and after some period I would get the error. This one is really bad. I am up to SQL 2008 R2 CU#2 patch level.

Hi,
I'm the creator of the SSMS Tools Pack and the multiple menu items that show up is my bug.
It's been around for a while but i've never been able to reproduce it until a little while back and it will be fixed for next version. It has nothing to do with SSMS.

By looking at the Redgate add-in code in reflector and knowing what to look for i can say that your bug is not related to their product either.

To emphasize for clarity:
There is only ONE stack trace I copied, from ONE error message box (but it is divided into two parts). thanks.

correction: I meant "I also have vs2010 [there is no vs2010 x64] and office 2010 x64 installed."

Alok,
Thank you for helping to track the cause of this. I hope it's resolvable since it's a nasty bug. To clarify: There is only ONE bug, at least it's presented to the user that way (only one error message box and only one stack trace). It is the one and the same error: the error message box reads (as Bertrand says) "Failed to retrieve data . . . Not allowed to change connection string" property.

When you press the "technical details" icon on that error message box, then you get the stack trace I appended, which mentions the data reader issue among other things (though it is divided into two parts). But there is only one stack trace since there is only one error (as far as the end user sees it -- I can't comment on the technical details).

Unfortunately, it happens regularly and freezes ssms so that the user loses his work and sometimes corrupts the database. It only happens in R2 (final, x64 version).

I have it on local machines (no servers to test on), but AaronBertrand reported the error also on a remote connection to a server, so perhaps he can chime in on that.

Note that I also have office 2010 x64 final and vs 2010 x64 final installed (on win 7 x64). The only other thing I can think of is that I have redgate tools installed and SSMS tools, and that changes some of the menu items and right-click actions of SSMS. I don't know if AaronBertrand has that installed also.

Let me know if you need any other information.
Thank you,
Manuel

Hi,

I tried to repro this bug on our dev machines with same configuration Sql Server 2008 R2 with Windows 7 x64 but have not been sucessful so far.
Also also it seems there are two bugs ,earlier one which had connection string cannot be changed and new one with open data reader issue.

I investigated the stack trace given for data reader one and did not find anything suspicious. In case you do hit the first issue again and if it possible to copy its stack trace.Please post it here.

Are you facing this problem with all the servers or just ".\".
Also are you facing this problem on other machines as well.

Regards,
Alok

I want to add that I recommend that this get highest priority for three reasons:
1. it happens fairly frequently--three times today.
2. you don't get a chance to save your work--ssms completely freezes.
3. it seems to have caused database corruption at least once.

Thank you for looking into it.

ok I got a trace! here it is:
(btw the problem should be reproducible if you keep on right-clicking tables in the SSMS object explorer.)

===================================

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

------------------------------
Program Location:

at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.FullTextMenuHandler.GetMenuItems()

===================================

There is already an open DataReader associated with this Command which must be closed first. (System.Data)

------------------------------
Program Location:

at System.Data.SqlClient.SqlInternalConnectionTds.ValidateConnectionForExecute(SqlCommand command)
at System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command)
at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteReader(String sqlCommand, SqlCommand& command)
at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataReader(String query, SqlCommand& command)
at Microsoft.SqlServer.Management.Smo.DataProvider.SetConnectionAndQuery(ExecuteSql execSql, String query)
at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataProvider(StringCollection query, Object con, StatementBuilder sb, RetriveMode rm)
at Microsoft.SqlServer.Management.Smo.ExecuteSql.ExecuteWithResults(StringCollection query, Object con, StatementBuilder sb)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData(ResultType resultType, StringCollection sql, Object connectionInfo, StatementBuilder sb)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult sqlresult, ResultType resultType)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult result)
at Microsoft.SqlServer.Management.Smo.DatabaseLevel.GetData(EnumResult res)
at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData()
at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci)
at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)

The "failed to retrieve data / connection string" freeze happened again. I saw the trace button on the error message box but it did not work (frozen), but I may have accidentally pressed something else first -- will try again next time.

Note also:
I am running sql 2008 R2 x64 on win 7 x64. I also have office 2010 x64 final installed and visual studio 2010 final.

thanks.

correction: The freeware "SSMS Tools" is still appearing as a right-click option for tables. I had said it no longer appeared after the display glitch (when "SSMS Tools" shows up hundreds of times in the right-click list), but I missed it. Again, not sure that is related to the main bug, but I wanted to report (as possibly related) all SSMS object explorer right-click-on-table issues here.

correction:
When I queried sys.dm_exec_requests, it showed that
ALTER DATABASE mydb SET SINGLE_USER;
was "SUSPENDED."
From the query window from which I ran the alter database it seemed it was still running (the "busy" circle and the count in the lower right).

Actually I'm not sure I can send a stack trace because the bug locks up almost everything (the error message box itself is frozen, and SSMS is completely frozen and can't save work), although Ctrl-C did copy the text of the error box. Where is the trace icon (is it in ssms, the message box, somewhere else)?

Will send a stack trace when I reproduce and finish with repair.
I discovered minor corruption on a table when backing up my database, probably caused by having to use task manager several times to close ssms (from the lockups).

In preparation to run a dbcc repair I ran:
ALTER DATABASE mydb SET SINGLE_USER;

That command ran for a day and still didn't complete! (of course, it normally takes only a second.)
I confirmed it was still running by querying sys.dm_exec_requests
Perhaps this is related in some way to the "connecting" message the bug gives.

Fortunately I was able to switch to single user mode using these directions:
http://msdn.microsoft.com/en-us/library/ms345598.aspx

(I am repairing now.)

Hi,

We are trying to get a repro for this.
It would be great if we can get the complete stack trace as it would help us narrow down the problem.
Next time when you hit this issue please use the third small icon next to copy icon to view complete stack trace and update this bug.

Regards,
Alok

here's another odd thing: every so often (when I right-click on a table in SSMS object explorer), I get a zillion entries for "SSMS Tools" (a freeware sql tool). But then that doesn't happen again for a while (and meanwhile "SSMS Tools" doesn't appear as a right-click option).

possibly related: I noticed before the last freeze that I had two identical items listing Redgate's "Smart Rename" option in the context menu (when I right-clicked a table). Now there's only one "Smart Rename" showing. (btw SmartRename is one extremely useful tool).

I have experienced this, this is R2 final. (thanks aaron for reporting this!).
Here is the mesage I posted on the msdn forum, at:
http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/f65737b7-fe4d-487b-aa41-ef437d100bc7

Below is the text of my post there:
Running Windows 7 x64 standalone, SQL 2008 R2 final, using SSMS.
Everything is local and I connect to the server as ".\"
The net transport when this happens is "Shared Memory" (i.e. the default for a connection to local sql server).

This bug is not consistently reproducible but it has happened four times this week with R2 (and never with prior versions.

When it does happen:
I have a query open in SSMS (text editing) with some lines of text highlighted. I also have the SSMS object explorer open to the left. I move to the object explorer on the left to right click on a table in the table list in the same database from which the query I am editing comes from. (I am about to edit the table but I don't make it--.)

blammo, I get the error message box shown below, which CANNOT be closed (though it has an ok and red x close button). After the box appears SSMS is also frozen and has to be terminated from task manager (i.e. can't save work first).

TITLE: Microsoft SQL Server Management Studio
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

ADDITIONAL INFORMATION:
Failed to connect to server .\. (Microsoft.SqlServer.ConnectionInfo)
Not allowed to change the 'ConnectionString' property. The connection's current state is connecting. (System.Data)

Thanks Aaron for reporting this issue. We are investigating the same and will update this bug shortly.
Regards
Vinod