SSMS 2008 R2 IntelliSense Stopped Working after VS 2010 SP1

Question

I recently installed Visual Studio 2010 Service Pack 1 on my development machine, and now it appears that IntelliSense in SQL Server Management Studio 2008 R2 has stopped working.  IntelliSense in other Microsoft tools on my machine (Visual Studio 2008, Visual Studio 2010) is still working properly.

I tried a few searches on Google, but didn't come up with anything that helped.

I've tried doing a "Repair" on SQL Server, but that has not fixed the issue.  I also already tried the steps the steps discussed in this thread http://social.msdn.microsoft.com/Forums/en/sqltools/thread/dcd5c2cb-fc53-4c28-8b09-bfd9fea6684e as well as http://msdn.microsoft.com/en-us/library/ms173434.aspx, but neither help.

I've tried connecting to both a local SQL Server 2008 R2 Developer Edition database using Windows authentication, and a remote SQL Server 2008 R2 Standard Edition databsae logging in as "sa".

Has anyone else had problems with VS 2010 SP1 affecting IntelliSense in SSMS?  Any ideas on how to get it working again?  Is this a known issue?

---

Richard Glidden Brock Solutions www.brocksolutions.com Engineering Solutions that Perform.

Answer 1

I'm experiencing the same issue, but haven't found any resolution yet either.

FYI - Here is a link to the same issue on the VS forum, in case that gets resolved first:

http://social.msdn.microsoft.com/Forums/en-US/vssetup/thread/b4b3c27a-6bdc-4ba3-a76b-982adf5d5481

Answer 2

 

Hi Richard,

 

Thank you for your post.

 

For a further investigation, please try these steps as below: 

1. Enable IntelliSense: 

   1. For all query windows, please go to Tools >> Options >> Text Editor >> Transact-SQL >> IntelliSense, and select Enable IntelliSense.
   2. For each opening query window, please go to Query >> Intellisense  Enabled.  
2. Refresh IntelliSense local cache: please go to >> Edit >> IntelliSense >>Refresh Local Cache or use the CTRL+Shift+R keyboard shortcut to refresh.
3. Enable statement completion: please go to Tools >> Options >> Text Editor >> Transact-SQL >> General, and check on Auto list members and Parameter information boxes.
4. Reboot SQL Server Management Studio.

 

If this not helps, we could try to uninstall Visual Studio 2010 SP1 to verify whether this issue (SSMS IntelliSense stop working) is related to VS SP1. If it indeed, then I would like to suggest you submit a feedback to Microsoft at https://connect.microsoft.com/SQLServer, this connect site is a connection point between you and Microsoft. Your feedback enables Microsoft to make software and services the best that they can be.

 

Thanks,
Weilin Qiao

---

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,

I've also got the same probelm after I've installed the VS SP1... And still looking for the solution.......

I've tried all the step mentioned above, but still with no luck.... !!!!!

Eyad

 

 

 

Answer 4

WeiLin,

Thanks for your reply.  I have already tried most of your suggestions, but for the record:

1. IntelliSense is already Enabled.  Disabling and re-enabling has no effect.
2. Refreshing the Local Cache has no effect.
3. Auto list members and Parameter information are already checked.  Unchecking and re-checking them has no effect.
4. Restarting SSMS, rebooting the SSMS Workstation, and rebooting the SQL Server computer all have no effect.

I am unable to uninstall Visual Studio 2010 Service Pack 1 at this time due to the following:

Warnings:

The removal of Microsoft Visual Studio 2010 Service Pack 1 may put this computer in an state in which projects cannot be loaded and Service Pack 1 cannot be reinstalled. For instructions about how to correct the problem, see the readme on the Microsoft Download Center website.

I cannot afford to have my Visual Studio environment break at this time.  I will try to do the uninstall on another machine.  However, I have verified that it is related to VS 2010 SP1 by doing another, more controlled, installation last night.  On a second machine with Visual Studio 2010 and SQL Server 2008 R2 Developer Edition installed, I did the following:

1. Reboot the computer.
2. Log in as a user with Administrative privileges.
3. Run SSMS 2008 R2, and verified that IntelliSense is working against the "ReportServer" database, and other databases.
4. Close SSMS 2008 R2, and ensure no other programs are running.
5. Installed VS 2010 SP1.
6. Rebooted at the end of VS 2010 SP1 as requested by the setup program.
7. Logged in as the same user with Administrative privileges.
8. Run SSMS 2008 R2, and verified that IntelliSense is no longer working against the "ReportServer" database, or other databases.

Since there were no other programs running on the machine, and I did not install any other updates, or do anything else to the machine aside from installing Visual Studio 2010 SP1, I'm confident that there is some sort of conflict here.

I will report the issue via Microsoft Connect.

Thanks for your assistance.

---

Richard Glidden - Brock Solutions - www.brocksolutions.com - Engineering Solutions that Perform.

Answer 5

Someone else has already submitted the issue via Microsoft Connect.  For anyone else having the same problem, the Connect issue can be found here:

https://connect.microsoft.com/SQLServer/feedback/details/650569/ssms-2008-r2-is-losing-intellisense-after-installing-visual-studio-2010-sp1

---

Richard Glidden - Brock Solutions - www.brocksolutions.com - Engineering Solutions that Perform.

Answer 6

For what it is worth, I have not seen this problem on multiple machines where I have installed VS2010 SP1. They all either have Windows 7 Enterprise SP1 or Windows Server 2008 R2 SP1, along with SQL Server 2008 R2 CU5 or CU6.

Are you using the latest CU (which is CU6) for SQL Server 2008 R2?  If not, I would try installing that, and perhaps it will correct the issue.

---

Glenn Berry
http://glennberrysqlperformance.spaces.live.com/
http://www.twitter.com/GlennAlanBerry
Please click the Mark as Answer button if a post solves your problem!

Answer 7

Thanks for the tip Glenn!  Installing CU6 on my development machine has resolved the problem!

---

Richard Glidden - Brock Solutions - www.brocksolutions.com - Engineering Solutions that Perform.

Answer 8

I have CU6 installed and my intellisense is half working.  Select * from ... brings up a list of tables, but I can't seem to get intellisense on column names, which used to work great prior to SP1.  

Answer 9

This is working properly for me after applying CU6.  IntelliSense is context sensitive.  It will only show column names if it makes sense to insert a column name in that spot in your code. So, if you do:

SELECT *
FROM dbo.

Then when you press the ".", you will get a list of tables.  However, if you do:

SELECT *
FROM dbo.Table.

It won't give you column names, because a column name doesn't make sense in the FROM clause.  But if you do:

SELECT [dbo].[Table].

Then you should get a list of column names.  Also, if you do:

SELECT
FROM [dbo].[Table]

Then put your cursor after the SELECT, you should get IntelliSense on the column names.

You may also need to use the "Refresh Local Cache" (CTRL-SHIFT-R) to get the column names to show up.

---

Richard Glidden - Brock Solutions - www.brocksolutions.com - Engineering Solutions that Perform.

Answer 10

you're third example is not giving me any columns names. 

Neither is: Select * from TableName where ...

that used to always give me column names.  Refreshing the Cache did not unfortunately.

Answer 11

For me

select T.   from myTable T will give column names after I type T.

---

For every expert, there is an equal and opposite expert. - Becker's Law

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 12

Same as MSCRIVO!!

Sometime intellisense works, sometimes not. E. g. most of the time intellisense shows me no column names ;(

Regards
Daniel

---

Daniel Schmitz

Answer 13

Thanks Weilin...This worked for me.  Thanks for your help.

Answer 14

Intellesence is not showing Column Names.

I have installed this hotfix SQLServer2008R2_RTM_CU6_2489376_10_50_1765_x64 on Windows 7 machine and still intellesense is not showing the column names ........ :S ...

Richard, please which hotfix you had installed !

Regards,

 

Answer 15

I installed SQLServer2008R2_RTM_CU6_2489376_10_50_1765_x64 from http://support.microsoft.com/kb/2489376 on both of my machines (Windows 7 Enterprise x64, and Windows 7 Ultimate x64), both running SQL Server 2008 R2 Developer RTM.

This appears to have resolved the issue on both of my machines.  I am able to get IntelliSense, including column names.

---

Richard Glidden - Brock Solutions - www.brocksolutions.com - Engineering Solutions that Perform.

Answer 16

Well, although CU6 worked great on my machine, it's now broke Database Projects in Visual Studio.  VS 2010 now gives me the error:

Method not found: 'Microsoft.SqlServer.Management.SqlParser.Common.TransactSqlVersion Microsoft.SqlServer.Management.SqlParser.Parser.ParseOptions.get_TransactSqlVersion()'

Guess I'll have to start looking for another work-around.

---

Richard Glidden - Brock Solutions - www.brocksolutions.com - Engineering Solutions that Perform.

Answer 17

For me CU6 doesn't work at all. E.g.:

;

 

WITH CTE
AS
(
    SELECT ID FROM Table1
)
SELECT ID FROM CTE

 

On typing i get intellisense for first SELECT, but there is no intellisense for second SELECT. I use CTE's very often and i get allways intellisense for this.

Often i get on initial writing intellisense and no intellisense after writing some lines of T-SQL code.

Regards
Daniel

---

Daniel Schmitz

Answer 18

I've just installed CU6 on my PC (Win7 Enterprise x64), and it doesn't fix the problem. I get intellisense for table and view names in the first select statement, and for column names from a CTE, but nothing for column names from a table or view, or for subsequent statements.

SELECT B. --< nothing here
FROM dbo. --< works here
WHERE B. --< nothing here
;

SELECT
FROM dbo. --< nothing here
;

WITH cte As (SELECT 1 As a)
SELECT cte. --< works here
FROM cte --< works here
WHERE cte. --< works here
;

SELECT
FROM dbo. --< still nothing here
;

Answer 19

Same thing here. It's actually even more annoying - intellisense works for one and only one statement. if there is another statement in the 2nd row - it won't work in the first row neither :(

Answer 20

For me the same problem, also after installing cu6

Answer 21

The only workaround so far seems to be:

Uninstalling from the Control Panel:

"Miscrosoft SQL Server 2008 R2 Data-Tier Application Framework"
"Miscrosoft SQL Server 2008 R2 Data-Tier Application Project"
"Miscrosoft SQL Server 2008 R2 Transact-SQL Language Service"

and reinstalling:

\WCU\DAC\DACFramework_enu.msi
\WCU\DAC\DACProjectSystemSetup_enu.msi
\WCU\DAC\TSqlLanguageService_enu.msi

from the original Visual Studio 2010 DVD.

This will bring the versions of the 3 components above from 10.50.1750.9 (or 10.50.1765.0 if you made the mistake to install the SQL Server 2008 R2 CU6) to the original 10.50.1447.4.

Answer 22

The only workaround so far seems to be:

Uninstalling from the Control Panel:

"Miscrosoft SQL Server 2008 R2 Data-Tier Application Framework"
"Miscrosoft SQL Server 2008 R2 Data-Tier Application Project"
"Miscrosoft SQL Server 2008 R2 Transact-SQL Language Service"

and reinstalling:

\WCU\DAC\DACFramework_enu.msi
\WCU\DAC\DACProjectSystemSetup_enu.msi
\WCU\DAC\TSqlLanguageService_enu.msi

from the original Visual Studio 2010 DVD.

This will bring the versions of the 3 components above from 10.50.1750.9 (or 10.50.1765.0 if you made the mistake to install the SQL Server 2008 R2 CU6) to the original 10.50.1447.4.

This does not work me.  Same result as with CU6 installed, only getting intellisense for tables.   Apparently it will be fixed in CU7 which is scheduled for mid-april.

Answer 23

It looks like Microsoft has confirmed this issue, and will hopefully have a fix ready for CU7. This was posted via Connect (https://connect.microsoft.com/SQLServer/feedback/details/650569/ssms-2008-r2-is-losing-intellisense-after-installing-visual-studio-2010-sp1):

Posted by Jay Hamlin - MSFT on 3/21/2011 at 10:35 PM

The SQL team made a change to the way an error message is constructed in the TSQL Language Service in Dev10 SP1. This change caused a loss of Intellisense when SSMS SQL Server 2008 R2 and Dev10 SP1 are installed. The team is issuing a fix that will be available in an upcoming SQL Server 2008 R2 CU (CU#7 scheduled for mid-April) and in SQL Server 2008 R2 SP1.

 

---

Richard Glidden - Brock Solutions - www.brocksolutions.com - Engineering Solutions that Perform.

Answer 24

Either Connect is having serious problems, or someone's deleted that bug report:

"The content that you requested cannot be found or you do not have permission to view it.
https://connect.microsoft.com/SQLServer/feedback/details/650569/ssms-2008-r2-is-losing-intellisense-after-installing-visual-studio-2010-sp1"

"You searched for: intellisense visual studio 2010 sp1
We could not find any results that matched your search."

Answer 25

SQL Server 2008 R2 CU7 is now available:  http://support.microsoft.com/kb/2531482

Answer 26

SQL Server 2008 R2 CU7 is now available:  http://support.microsoft.com/kb/2531482

Installing CU7 solved the problem for me.  Thank you.

Answer 27

While the numbered steps could help in general the user is experiencing a Microsoft bug (As did I).  Uninstalling Visual Studio SP1 is not a viable solution.

 

I did however install Cumulative Update 8 and this resolved my problem, though obtaining the download link for this update was difficult and there was no mention of any intellisense fix in the article.

 

Please make the intellisense fix documentation more available, and please make it easier to download the cumulative update so others don't have to go digging for answers to this bug.  

Answer 28

Update: SQL Server 2008 R2 SP1 is now available, and it does contain the fix for this issue.

---

Paul Keister http://www.pjpm.biz

Answer 29

The Cumulative Update 9 http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=2567713&kbln=en-us solved the problem.

 

I had tried the CU6, 7, 8, SP1 and all other proposed options and none worked. However, CU9 completely fixed the issue. It shows me the tables, columns, etc.

Answer 30

This do work for me, do appreciate, it has confused me for a long time.

Answer 31

The following link discusses another update that will break intellisense.  GDR1617 - KB2494088.  Uninstalling this update allows intellisense to function once again. 

http://darrenmyher.wordpress.com/2011/06/17/microsoft-sql-server-update-gdr-1617-leaves-sql-server-unusable/