locked
Visual Studio 2010 Database Reference and Four Part Names

    Question

  • Hi,

    I have two Visual Studio 2010 database projects:

    frame1 is a database project (named: frame1) that contains the schema for a database owned by a third party and which runs on a different server from my own database. All the schema object reside in the [dbo] schema.

    Sky is a database project that I own and which contains views that reference tables in the frame1 database using four part names eg. [LDW].[frame1].[dbo].tablename . I have added a database project schema reference to the frame1 database. I have also added a linked server for LDW.

    If I do not use database reference variables, all the occurrences of the four part name references to [LDW].[frame1] in the Sky database views generate unresolved references errors (3006).

    If I do use database reference variables, everything builds fine.

    I would prefer not to use database reference variables, so my question is:

    Is it possible to configure the projects, references and linked server definition so that I do not need to use database reference variables and if so what is the best way to do this ?

    Thanks

     

     

     

    Thursday, October 07, 2010 3:26 PM

Answers

  • Hello Graham,

    Sorry for the late reply.

    Using the SETVAR variables is very convenient. For example, for each database to which you refer, you can define SETVAR variables that correspond to the server and database to which you want to refer. When you modify an object definition to include the reference, you can define it by using these variables instead of the explicit names of the server and databases.

    For your scenario (reference schema on a different server), just as you said, you must define a linked server on your target server first. And at the same time, you need to specify both the database and server variable. Then the reference is added to the database project, and those objects and scripts that you specified are modified to use the variables instead of the names of server’s and database’s.

    Note: 1). You do not need to define a server variable if you are referring to an object in a database that resides on the same server as the database to which you are adding the reference.
    2). Names of SETVAR variables must be unique. If your server has the same name as your database, you must define variables that have different names but resolve to the same value.

    And for what situation you need to copy-paste the schema object script between VS2010 and SSMS? If you want to synchronize the database project with the source database, you can use the Schema Compare. And if you want to import single object, you can use Import Script…

    Thanks


    Edwer Fang [MSFT]
    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg @ microsoft.com

    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Tuesday, October 12, 2010 5:53 AM
    Moderator

All replies

  • Hello Graham,

    Thanks for your post.

    Based on your issue, as far as I know the best way to manage four-part name references inside a database project is using the database reference variables. I don’t think you can manage these four-part name references by configuring the projects, references or linked server instead of the database reference variables.

    Thanks.


    Edwer Fang [MSFT]
    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg @ microsoft.com

    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Friday, October 08, 2010 5:36 AM
    Moderator
  • Hi Edwer,

    thanks for your reply.

    The reason I would like to avoid database reference variables is that it is very convenient to be able to copy-paste the schema object sql between VS 2010 and SSMS without needing to make changes (ie. replacing the database reference variables).

    I wonder if synonyms would help here ?

    Also, I have found a somewhat old reference here: http://blogs.msdn.com/b/gertd/archive/2007/07/26/database-references.aspx

    which contains an aside:

    NOTE: You might not have an error, because you have a local copy of AdventureWorks on your local SQL Server instance which is used for design time validation.

    Is there any more information on how the design time validation process works ?

    Many thanks

     

    Friday, October 08, 2010 8:24 AM
  • It seems that using a Database Variable literal has the effect of allowing the database name to be fixed in the schema object sql.

    So, I think what I am asking for is Server Variable literals ...

    Any thoughts ?

    Many thanks

     

    Friday, October 08, 2010 3:00 PM
  • Hello Graham,

    Sorry for the late reply.

    Using the SETVAR variables is very convenient. For example, for each database to which you refer, you can define SETVAR variables that correspond to the server and database to which you want to refer. When you modify an object definition to include the reference, you can define it by using these variables instead of the explicit names of the server and databases.

    For your scenario (reference schema on a different server), just as you said, you must define a linked server on your target server first. And at the same time, you need to specify both the database and server variable. Then the reference is added to the database project, and those objects and scripts that you specified are modified to use the variables instead of the names of server’s and database’s.

    Note: 1). You do not need to define a server variable if you are referring to an object in a database that resides on the same server as the database to which you are adding the reference.
    2). Names of SETVAR variables must be unique. If your server has the same name as your database, you must define variables that have different names but resolve to the same value.

    And for what situation you need to copy-paste the schema object script between VS2010 and SSMS? If you want to synchronize the database project with the source database, you can use the Schema Compare. And if you want to import single object, you can use Import Script…

    Thanks


    Edwer Fang [MSFT]
    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg @ microsoft.com

    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Tuesday, October 12, 2010 5:53 AM
    Moderator
  • Hello,

    I will mark the reply as answer, if you find it no help, you can feel free to unmark it.


    Edwer Fang [MSFT]
    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg @ microsoft.com

    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Wednesday, October 20, 2010 3:48 AM
    Moderator
  • Hi 

    We have the same issue with the linked server references for our visual studio database project.  

    We do not want to use the database variables because we don't necessary want to use the visual studio to generate a change script for every deployment.  Since our dev environment can be modified by others outside of the visual studio.

    We are currently using VS2010.  Since you allow us to create a literal database variable, is there a work around for us to create an literal server variable?  If not, can we request a new feature to create a literal server variable?

    Thanks!

    Monday, February 06, 2012 2:57 PM