Intellisense not working in SQL Server Management Studio

by Victor Feinman

It was bothering me that the intellisense in SQL Server Management Studio was not working on my computer. I sought after how to achieve this feat and was please to find that it’s actually an easy (kinda) fix. See some other guy’s Stack overflow question about this issue. It’s actually a well known problem that occurs when you install Visual Studio 2010 SP1 as described on Microsoft’s site, in the Cumulative Update package 7 for SQL Server 2008 R2 knowledge base article.

These solutions have worked for others out there and if you don’t have any errors on installation, it’ll work for you too:

  1. Download and apply SQL 2008 R2 Service Pack 1
  2. Install Cumulative Update package 7 for SQL Server 2008 R2 by requesting
    SQLServer2008R2_RTM_CU7_2507770_10_50_1777_x86
    OR
    SQLServer2008R2_RTM_CU7_2507770_10_50_1777_x64
    from this hotfix request page.

As soon as I downloaded and tried to apply SQL 2008 R2 Service Pack 1, I got an error during installation. I had hoped that the cumulative update hotfix wouldn’t fail. WRONG! I was getting a similar error:

TITLE: SQL Server Setup failure.
——————————
SQL Server Setup has encountered the following error:

A failure was detected for a previous installation, patch, or repair for instance ‘MSSQLSERVER’ during configuration for features [SQL_Replication_Core_Inst, SQL_Engine_Core_Inst, MPT_AGENT_CORE_CNI, SQL_FullText_Adv,]. In order to apply this patch package (KB2507770), you must resolve any issues with the previous operation that failed. View the summary.txt log to determine why the previous operation failed.

A failure was detected for a previous installation, patch, or repair during configuration for features [SQL_DTS_Full, SQL_SSMS_Full, SQL_SSMS_Adv, SQL_PowerShell_Tools_ANS,]. In order to apply this patch package (KB2507770), you must resolve any issues with the previous operation that failed. View the summary.txt log to determine why the previous operation failed.

Error code 0x84B20001.

Googling “In order to apply this patch package (KB2507770), you must resolve any issues with the previous operation that failed” came up with some attempts to fix the error…

It looks like your previous SQL 2008 installation is in pending state. Please go to control panel, and select SQL Server 2008 (click on Change components and follow the wizard) and then try to complete the pending installation. Once its done proceed with SP1 installation. - Vidhya Sagar on phwinfo.com

Tried doing that, and when I clicked uninstall/change/repair, it asked for the location of the installation media – which I don’t know where is, so I dumped this approach. It was kinda vague anyway.

And this less than ideal proposal I didn’t even try:

I eventually did a remove/re-install then updated to Service Pack 1, which bypassed the need to install this update. - Derek on sqlservercentral.com

One thing still bugged me though, in the error it says to view the summary.txt log to determine why the previous operation failed. WHERE THE HECK is the summary.txt log??? Found it! %programfiles%\Microsoft SQL Server\100\Setup Bootstrap\log - Microsoft on connect.microsoft.com

Let’s take a look at the log… err – maybe not, doesn’t look like I can really use anything in there.

I continue my search from my last point connect.microsoft.com and on the Workarounds tab I see something interesting.

Nicholas Seems to think doing this solves the error. I was weary of this approach since it involved deleting and re-installing software so I didn’t even attempt it:

This seems to be a bug with the 2008 installer whenever you do an Edition Upgrade, such as if when you forgot to install Management Studio Express and did an edition upgrade to get it. The SQL Sever 2008 installer appears to leave things in an inconsistent state after doing an Edition Upgrade.

To work around this bug that Microsoft seems to think is not a bug:

Step 0. If you have Visual Studio 2008 SP1 installed, you MUST first remove “Microsoft SQL Server Compact 3.5 SP1 <Language>” via Add/Remove Programs as described in http://support.microsoft.com/default.aspx/kb/970876. (It will get re-installed later during the repair; this works around yet another bug in SQL Server 2008′s installer.)

Step 1. Download and run the original self-extracting installation of SQL Server 2008. If you have installation media, you can use the “SQL Server Installation Center” option in your Start menu.

Step 2. Choose Maintenance > Repair. When asked what to repair, choose “<<Repair shared components only>>”, then let the repair complete. Then close the SQL Server Installation Center. (You shouldn’t have to repair any instances.)

Step 3. You should now be able to run the SP1 installer.

Good luck! Here’s hoping that they rewrite the entire installer in 2010.

On the same page/tab, NedleyFlanders throws out:

Due to upgrading from dev to full version, I had previously had to set HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\ConfigurationState\CommonFiles to the value “3″ in order for the upgrade to work … Setting the value back to the original (1) value allowed the patch to proceed and finish.

OK… Let’s give this a try.

In my registry, I changed all DWORD values to 1 in the following Keys: (As always be careful modifying the registry and create a backup of the key before changing anything)

HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\100\ConfigurationState
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\ConfigurationState
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\ConfigurationState
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQLEXPRESS\ConfigurationState
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\ConfigurationState

After modifying my registry, I retried the SQL 2008 R2 Service Pack 1 installation and much to my delight and surprise (I was getting used to being let down) it completed successfully. Note this installation takes a long time, this is normal. After installation was complete, I started SQL Server Management Studio and again elation as intellisense was restored!

Tags: , ,

2 comments

  1. Thanks Victor this is extactly what I wanted. Although I did not have to do the Registry stuff. It just worked after that.
    I removed compact FW SP2 and did sql repair on shared components and installed CU7.

Leave a comment