RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Easily Access Remote Data Using SQL Server Views : Page 2

Getting to the data you need is always a challenge. Views can provide a "no muss, no fuss" way to retrieve the data you need—whether it's in the current database or another.

Remote Viewing
To create a view, use the TRANSACT-SQL (T-SQL) CREATE VIEW statement in the following form:

CREATE VIEW [databasename].[owner].viewname[
 (column [n, ...])]
[WITH attribute [n, ...]]

Everything's optional except viewname and the columns that comprise it.

After you've created the view, users can execute it via a SELECT statement in the following form:

SELECT *|fieldlist FROM database.owner.viewname 

As long as the view is in the current database, you can omit database. However, if you want to view data in another database, the statement needs the explicitly declared database argument as shown in Figure 1. From inside a database named Sales, the SELECT statement executes a view named vUpdateProductionLocation in AdventureWorks (a sample database that you can download). It really is that easy.

Figure 1. View Data in Another Database: Views enable you to identify another database explicitly to execute an existing view.

The actual view is a simple SELECT statement that displays three fields of data from Production.Location, a table in AdventureWorks:

SELECT Name, CostRate, Availability
FROM Production.Location

Only the second SELECT (see Figure 1) returns data, because that statement specifies the database AdventureWorks. That's the key to viewing data in another database, the easy way. It's so simple that you might not realize you can do it!

The view specifies the table and columns, and it can include a WHERE clause to limit the results. The calling SELECT statement identifies the database and the view.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date