September 17, 2014 at 9:54 am
I have an issue in SSMS that I can't figure out.
In SSMS, connected to an SQL Server 2012 instance (Enterprise 64-bit, SP2), open new query window, connect to one database and Intellisense works fine, but on another database, same query window, same server, it does not work:
-- Works:
USE XXX;
GO
SELECT * FROM [
-- Does not work:
USE YYY
GO
SELECT * FROM [
@@Version
Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
May 14 2014 18:34:29
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
ProductVersionProductLevelEdition
11.0.5058.0SP2 Enterprise Edition (64-bit)
September 17, 2014 at 9:58 am
What version of SSMS are you running?
What is compatibility level of database(s) you don't see intellisense work?
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
September 17, 2014 at 10:36 am
SSMS is 2012 also. We have a couple developers using SSMS 2014.
This appears to be the case for several different users for only these databases on this server, running on separate workstations.
All databases are SQL Server 2012 (110), Full Recovery, SQL_Latin1_General_CP1_Cl_AS
I've checked that 3 times now.. my first thought.
I'm curious whether there is something due to the SSMS 2014 clients that's happening.
Another interesting thing is that I can type in a query on a bogus table and not get the red underline, but when I change the database using the toolbar control, then refresh local cache, then I get the red line..
September 17, 2014 at 10:46 am
Also, the users affected are connecting using Windows Authentication, and are members of an AD group that has been granted SYSADMIN on the db server in question, and I ensured that they had no database-level permissions.
I can query system views on the db that won't do Intellisense, does not seem to be a permissions issue. Also, I made sure the database Containment type is NONE.
The database in question is for our development environment. The same database in our test and production environments work fine with Intellisense. However, this issue just showed up a few days ago, and the last refresh (from production) of this database was done last month. Nobody here uses VS projects, they query the database using SSMS only.
September 17, 2014 at 11:04 am
SSMS > Help > About, are you still running the RTM version of SSMS or is it patched?
Intellisense is a thing that always will cause problems, and never has an explanation why it stops or why it starts working again. Making sure it is patched to current service pack or CU can sometimes fix little nuances. In general I would take one machine and reinstall SSMS, completely remove all previous versions or installs and start over.
If you have multiple versions of SSMS installed it can cause issues at various times, at least for me. I had to rebuild my client VM to get SSMS 2012 to function correctly, F7 would not open object details for some reason. After reinstall it worked fine.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
September 17, 2014 at 12:25 pm
Microsoft SQL Server Management Studio11.0.5058.0
Microsoft Analysis Services Client Tools11.0.5058.0
Microsoft Data Access Components (MDAC)6.1.7601.17514
Microsoft MSXML3.0 5.0 6.0
Microsoft Internet Explorer9.11.9600.17239
Microsoft .NET Framework4.0.30319.18444
Operating System6.1.7601
I'm used to the Intellisense being pretty quirky.. It's just a bit diabolical that the one database we have that it doesn't work for is the DEV database we deal with mostly!
I doubt that this is related to the SSMS installation. I did recently install SP2 on this server, and it's quite possible that it's been broken since that update, however, that doesn't explain why the other 8 databases (plus 4 system dbs) all work fine, the problem is isolated to this one database on one server out of 22 servers hosting 395 databases, not to mention the clients connecting to it have various versions of SSMS, 2012, 2014, some patches, some not. Same with Visual Studio.. we all have varying versions of 2010, 2012 and 2013.
I think I may open a ticket with Microsoft on this. I just wanted to see if anyone was aware of any database specific setting that would effectively make Intellisense not work. I haven't seen any other issues with the database. We will probably end up doing a refresh of the environment and see if that cures the issue.
September 17, 2014 at 12:34 pm
bartedgerton (9/17/2014)[/bI think I may open a ticket with Microsoft on this. I just wanted to see if anyone was aware of any database specific setting that would effectively make Intellisense not work. I haven't seen any other issues with the database. We will probably end up doing a refresh of the environment and see if that cures the issue.
Have you compared the database settings between one that works and the one that doesn't?
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
September 17, 2014 at 12:41 pm
Quick thought, when the IS is not working, try pressing CTRL+SHIFT+R to refresh the cache, also check the Tools-Options-Text Editor-Transact SQL-IntelliSense memory settings, might want to increase that.
😎
September 17, 2014 at 2:07 pm
the working and non-working databases have identical settings.
I had the Max Script size set to 1MB, but it doesn't matter what it is set on, does not help. All other IntelliSense options are checked.
September 17, 2014 at 2:27 pm
I've had similar issues when working on servers in DMZ or other network restricted "areas", sometimes adding a host record or an cliconfg alias has helped but not always.
😎
September 18, 2014 at 2:30 pm
I have similar issues with Intellisense on SQL Server 2008 and 2012.
Sometimes it works, sometimes it works ....v..e...r..y.......s..l...o...w...l...y and sometimes it just plain refuses to do anything.
I can have multiple tabs in a SSMS window and in tab 1 IS works fine, in tab 7 IS won't work at all. Both tabs are hitting the same database.
December 4, 2014 at 1:30 pm
I can tell there is a slight hesitation the first time I type in a query in a new query window at the time when it should be popping up with Intellisense, however, it never does come up with anything, and CTRL-J (list members) has no effect.
Of 22 servers, each with a half-dozen or more databases, only this database on 1 server is not allowing Intellisense. I can open up a query window on this server/database and not have Intellisense work, but then use the same query window, but change connection and/or change database, refresh the cache (CTRL-SHIFT-R) and Intellisense works.
This wouldn't be such a big deal, but the "1 database" happens to be our main DEV database, where we need it the most (the same database in our TEST and PROD environments does not have the issue!).. in the words of Vizzini.. "Diabolical!"
Anyway, the server is fully patched to the latest version, as well as our client machines. Everything has SQL Server 2012 SP2. We are running a mix of developer clients on SSMS 2014 or 2012, and Visual Studio 2010, 2012 and 2013. I even tried to create a VS2012 database project to see if I could "rebuild" the Intellisense cache (no luck, but VS2012 Intellisense works fine for this db).
Even after doing a drop and restore from a PROD backup of the same database, Intellisense will not work on the DEV server, but still works fine in TEST and PROD. The other 8 databases on our DEV server have Intellisense working fine.
The server environment is a Windows Failover Cluster on 3 servers running Windows Server 2008R2, SQL Server 2012, and the databases in question are in an AlwaysOn Availability Group. The secondary server is read-only, and I can connect to it and it also exhibits the same problem for the same database. The other databases in the same Availability Group are working fine on both Primary and Secondary servers.
December 5, 2014 at 5:58 am
I am seeing a new behavior in 2012.
Intellisense will pop up, but the drop down list is blank. There are entries in the drop down list that you can choose from, but you just can't see what you are choosing. Weird.
December 5, 2014 at 8:51 am
You may be able to fix that issue by flushing your IE cache.
February 18, 2016 at 1:57 am
I encounter a similar issue. I solved it by removing a linked server that was no more online.
I saw that when i was running a check against the sys.sysprocesses view, intelisense was waiting for a ressource and it was the regarding linked server.
Hope it helps.
Regards,
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply