Walkthrough: Create and Deploy a Database that References Another Database

Article
08/23/2010

In this walkthrough, you will create a database project for a database that refers to another database. The database to which the project refers is also managed by using Visual Studio Team System Database Edition but will be in a different solution. This approach emulates the common scenario where different developers manage different databases or where a developer cannot deploy the database that is the target of a reference.

This walkthrough illustrates the following tasks:

How to create a database project.
How to refer to a .dbschema file that was produced by another database project.
How to define variables and their values for the database project to which you are referring.
How to deploy your database project into your isolated development environment.

Prerequisites

You must have installed Visual Studio Team System Database Edition. In addition, you must have completed Walkthrough: Create and Deploy a New Version-Controlled Database and deployed the resulting database to your isolated development environment. To add your project to version control, you must have installed version control software, such as Visual Studio Team System Team Foundation Server or Visual SourceSafe.

To create the database project

On the File menu, point to New, and click Project.

The New Project dialog box appears.
In Project Types, expand Database Projects, and click SQL Server 2005.
In Templates, click SQL Server 2005 Database Project.
In Name, type OrdersDB.
Select the Create directory for solution check box.
Accept the default values for Location, Solution Name, and Add to Source Control, and click OK.

Note

You could, at this point, add your solution to version control. In this walkthrough, you add the solution to version control in the final procedure.

The new database project, ProductsDB, appears in Solution Explorer.
On the View menu, click Database Schema View.

Schema View appears if it was not already visible.

Next, you add the tables to the database project.

To add the OrderItems table to your database project

In Schema View, expand the OrdersDB node, expand the Schemas node, expand the dbo node, and click the Tables node.
Right-click the OrdersDB project, point to Add, and click Table.

The Add New Item dialog box appears.
In Templates, click Table.

Note

In the Categories list, you can click Tables and Views to more easily find the template for a table.
In Name, type OrderItems as the name that you want to give the new table.
Click Add to add the table to your database project.

Solution Explorer shows the new file for the table in your database project. Schema View shows the new table object. The Transact-SQL (T-SQL) editor appears and displays the definition for your new table.

In the T-SQL editor, modify the table definition to match the following example:

-- =============================================
-- Create OrderItems table
-- =============================================
CREATE TABLE [dbo].[OrderItems]
(
[POID] INT NOT NULL,
[ItemID] INT NOT NULL,
[ProductID] INT NOT NULL,
[Quantity] INT NOT NULL,
[Price] FLOAT NOT NULL,
[Notes] NVARCHAR(75) NULL
) ON [PRIMARY]

On the File menu, click Save dbo.OrderItems.table.sql.
In Schema View, expand the dbo.OrderItems node.
Expand the Columns node in the dbo.OrderItems table.

The columns that you defined in the T-SQL editor appear.

Next, you add a primary key to the OrderItems table.

To add a primary key to the OrderItems table

In Schema View, click dbo.OrderItems.
On the Project menu, click Add New Item.

The Add New Item dialog box appears.

Note

You can also right-click the dbo.OrderItems table, point to Add, and click Primary Key.
In Templates, click Primary Key.
In Name, type PK_OrderItems as the name that you want to give the new primary key.
Click Add to add the primary key.

Note

The icon for the primary key appears with the red circle that contains a white "x," which indicates that the default definition contains an error. This behavior is expected because the default definition refers to the "column_1" column, which does not exist.
In the T-SQL editor, modify the primary key definition to match the following example:
```
-- =============================================
-- Create PK_OrderItems primary key 
-- =============================================
ALTER TABLE [dbo].[OrderItems] 
ADD CONSTRAINT [PK_OrderItems] 
PRIMARY KEY CLUSTERED  ([POID], [ItemID]) 
ON [PRIMARY]
```
Solution Explorer shows the new file for the primary key in your database project. Schema View shows the new primary key object. The T-SQL editor appears and displays the definition for your new key.
On the File menu, click Save dbo.OrderItems.PK_OrderItems.pkey.sql.

The error indicator disappears from the icon, which indicates that the primary key definition is now valid.

Next, you add the cross-database reference.

To add a view that references a table in another database

In Schema View, click the Views node.
On the Project menu, click Add New Item.

The Add New Item dialog box appears.

Note

You can also right-click the OrdersDB project, point to Add, and click View.
In Templates, click View.

Note

In the Categories list, you can click Tables and Views to more easily find the template for a table.
In Name, type ItemDetailsView as the name that you want to give the new view, and then click Add.

Solution Explorer shows the new file for the view in your database project. Schema View shows the new view object. The Transact-SQL (T-SQL) editor appears and displays the definition for your new view.

Note

In Schema View, the icon for the view appears with the red circle that contains a white "x," which indicates that the default definition contains an error. This behavior is expected because the default definition references the "[sometableorview]" table or view, which does not exist.

In the T-SQL editor, modify the table definition to match the following example:

-- =============================================
-- Create ItemDetailsView view
-- =============================================
CREATE VIEW [dbo].[ItemDetailsView]
AS 
SELECT O.[POID], O.[ItemID], P.[ProductName], P.[UnitPrice], O.[Quantity], 
       O.[Price], O.[Notes] FROM [dbo].[OrderItems] AS O
 INNER JOIN [ProductsDB].[dbo].[Products] AS P
        ON P.[ProductID] = O.[ProductID]

On the File menu, click Save dbo.ItemDetailsView.view.sql.
On the View menu, click Error List.

In the Error List, six errors appear. The errors occur because Database Edition cannot validate whether the database to which you are referring will exist on the target server when you deploy the database project. This condition is noteworthy because you can deploy to different target locations. To resolve the errors, you next define a cross-database reference.

To define a cross-database reference

In Solution Explorer, expand the OrderItems project, and click the References node.
On the Project menu, click Add Database Reference.

Note

You can also right-click the References node and click Add Database Reference.

The Add Database Reference dialog box appears. Because the solution contains only one database project, Database project schema (.dbschema) is specified by default.
Click Browse.

The Select Database File dialog box appears.
Specify the ProductsDB.dbschema file that was created when you built the project in the prerequisite walkthrough (for example, My Documents\Visual Studio 2008\Projects\ProductsDB\ProductsDB\sql\ProductsDB.dbschema), and click Open.
In Database Reference Variables, select the Define database variable check box. In Name, type ReferencedDBVar. In Value, type ProductsDB.

Note

In this walkthrough, you can assume that the database to which you are referring is always on the same server as the database to which you are adding the reference. Therefore, you do not have to define variables for the server name.
In Updating schema objects and scripts, select the Update the existing schema object definitions and scripts to use the database reference variables check box, and click OK.

In the Preview Changes dialog box, click INNER JOIN to display the details of the change, and then click Apply.

The definition of the view is updated to match the following:

-- =============================================
-- Create ItemDetailsView view
-- =============================================
CREATE VIEW [dbo].[ItemDetailsView]
AS 
SELECT O.[POID], O.[ItemID], P.[ProductName], P.[UnitPrice], O.[Quantity], 
       O.[Price], O.[Notes] FROM [dbo].[OrderItems] AS O
 INNER JOIN [$(ReferencedDBVar)].[dbo].[Products] AS P
        ON P.[ProductID] = O.[ProductID]

The variable name that you specified identifies the table to which you are referring, and the six errors have been resolved. Next, you build and deploy the database project.

To configure, build, and deploy the project

In Solution Explorer, click OrdersDB (the project, not the solution).
On the Project menu, click OrdersDB Properties.

The properties window for the project appears.

Note

You can also right-click OrdersDB in Solution Explorer and click Properties.
Click the Deploy tab.
In Deploy action, click Create a deployment script (sql) and deploy to database.
Click Edit to specify the target connection.

Specify the information to connect to the database server to which you want to deploy the OrdersDB database.

Important Note:
To successfully complete this walkthrough, you must deploy the database to the same server to which you deployed the ProductsDB in the prerequisite walkthrough. If you want to deploy to a different server, you must define a variable for it.

In Select or enter a database name, type OrdersDB, and click OK.

The Target Connection box contains the connection string, and the Target Database Name box contains OrdersDB.
Accept the default values for the other options, open the File menu, and click Save Selected Items.

Your project deployment settings are saved.
On the Build menu, click Build Solution.

Your database project builds. If the build was successful, Build Succeeded appears in the status bar, and the build results appear in the Output window.
In Solution Explorer, click OrdersDB (the project, not the solution).
On the Build menu, point to Deploy OrdersDB.

Your database project is deployed by using the connection that you specified in the build configuration. The message "Deployment succeeded" appears in the Output window and in the status bar.

Next Steps

You would now typically put your database project under version control to share it with the team.