Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

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.


advertisement
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, ...]] AS selectstatement [WITH CHECK OPTION]

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.



Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap