SSMS 2014 randomly starts throwing "Value does not fall within the expected range" when using backspace key

Question

Randomly when writing SQL queries in SSMS 2014, I'll get a popup that "Visual Studio has encountered an exception. This may be caused by an extension" along with a tip that I can run the application with the /log parameter and check the ActivityLog.xml file created somewhere in my user's AppData directory.

After this popup, IntelliSense stops updating, and toggling it on/off has no effect (any errors still underlined with red squiggly lines, etc, including their mouse hover popups remain indefinitely). Note that this happens even without any errors in my script.

But that's not really the issue, as I get the "Value does not fall within the expected range" error EVERY TIME I PUSH THE BACKSPACE KEY, although the Delete key works just fine.

This issue was not present until many months after I started using the application.

The workaround so far has been to copy the query into a new query window (immediately works), or to wait for it to randomly stop. So the issue is isolated to the query window.

Steps to reproduce:

1. Type SQL code for a while.

There seems to be absolutely no commonality, since it has happened with all combinations of syntactically correct/incorrect statements, and long/short queries (ranging from a single SELECT statement to a multi-thousand line stored procedure). It also happens in statements not referencing any user-defined entities (e.g. selecting from the system tables).

Troubleshooting steps taken:

1. Making sure all updates installed
2. Restarting computer
3. Disabling all extensions - I'm not using any... I followed this registry path and I have no AddIns key in either the x86 or x64 registry paths for any version number of SQL Server.
4. Googling for issue - nothing relevant - I'm typing queries and not using SSIS
5. Running SSMS with /log parameter - initial error telling me to start SSMS with the /log parameter is not logged. But the "out of range" error is logged with this identical stack trace every time (included at bottom of post)

Looking at the log file, they may be a two hour gap between the last message (larger time than I have been in the query window) and the exception caused by using the backspace key.

The following steps were performed by my company's IT department:

6. Clearing IntelliSense and other caches
7. Reinstalling SSMS

And later:

8. Entire computer replaced (I have since completely abandoned IntelliSense for SQL). It worked for a while, then started again after a couple months.

I seem to be the only one with this issue, as my company's IT department has not seen it before, and all Googling for this error message has been fruitless (I'm not using SSIS). It's been bugging me for over a month now and driving me crazy.

I am getting similar weird behavior in Visual Studio 2013 when editing JavaScript, and it even threw an error which I think is the same as the first one thrown in SSMS before the backspace issue (but I clicked too fast and closed it before reading).

Sample Stack Trace in ActivityLog.xml

<entry>
    <record>1172</record>
    <time>2017/01/16 19:33:02.289</time>
    <type>Error</type>
    <source>Editor or Editor Extension</source>
    <description>
System.ArgumentException: Value does not fall within the expected range.&#x000D;&#x000A;
   at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo)&#x000D;&#x000A;
   at System.Runtime.InteropServices.Marshal.ThrowExceptionForHR(Int32 errorCode)&#x000D;&#x000A;
   at Microsoft.VisualStudio.NativeMethods.ThrowOnFailure(Int32 hr, Int32[] expectedHRFailure)&#x000D;&#x000A;
   at RadLangSvc.SqlCompletionSet.GetTextTypedSoFar()&#x000D;&#x000A;
   at RadLangSvc.Source.OnCommand(IVsTextView textView, VSStd2KCmdID command, Char ch)&#x000D;&#x000A;
   at Microsoft.VisualStudio.Package.ViewFilter.HandlePostExec(Guid&amp; guidCmdGroup, UInt32 nCmdId, UInt32 nCmdexecopt, IntPtr pvaIn, IntPtr pvaOut, Boolean bufferWasChanged)&#x000D;&#x000A;
   at Microsoft.VisualStudio.Package.ViewFilter.ExecCommand(Guid&amp; guidCmdGroup, UInt32 nCmdId, UInt32 nCmdexecopt, IntPtr pvaIn, IntPtr pvaOut)&#x000D;&#x000A;
   at Microsoft.VisualStudio.Package.ViewFilter.Microsoft.VisualStudio.OLE.Interop.IOleCommandTarget.Exec(Guid&amp; guidCmdGroup, UInt32 nCmdId, UInt32 nCmdexecopt, IntPtr pvaIn, IntPtr pvaOut)&#x000D;&#x000A;
   at Microsoft.VisualStudio.Editor.Implementation.CommandChainNode.Exec(Guid&amp; pguidCmdGroup, UInt32 nCmdID, UInt32 nCmdexecopt, IntPtr pvaIn, IntPtr pvaOut)&#x000D;&#x000A;
   at Microsoft.VisualStudio.Editor.Implementation.CommandFilterWrapper.Exec(Guid&amp; pguidCmdGroup, UInt32 nCmdID, UInt32 nCmdexecopt, IntPtr pvaIn, IntPtr pvaOut)&#x000D;&#x000A;
   at Microsoft.VisualStudio.Editor.Implementation.CommandChainNode.Exec(Guid&amp; pguidCmdGroup, UInt32 nCmdID, UInt32 nCmdexecopt, IntPtr pvaIn, IntPtr pvaOut)&#x000D;&#x000A;
   at Microsoft.VisualStudio.Editor.Implementation.CommandFilterWrapper.Exec(Guid&amp; pguidCmdGroup, UInt32 nCmdID, UInt32 nCmdexecopt, IntPtr pvaIn, IntPtr pvaOut)&#x000D;&#x000A;
   at Microsoft.VisualStudio.Editor.Implementation.CommandChainNode.Exec(Guid&amp; pguidCmdGroup, UInt32 nCmdID, UInt32 nCmdexecopt, IntPtr pvaIn, IntPtr pvaOut)&#x000D;&#x000A;
   at Microsoft.VisualStudio.Editor.Implementation.CommandFilterWrapper.Exec(Guid&amp; pguidCmdGroup, UInt32 nCmdID, UInt32 nCmdexecopt, IntPtr pvaIn, IntPtr pvaOut)&#x000D;&#x000A;
   at Microsoft.VisualStudio.Editor.Implementation.VsTextViewAdapter.Exec(Guid&amp; pguidCmdGroup, UInt32 nCmdID, UInt32 nCmdexecopt, IntPtr pvaIn, IntPtr pvaOut)&#x000D;&#x000A;
   at Microsoft.VisualStudio.Editor.Implementation.VsKeyboardFilter.SendCommand(Guid cmdGroup, UInt32 cmdID, Object inParam)&#x000D;&#x000A;
   at Microsoft.VisualStudio.Editor.Implementation.VsKeyboardFilter.TextInput(TextCompositionEventArgs args)&#x000D;&#x000A;
   at Microsoft.VisualStudio.Text.Editor.Implementation.KeyProcessorDispatcher.&lt;DispatchTextInputEvents&gt;b__0(KeyProcessor p, TextCompositionEventArgs args)&#x000D;&#x000A;
   at Microsoft.VisualStudio.Text.Editor.Implementation.KeyProcessorDispatcher.&lt;&gt;c__DisplayClass1e`1.&lt;Dispatch&gt;b__1a()&#x000D;&#x000A;
   at Microsoft.VisualStudio.Text.Utilities.GuardedOperations.CallExtensionPoint(Object errorSource, Action call)
    </description>
</entry>

We upgraded to VS 2017 and SQL IntelliSense is behaving there like it did before. It went out of control and spammed memory and CPU, forcing me to use Task Manager to kill VS.

Version information:

Microsoft SQL Server Management Studio 12.0.4232.0

Microsoft Analysis Services Client Tools 12.0.4232.0

Microsoft Data Access Components (MDAC) 6.1.7601.17514

Microsoft MSXML 3.0 5.0 6.0

Microsoft Internet Explorer 9.11.9600.18537

Microsoft .NET Framework 4.0.30319.36373

Operating System 6.1.7601

Answer 1

I find it ridiculous that backspace error in SSMS is still an issue; worse than the description in the original post, it hits me again with the error in about a minute or so. Also, none of the workarounds explained before seem to work. I can't understand it, so I'm thinking seriously to install the MS PowerToys and deactivate the backspace key, so I would learn ignoring it's existence; this, before I waste any more brain cells on things that should not be. I guess I could also remove the key from my keyboard...

Do my ideas seem like normal solutions, SSMS development team guys? If so, it should be added to the SSMS official help page.

PS: The SSMS version with all this fun is 20.2.30.0

Answer 2

I noticed that this happens when I have a mismatch on BEGIN/END or some syntax error that is causing an error further down in the script.

Fixing the syntax issue appears to resolve the error message box.

Answer 3

This is a bug with intellisense, which is present even in v18.5

Two workarounds:

1. Turn back on auto-list members (Options / Text Editor / All Languages / General)

2. If you want to keep auto-list members off, then when you get the error, hit [Ctrl]+[Space] to list members, this should repopulate intellisense object with data.