Lesson 7	Using OPENROWSET
Objective	Use the OPENROWSET remote data function to access remote data.

Using OPENROWSET Remote Data Function in SQL-Server

OPENROWSET is a special Transact-SQL statements that help you write queries against remote data sources.

OPENROWSET

OPENROWSET allows you to construct a query against a remote OLE DB data source without linking a server. As opposed to a linked server, the OPENROWSET statement does not retain any information, and it must be used as a one-time-only query.
The OPENROWSET statement can be used in the following situations:

From within a FROM clause of a query, which means that it is useful for performing quick look-ups of data located remotely
When you allow users to specify where the data is located and dont know in advance what servers will be accessed

OPENROWSET syntax

The OPENROWSET function uses the syntax shown in the following display to access a SQL Server 2000 OLE DB data source:

OPENROWSET Syntax

OPENROWSET ('provider_name' 
   , { 'datasource' ; 'user_id' ; 'password' 
     | 'provider_string' } 
   , { [ catalog. ] [ schema. ] object 
     | 'query' } 
   )

OLE DB

Note that there is an extended syntax available to use for OLE DB data sources other than SQL Server 2012.
The syntax shown above is for SQL Server 2012 only, because the first argument is the string literal 'SQLOLEDB'.

OPENROWSET Example

To use the OPENROWSET function, use the following parameters to access the Timesheets table:

Table name	Timesheets
Server	Boston
Login	`amann`
Password	`amannpwd`

Use the following Transact-SQL statement:

SELECT t.*

FROM OPENROWSET('SQLOLEDB','Boston';'amann';'amannpwd',
'SELECT * FROM Timesheets') AS t

As you can see, the OPENROWSET function is used in the FROM clause and aliased so that it can be easily used within the rest of your query. Therefore, you can treat the results of the OPENROWSET function as if they were a table.
In the next lesson, you will learn how to use the OPENQUERY function.