locked
Database References and Intellisense

    Question

  • Should intellisense work for a database I am referencing in another project. E.g. I have database project A referencing database project B, and the target database name of project B is simply "B", should I get intellisense for example when typing this:

    Select * From B.(intellisense activates here?)

    Or is intellisense for referenced database project something that is not supported?

     

     

    Wednesday, November 17, 2010 9:40 PM

Answers

  • That is what I mean with does not work. The way database references are resolved is by loading the object names in to the project namespace, while doing this you lose the database name, since IntelliSense is sourced from the schema model of the project it does not know either, hence my statement that it does not work (as expected).

     


    GertD @ www.DBProj.com
    • Marked as answer by AaronLST Tuesday, December 28, 2010 7:57 PM
    Tuesday, December 28, 2010 7:17 PM

All replies

  • Hello AaronLST,

    Thanks for your post.

    When the file you are editing with the Transact-SQL editor is associated with a database project, IntelliSense is available. That is to say, the IntelliSense for referenced database project is supported.

    However, IntelliSense is not active when SQLCMD mode is enabled. So, to deal with your issue, please check out your SQLCMD mode is disabled. Open your T-SQL editor, right click and disable the SQLCMD mode. After you disabling the SQLCMD mode, the IntelliSense should be activated.

    I hope it helps you with your issue. Let me know if you have any further concerns.

    Thanks,

    Vicky Song

    ________________________________________

    Please remember to mark the replies as answers if they help and unmark them if they provide no help

    Thursday, November 18, 2010 6:13 AM
  • The intellisense works for the project A, but not when quering against the referenced project B.

    For example, I have a stored procedure script file in project A.  I open the script to edit, sqlcmdmode is not enabled, but the intellisense when querying against referenced project B does not work.

    I have the database reference for Project B setup as a database literal of the same name, B.  Thus I am using a 3 part naming convention, and type something like:

    Select * From B

    or even

    Select * From B.dbo.

    But neither works.  One thing I should note is project B has a few hundred tables.  Project A only has about 10 tables.  When I am using intellisense for project A, it often freezes up for 10-20 seconds.  I kind of wonder if it's choking on there being so many tables.

     

    Thursday, November 18, 2010 9:13 PM
  • Hello AaronLST,

    Sorry for the late reply.

    IntelliSense functionality does not cover database objects created by another connection after your editor window connected to the database. You can choose one of these three mechanisms to refresh the cache of objects for your editor window.

    1)       Select the Edit menu, select IntelliSense, then select Refresh Local Cache.

    2)       Use the CTRL+Shift+R keyboard shortcut.

    3)       Disconnect your editor window from the instance of the Database Engine and reconnect.

    This time you should enable the IntelliSense functionality.

    In addition, this article describes these conditions when IntelliSense is unavailable.

    http://msdn.microsoft.com/en-us/library/ms173434.aspx

    Thanks,

    Vicky Song

    ________________________________________

    Please remember to mark the replies as answers if they help and unmark them if they provide no help

     

    Wednesday, November 24, 2010 2:02 AM
  • Ok this may be a digression from the original question, but I don't think it's too bad.

    I just applied VS2010 sp1 beta and now intellisense isn't working on a database project (against a sql 2005 database). The intellisense worked great before I applied the service pack. Any ideas?

    Thursday, December 16, 2010 6:19 PM
  • What you describe doesn't make sense because none of my editor windows are connected to a database, and never have been.  I have a feeling you misunderstand the context of my question when you start talking about the editor window being connected to a database.  When working with a database project, intellisense is not based on a database to which you are connected, but is instead based on the contents of the database project.

    I do get intellisense when editing a file in the same project that it references.  E.g. if editing a file in project A, then references to database A have intellisense, but references to project B do not, even though there is a reference to projet B.

    I am describing a scenario where I have two database projects, and none of my editor windows are connected.  In such a scenario, I do have intellisense for the project in which I am editing a file, e.g. in project A if I am editing a file under project A.  I would expect however, that when referencing project B from project A, then references to project B should also have intellisense.  This would be intuitive and expected behavior to me simply based on the fact that any other type of visual studio project(C# app. etc.) would have the same behavior.

    I came back here to share something I discovered about this problem that was quite odd.  If I don't fully qualify the table name when referencing a table in project B, then intellisense works. For example:

     

    select *

    from A.dbo.someTableInA as inA

    inner join B.dbo.someTableInB as inB on inB.(no intellisense)

    Yet if I don't fully qualify the name:

    inner join someTableInB as inB on inB.(I get intellisense)

    But this doesn't solve the problem, because this will not successfully compile/deploy/run because someTableInB does not exist in project A, and therefore must be fully qualified with a 3 part name.  It seems like a bug in how references are handled in terms of intellisense.

     

    Wednesday, December 22, 2010 4:10 PM
  • I am pretty certain that cross database T-SQL IntelliSense does not work for Database Projects in VS2010 (disconnected mode), when using the editor in connected mode it should behave the same way as in SSMS.

     


    GertD @ www.DBProj.com
    Monday, December 27, 2010 9:46 PM
  • I am pretty certain that cross database T-SQL IntelliSense does not work for Database Projects in VS2010 (disconnected mode), when using the editor in connected mode it should behave the same way as in SSMS.

     


    GertD @ www.DBProj.com
    Perhaps you are right, but if you read to the end of my previous post you'll see that it does work, but not as expected.  I get intellisense for the tables in the other referenced database project, but only if I don't qualify it with the database name.  So it is as though it is interpreting the reference to the other database as if the tables existed in the same database.
    Tuesday, December 28, 2010 7:01 PM
  • That is what I mean with does not work. The way database references are resolved is by loading the object names in to the project namespace, while doing this you lose the database name, since IntelliSense is sourced from the schema model of the project it does not know either, hence my statement that it does not work (as expected).

     


    GertD @ www.DBProj.com
    • Marked as answer by AaronLST Tuesday, December 28, 2010 7:57 PM
    Tuesday, December 28, 2010 7:17 PM