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:

Smith

William

1000

275

Scott

Tiger

2000

275

John

Jumangi

2500

345

Sam

Rooban

3524

600

Peter

Norton

1234

320

Kathy

Lee

8300

1200

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:

Smith

William

1000

275

Scott

Tiger

2000

275

John

Jumangi

2500

345

Sam

Rooban

3524

600

Peter

Norton

1234

320

Kathy

Lee

8300

1200

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:

2

2

MASTER

9A7B3CC347CD336A

112

2

JTOWSLEE

9A7B3CC347CD336A

122

2

RHOWE

NULL

157

2

BMOELLER

NULL

132

2

CFINETTI

NULL

142

2

LTHOMAS

NULL

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:

2

2

MASTER

9A7B3CC347CD336A

112

2

JTOWSLEE

9A7B3CC347CD336A

122

2

RHOWE

NULL

157

2

BMOELLER

NULL

132

2

CFINETTI

NULL

142

2

LTHOMAS

NULL

Latest Articles