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.