dcsimg

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:

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








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers