OpenRowSource and OpenRowSet in SQL Server 2000

SQL Server Data Base Administrators are often exposed to
situations in running ad-hoc queries using external data sources such as MS-Access
database or Excel sheets or CSV files or text files. For such frequent requests,
Database Administrators usually use “linked servers” covered in my previous
article, Linking
SQL Server to Heterogeneous Systems.

However, for in-frequent ad-hoc requests, Database
Administrators usually use openrowsource or openrowset, or they import the
external data source to SQL server and query tables. The main intent of this
article is to demonstrate how to use OpenRowSource and OpenRowset.

OpenRowset helps in accessing remote data from an OLE DB
data source. Openrowsource provides ad hoc connection information as part of a
four-part object name without using a linked server name.

How to query an Excel sheet using OpenDataSource

Let’s
create an Excel sheet c:\external\MyExcel.xls with Book1 as the workbook and
create some sample data. Open Query analyzer and execute the SQL statement
below.

–Read Excel Sheet using OpenDataSource

SELECT *

FROM OpenDataSource( ‘Microsoft.Jet.OLEDB.4.0’,

  ‘Data Source=”c:\External\MyExcel.xls”;

    User ID=Admin;Password=;Extended properties=Excel 8.0′)…Book1$

The result will look like
the table below:

How to query an Excel
sheet using OpenRowSet

Let’s create an Excel sheet c:\external\MyExcel.xls with
Book1 as the workbook and create some sample data. Open Query analyzer and
execute the SQL statement below.

–Read Excel Sheet using OpenRowSet

select * FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’,

‘Excel 8.0;DATABASE=c:\External\MyExcel.xls’, ‘Select * from [Book1$]’)

The result will look like the
table below:

How to query a remote SQL
Server table using OpenDataSource

Let’s create a table named CM_Sales.dbo.users
on a remote server ETL. Execute the SQL statement below.

–Read SQL Server table using OpenDataSource

SELECT   *

FROM      OPENDATASOURCE(

         ‘SQLOLEDB’,

         ‘Data Source=ETL;User ID=Weblogin;Password=Web’

         ).CM_Sales.dbo.users

The results will look like the
table below:

How to query a remote SQL
Server table using OpenRowset

Let’s create a table named CM_Sales.dbo.users
on a remote server ETL. Execute the SQL statement below.

–Read SQL Server table using OPENROWSET

SELECT *

FROM OPENROWSET(‘MSDASQL’,

   ‘DRIVER={SQL Server};SERVER=ETL;UID=Weblogin;PWD=web’,

   CM_Sales.dbo.Users)

The results will look like the
table below: