Using SQL Database Project

Unresolved References to Same Database Project

VSTS:DB does not support self-referencing 3 part names. Our guidance is to not use local 3-part names as it introduces an unnecessary layer of abstraction and dependency on the database name. 

For teams that use local 3 part names and would like to continue using local 3-part names can use the $(DatabaseName) variable to resolve local references. This would make your object references look like this: [$(DatabaseName)].[Schema Name].[Object Name]. 

To update all the local 3-part names to use a variable can be done through refactoring. To refactor all of your 3 part names to use the variable you do the following: 
1. Right click on your project and select Refactor -> Rename Server/Database References 
2. On the window, uncheck the box for “Replace the name of a server or server variable” 
3. Check the box for “Replace the name of Database or a database variable” 
4. Enter in the name of your database in the Old name field: ex “ThreePart” 
5. Enter $(DatabaseName) for the New Name 
6. Click OK 
7. Click Apply on the preview window 

VSTS:DB does support external 3 and 4 part references using variables and literals. The $(DatabaseName) variable is an ambient variable that will have its value replaced at the time of deployment. This variable gets its value from the project properties deployment tab. 

3-part name: [Local Database Name].[Schema Name].[Object Name] 
4-part name: [$(RefServer)].[$(RefDatabase)].[dbo].[Employee] 

Here is another approach to use local 3 part names: Create project snapshot(dacpac), and add this file as a database reference. Remember to clear database variable field in the 'Add database reference' dialog. It works however this approach is not recommended by Microsoft and can cause other problems


Visual Studio defines three variables for you that you can use in your object definitions and scripts. The values of these variables are set when you deploy the project. The first variable is $(DatabaseName), it and contains the name of the target database to which you are deploying. The second variable is $(DefaultDataPath), and it contains the path in which the files for the database are stored on the target server. The third variable is $(DefaultLogPath), and it contains the path in which the log file for the database is stored on the target server.

Using References in Database Projects

Manage Reference data 

 A better way would be to use the MERGE statement in the post-deployment script. The MERGE statement allows data to be inserted, updated, or deleted all in one statement.

MERGE PostTypes AS target

USING (

  VALUES (1, N'Question') ,

         (2, N'Answer') ,

         (3, N'Wiki') ,

         (4, N'TagWikiExerpt') ,

         (5, N'TagWiki') ,

         (6, N'ModeratorNomination') ,

         (7, N'WikiPlaceholder') ,

         (8, N'PrivilegeWiki')

) AS source ([Id], [Type])

ON (source.Id = target.Id)

WHEN MATCHED THEN

  UPDATE SET [Type] = source.Type

WHEN NOT MATCHED THEN

  INSERT ([Id], [Type])

  VALUES (source.Id, source.Type)

WHEN NOT MATCHED BY SOURCE

    THEN DELETE;

unresolved reference to Login

The SQL that generates this error: CREATE USER [userName ] FOR LOGIN [userName ];

You can change the create user scripts to create roles:  "Create Role [userName] authorization dbo;"

This is a hack, but as long as you aren't having to deal with users and roles in your project, you can happily do the following:
GRANT EXECUTE ON OBJECT::[dbo].[sp_name] TO [userName];


If you create application specific logins (which you should) then you are going to come across this error when trying to build your solution. To correct this error, select include 'Non-Application-scoped' object types in the options (gear icon at top) when you do a schema compare (right-click the database project to find Schema Compare). You can then just import the logins into your regular project, and the references are sorted. Note: If you click on the Object Types tab and it closes the dialog (which it did for me), instead use the tab key until Application-scoped is highlighted, then press the down arrow to highlight Non-Application-scoped and press the space bar. Now you should be able to click OK and see the logins.

SQL71502: Procedure: [Schema].[StoredProc1] has an unresolved reference to object [Schema].[Table1].[Property1].

To resolve the reference error, on the table sql file, right click properties and verify the BuildSettings are set to Build. Changing it to Not In Build fixed it.

SQL71006: Only one statement is allowed per batch. A batch separator, such as 'GO', might be required between statements.

this error is usually (in my experience) due to having a script in your project with property BuildAction=Build whereas it should have BuildAction=None.