OpenRowSource and OpenRowSet in SQL Server 2000

April 7, 2004

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: