Why is IntelliSense not working?
I see people complain that IntelliSense (new in SQL Server 2008's Management Studio) is not working. Most of the time, it is for one of two reasons:
- The object is not in the local IntelliSense cache, since it was created recently. You can fix this easily by going to Edit > IntelliSense > Refresh Local Cache, or CTRL+SHIFT+R.
- The query window is connected to a downlevel server (e.g. SQL Server 2005), where IntelliSense does not function (see Connect #341872 from Whitney Weaver for more info and LOTS of community feedback on this decision :-)).
Little did I know, there are a number of other situations where IntelliSense will not function correctly (e.g. when you have switched to SQLCMD mode). I was also unaware, until today, that Alan Brewer at Microsoft created a Books Online page that documents these and other cases:
When IntelliSense Is Unavailable
So many thanks for this document Alan, as I think it will help others as well. What I'm still trying to figure out, though, is why there is a SQL Server 2005 version of the page, since IntelliSense does not exist in Management Studio for 2005, nor does it work in 2008 against 2005 instances.
Very nice article
Appreciate your help but this is soooooooooo frustrating. Can't microsoft provide a script (powershell, transact sql, or executable) that could diagnose this problem and tell what needs to be done to get intellisense working?????????? They are F*()'ing microsoft and wrote all this cr*((p.
Ctrl Shift R Nice!!
I have got troubles with this request generated by Intellisense in TempDB.
We generate and drop many #tables all the time and this request cannot be completed and always been blocked. It looks like it comes from ObjectExplorer somehow.
Let me know if someone have any idea how to suppress this behavior except of disabling this option. We do a lot of development and constantly switching between prod and test boxes.
SELECT tr.name AS [Name], tr.object_id AS [ID], CASE WHEN tr.type = N'TR' THEN 1 WHEN tr.type = N'TA' THEN 2 ELSE 1 END AS [ImplementationType],
CAST( tr.is_ms_shipped AS bit) AS [IsSystemObject],
CAST(CASE WHEN ISNULL(smtr.definition, ssmtr.definition) IS NULL THEN 1 ELSE 0 END AS bit) AS [IsEncrypted]
FROM sys.triggers AS tr LEFT OUTER JOIN sys.sql_modules AS smtr ON smtr.object_id = tr.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmtr ON ssmtr.object_id = tr.object_id WHERE (tr.parent_class = 0) ORDER BY [Name] ASC
I recently noticed asynch_network_io waits shoot up, and it appears that the cause is increased usage of intellisense…or there is something wrong. It is this query – any ideas?
(@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000))
SELECT SCHEMA_NAME(sp.schema_id) AS [Schema],
sp.name AS [Name],
sp.object_id AS [ID],
CASE
WHEN sp.type = N'P' THEN 1
WHEN sp.type = N'PC' THEN 2
ELSE 1
END AS [ImplementationType],
CAST(
CASE
WHEN sp.is_ms_shipped = 1 THEN 1
WHEN (
SELECT major_id
FROM sys.extended_properties
WHERE major_id = sp.object_id
AND minor_id = 0
AND class = 1
AND name = N'microsoft_database_tools_support'
)
is not null THEN 1
ELSE 0
END AS bit) AS [IsSystemObject],
CAST(
CASE
WHEN ISNULL(smsp.definition, ssmsp.definition) IS NULL THEN 1
ELSE 0
END AS bit) AS [IsEncrypted]
FROM sys.all_objects AS sp
LEFT OUTER JOIN sys.sql_modules AS smsp
ON smsp.object_id = sp.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmsp
ON ssmsp.object_id = sp.object_id
WHERE (sp.type = @_msparam_0
OR sp.type = @_msparam_1
OR sp.type=@_msparam_2)
ORDER BY [Schema] ASC,[Name]
ASC
The intellisense on SQL 2012 is crap, it works great on 2008 but this new version has soo many issues that i'.m going to remove it and put 2008 on, thanks!
Thanks good sir! Ctrl+Shift+R treats me right 🙂
check: http://support.microsoft.com/kb/2531482
Download and install
Microsoft® SQL Server® 2008 R2 Service Pack 1
Thx for the post…
I faced the same issue recently & proble resolved by install SQL 2008 SP1
Thx
http://www.mytricks.in/2011/11/fix-sql-server-management-studio.html
SSMS 2008 R2 is losing Intellisense after installing Visual Studio 2010 SP1
http://web.archive.org/web/*/http://web.archive.org/web/*/https://connect.microsoft.com/SQLServer/feedback/details/650569/ssms-2008-r2-is-losing-intellisense-after-installing-visual-studio-2010-sp1
Excellent Aaron!
Also it seems they went from a character/case order to a case/character order. Which makes it all most useless if you are in an environment that has changed their standard for CAPS to camel case.
Example:
TABLECUSTOMER201001
TABLECUSTOMER201002
TABLECUSTOMER201003
TABLECUSTOMER201004
All other CAP TABLES/VIEWS (could be hundreds )
TableCustomer201101
I used redgate's for years and then changed jobs and am back to microsoft's (which is why I am on this article). I like redgate's a lot but find microsoft's a pain in the rear.
What Dingo said about the period is definitely an annoyance. I also just upgraded to 2008 R2 and am annoyed at this loss of functionality.
Are there any solutions to this? I had initially assumed I accidentally disabled that function somehow…why would you take it out?
I've just upgraded my client tools from 2008 to 2008R2 (connecting to a 2008 R2 server) and there seem to be differences in the implementation of intillisense in Management Studio between the versions.
Pre-upgrade (plain old 2008) to accept a suggestion from autocomplete, you could type either a period, closing square bracket or space, this for me worked pretty well.
Post upgrade (R2) things are worse, now the period does not accept the suggestion (it instead just appears at the end of what you've typed), the closing square bracket will accept BUT but only if you have NOT started the object name with an opening bracket!! So autocomplete entries end up looking like either dbo]. or [db]
The functionality of the spacebar remains unchanged, but this is annoying if you're autocompleting a db/schema name as you then have to delete the space you've put in, before continuing with the table name.
A shame, I was getting used to autocomplete and it reduces the time taken to get used to a new, complex schema at a client site (I don't have a photgraphic memory and so need either a diagram or something like autocomplete before getting up to speed).
Turns out that references to fields for synonyms show up underlined in red also.
Thanks, this helped to solve an annoying problem I was having 🙂
Question: Can EZ-CAP 6.0 handle this? The dilemma is that the member only is added to EZ-CAP once the member has transferred to an IPA Central Health contracts with. But the Healthplan and Option are the same so according to the Healthplan, the benefit accumulators should carry over from the first IPA (that does not contract with Central Health) to the second IPA that does. If Central Health starts the member’s accumulators off at $0 they will not be accurately tracking the member OOP totals and the deductible/OOP maximums will over-charged to the member.
Thank you!
Peters comments pointed me in the right direction but did not solve my problem.
The issue I found was that I was granted permission to the database server as part of a windows group but my windows login had been added as a user to the database with no role.
I believe this occured during the execution of enterprise library scripts that led to a schema being created with my login name.
When these two items where removed intellisense resumed working.
In summary, check all your permissions are correct before looking elsewhere for a solution
The short story:
grant exec on xp_instance_regread to public
The long story:
We recently upgraded our user testing database to 2008. Intellisense did not work until we tried granting my user SA rights. This prompted us to search for the permission required. The MSDN articles insisted that you will be able to use Intellisense on objects that you have access to, yet I was unable to get Intellisense working on tables I could select from.
I then went to ServerName -> Security -> Logins and tried to view the properties of my login. I got an execute denied error for xp_instance_regread. It turns out that this solves another issue in SSMS 2008 when trying to right click on non-2008 databases. Granting access to this enabled Intellisense.
One of tools that offer Intellisense for SQL Server 2005 and 2000 is our DbOctopus. Nice thing is it's free until September, so there're no reasons not to give it a shot. It's not just SQL code editor, but you can see that if you visit:
http://www.cogin.com/dboctopus/
Adam I understand your points, and I am actually on the same side of the fence as you. For *ME*, IntelliSense (both the native version and RG's) simply gets in the way. But for a lot of people, the scales tip the other way… the nuisance is worth it.
Non-working Intellisense–which includes both Microsoft's and Red-Gate's implementations–wastes a lot more time than it saves. Although to be fair to RG, Microsoft's is much worse at this point in terms of getting in the way.
Microsoft's implementation autocompletes with candidates that don't make sense, greatly increasing the number of keystrokes needed in some cases (you need to back out the autocompletion).
RG's does not have that problem, but also doesn't support nearly as much–such as CTEs–and it has bugs where it will occasionally populate the candidate list with incorrect column names. It also often marks perfectly valid SQL as invalid, which does waste time. These comments, by the way, refer to the prerelease version of RG's newest offering in this space.
I hope that some vendor–RG, Microsoft, or other–will solve the Intellisense problem soon. I agree that it would save a lot of time, but the offerings today don't, and for me hurt rather than help my productivity.
What…"I was going to say that you should be happy if it's not working :-)"
You people got to be kidding me…
Use Red-Gate SQL Prompt and you will quickly grow to love intellisense…
Just to be able to do "select * from tableA" and have it fill in the names of the columns when I want is worth the price..
So what if intellisense not perfect.. it SAVES a huge amount of time…
-jfc-
There's a space on the end of the link for "When IntelliSense Is Unavailable". Should be this: http://msdn.microsoft.com/en-us/library/ms173434.aspx.
I frequently jump between 2005 and 2008 connections and lose bracket highlighting. I'm anxious to read this link. 🙂
I was going to say that you should be happy if it's not working 🙂
A timely post Aaron. I am getting the feeling that Intellisense is quickly going down in the "be careful what you wish for" column.