Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Rowset Functions
Rowset functions return an object that can be used as if it were a table or a view. A couple of rowset functions (CONTAINSTABLE and FREETEXTTABLE) are specific to full-text search functionality. They require knowledge of full-text search functionality, and can be well-served with their own article.
(I have already discussed another rowset function (OPENXML) in a separate article. Please refer to "SQL Server and OPENXML" for details of OPENXML.)
The remaining rowset functions are OPENDATASOURCE, OPENQUERY, and OPENROWSET. Each of these functions provides a way to write a query against a remote data source, which can be another SQL Server, a database engine (Access, Oracle), a spreadsheet, a text file, and so on.
OPENQUERY lets you run a pass-through query against a data source that has been set up as a linked server. You can set up a linked server through Enterprise Manager or by using the sp_addlinkedserver system stored procedure. (Please refer to SQL Server online documentation for details of sp_addlinkedserver.)
Here is how you set up a linked server with Transact SQL:
EXEC sp_addlinkedserver 'AccessDB', 'Access', -- this could be any valid identifier 'Microsoft.Jet.OLEDB.4.0', 'C:\Data\MyMdb.mdb' - - this is the full path of mdb file
You can use OPENQUERY to run a SQL statement against this linked server as follows:
SELECT * FROM OPENQUERY(remote_server, 'SELECT au_lname, au_fname FROM authors')
The OPENROWSET function does not require the setup of a linked server. Instead, you specify the connection and security parameters within the query itself. To query the same Access database using OPENROWSET, you can use the following syntax:
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\data\MyMdb.mdb';'admin';'', Sales)
The OPENDATASOURCE function is very similar to OPENROWSET in that it does not require definition of a linked server. Instead, the connection information as well as the security parameters must be passed each time the query is executed. The difference between OPENROWSET and OPENDATASOURCE is that the latter provides the connection information as part of the four-part object name, as in [server].[database].[owner].[object], whereas the former provides the connection string and the object name in a comma-separated list. For example, OPENDATASOURCE can return all rows from the sales table from the Access database on a hard drive as follows:
SELECT * FROM OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\data\MyMdb.mdb";User ID=Admin;Password=; ')...Sales