none
Intellisence and design editor not working in any ver of SSMS and Azure SQL RRS feed

  • Question

  • how do I get intellisence and design editor working in ssms 17.6 and azure sql?? This is driving me crazy.

    That being said it has not worked for me on any dev machine in any ver of SSMS on azure sql. It does work fine on local db's.

    Thank you for any help.

    Friday, March 23, 2018 2:26 PM

All replies

  • Friday, March 23, 2018 2:40 PM
  • Still does not work with 17.2, 17.4, 17.5, 17.6.

    See the attached screenshot using 17.5 connected to an azure sql db.

    Friday, March 23, 2018 3:12 PM
  • What is the compatibility level of your database?

    Josh

    Saturday, March 24, 2018 12:26 AM
  • Good day Mike,

    Please check Sanjay Nagamangalam last response in this thread:
    https://social.msdn.microsoft.com/Forums/en-US/fd1ad07e-cd3c-47b8-a89d-6e691f206320/intellisense-not-working-with-azure-sql-in-ssms-172?forum=ssdsgetstarted

    Give us here all the information that he asked there, and we will continue from that point


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Sunday, March 25, 2018 9:51 AM
    Moderator
  • Using sql authentication. 4 db's.

    3 containing 108 tables and 8 views.  S3 standard, 100 dtu's.  With 250gigs of storage.

    1 containing 5 tables and 0 views. Basic.

    Everything else seems to work as expected in ssms.

    Intellisense never works not even after having it open and waiting for hours.

    I am targeting the <default> db during login, however targeting just the db with only 5 tables does not change the outcome.

    Thank you




    Sunday, March 25, 2018 1:23 PM
  • 1. Can you check if the the behavior changed if you move to higher tier

    * I assume the issue is not related to the SSMS side since you tested several versions which all should work well.

    * It might be related to slow network or tier limitation. When you use IntelliSense then behind the scene the client app (SSMS in this case) actually send queries to the server from time to time. The information is stored in the client cache.

    2. Click on "CTRL+SHIFT+R" please in order to refresh the cache, and check again

    off-topic: more SSMS Keyboard Shortcuts can be found here.


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]



    Sunday, March 25, 2018 7:41 PM
    Moderator
  • I have tried the forced cache refresh with the CTRL SHIFT R in the past on both my machines and it yielded nothing different.  I will attempt to increase the dtu's first thing in the morning and let you know. 

    Thank you for your help.

    Mike

    Monday, March 26, 2018 12:37 AM
  • OK, I have doubled from 100DTU's to 200DTU's and am now in a standard S4 tier.  Still no intellisense.  Also tried the cache refresh, CTRL+SHIFT+R, again with no luck.

    Is there a recommended tier I should try?

    Thank you




    Monday, March 26, 2018 5:04 PM
  • Some new info on the intellisense front!

    I have found that if I log in with the Master Login for the DB that was created during the DB creation in the Azure portal, I get intellisense.  But not if I login with a created user it still does not work.  I am not a DBA, however find myself having to get by from time to time as in this case, is there anything I should look for in the users that would enable intellisense? 

    Thank you

    Mike


    • Edited by MikeDCooper Tuesday, March 27, 2018 3:01 PM
    Monday, March 26, 2018 7:22 PM
  • Hi Mike,

    This explain everything and this is the expected behavior ;-)
    * Expected does not mean desired behavior

    Do you remember I said that intellisense based on queries behind the scene, and that SSMS collect the data into the client cache? And that we can use Ctrl+Shift+R to refresh the cache?

    Let's put it all together with the fact that you are using LOGIN who is not sysadmin:

    As much as I understand (after monitoring the queries behind the scene) Your LOGIN does not have permissions to execute some of the queries which are outside the scope of the database. Once this fail SSMS does not bring the intellisense even if LOGIN have permissions to the relevant element that you need for the current specific intellisense (for example list of tables). It is simply that SSMS look to catch all the information and it has no permission for the master database

    In local server any user have permission to the master.

    You can use third party applications that works well with the Azure even for simple LOGIN you created, since they based on queries that only pick the relevant data (you will need to give the LOGIN the right permissions on these elements).

    * for the sake of the discussion you can connect to local SQL Server -> start the profiler -> click on Ctrl+Shift+R and you can see that SSMS actually collect all the information and not only what we need for the specific intellisense  of specific table for example. This mean the LOGIN/USER needs all the permission on these elements, but thse element does not under your control in Azure. By the way you can't use profiler for the azure database as it is not supported as well :-)

    I hope this explain your issue, and now the future is in the hand of the developers of the SSMS. Maybe with small change in design this will work and maybe if I had the exact query which needed we could find the permission which we need to give the USER

    IMPORTANT! all the information here is based on testing and assumptions and might not be 100% full and accurate :-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Wednesday, March 28, 2018 6:48 AM
    Moderator
  • Thank you so much for your help, I do appreciate it.

    When I open the same db locally open a new query window, then press Ctrl+Shift+R I get 83 rows in the profiler.  Is there something specific I should look for?

    Mike

    Wednesday, March 28, 2018 12:40 PM
  • Well, I took it to the next step ;-)

    I sent email to several people from the develop team of the SSMS, and I already got a response. I requested to fix this issue. In public I can only tell you that this is now in discussion and if I'll get any information, which I will be allowed to share (publish) in public, then I will bring it.

    Regarding your question: this is exactly what seems to be the issue. SSMS execute multiple queries on multiple elements, which part of these element simply does not exists (not available is more accurate, since we are using virtual instance when we use Azure SQL Database). I did not check this in depth and I did not check all the queries in depth, since it's taking much longer time of what I can spend now on this. 

    As I mentioned at this time in your place, I would probably chose one of the third party extension, or wait  :-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Wednesday, March 28, 2018 10:53 PM
    Moderator
  • There is only a single vote for IntelliSense not working in SSMS 17.5 posted to User Voice.

    User Voice is the best way to communicate to the Product Group, and with sufficient votes, it sends a clear message as to what is desired.

    Thursday, March 29, 2018 3:53 PM
    Moderator
  • Thank you again for all your help.
    Thursday, March 29, 2018 5:08 PM
  • I have added my 2 cents. thank you
    Thursday, March 29, 2018 5:09 PM
  • You are most welcome ;-)

    and thanks for reporting this issue +5


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Thursday, March 29, 2018 11:14 PM
    Moderator
  • There is only a single vote for IntelliSense not working in SSMS 17.5 posted to User Voice.

    User Voice is the best way to communicate to the Product Group, and with sufficient votes, it sends a clear message as to what is desired.

    In general you are right and this is the way people should report issues (bugs)

    with that being said

    1. The thread misleading a bit.

    1.2 It's speaking about specifics version, while this is true to any version at thie time

    1.3 It's speaking about any connection to the Azure Database, while this issue is only when you connect with a user that is not a sysadmin

    2. Some of us has the prevalage of knowing the developers in person or simply have a direct communication channel and they are lazy to open a connect when can simply pick a phone or send email :-)

    but again, you are correct and using the official feedback system is the right way to go

    Thanks for adding the link to the connect case
    +5


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Thursday, March 29, 2018 11:24 PM
    Moderator
  • Hi Ronen & Mike,

    I've scoured the web for information about this issue as it has been driving me mad also. Ronen, I found your advice above to be the most helpful I've found anywhere, and I'm eager for MS to resolve this issue.  I've taken your advice and added a vote as a handful of others have.

    The weird thing is that the bug was raised, and now appears to be "COMPLETED".  I have the latest release of Azure Data Studio (v1.3.8) and I see no difference in behaviour.

    Is there something I need to do in order to obtain the fix?  Are you both still waiting for a fix?

    Thanks,

    _Nick

    • Edited by Nick Allan Saturday, January 12, 2019 11:55 PM
    Saturday, January 12, 2019 11:54 PM
  • Hi Ronen & Mike,

    I've scoured the web for information about this issue as it has been driving me mad also. Ronen, I found your advice above to be the most helpful I've found anywhere, and I'm eager for MS to resolve this issue.  I've taken your advice and added a vote as a handful of others have.

    The weird thing is that the bug was raised, and now appears to be "COMPLETED".  I have the latest release of Azure Data Studio (v1.3.8) and I see no difference in behaviour.

    Is there something I need to do in order to obtain the fix?  Are you both still waiting for a fix?

    Thanks,

    _Nick

    Good day Nick

    1. This is VERY old thread and it is not recommended to continue a discussion after a year.Things change to fast to keep it relevant usually. Moreover, it is recommend to open your thread which you can control and close at the end.

    2. Ronen, I found your advice above to be the most helpful

    Thanks :-)

    3. The weird thing is that the bug was raised, and now appears to be "COMPLETED".

    As I mention above "The thread misleading", where I am talking about the "bug report", which is NOT related to this issue but to general uses of Intellisense, which was fixed and therefore COMPLETED.

    4. I have the latest release of Azure Data Studio (v1.3.8) and I see no difference in behaviour.

    This is totally not relevant to this thread. This thread is about SSMS which is totally different application from Azure Data Studio.


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Sunday, January 13, 2019 12:58 AM
    Moderator
  • The weird thing is that the bug was raised, and now appears to be "COMPLETED".  I have the latest release of Azure Data Studio (v1.3.8) and I see no difference in behaviour.

    Azure Data Studio is an entirely different tool than SSMS. This thread is about SSMS Intellisense. Submit ADS specific issues on GitHub: https://github.com/Microsoft/azuredatastudio/issues


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Sunday, January 13, 2019 1:10 AM
  • Hi Ronen,

    Thanks for your reply.  I'll take your advice re: the age of this thread - not quite a year old yet ;)

    I'll create a new thread and see if anyone has any fresh ideas.  However, I have found identical behaviour in all of these environments:

    • SSMS 17.9.1
    • SSMS 18.0 Preview 6
    • Azure Data Studio v1.3.8

    The behaviour being exactly as you describe, using a user created LOGIN rather than a sysadmin LOGIN.  Note: "contained database" USERs appear fine, so I'll likely use this workaround for now.

    Cheers!

    _Nick


    • Edited by Nick Allan Monday, January 14, 2019 10:50 AM
    Monday, January 14, 2019 9:38 AM
  • Will do Dan, thanks!  _Nick
    Monday, January 14, 2019 9:39 AM