OpenRowSource and OpenRowSet in SQL Server 2000

Wednesday Apr 7th 2004 by Muthusamy Anantha Kumar aka The MAK
Share:

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. This week, MAK demonstrates how to use OpenRowSource and OpenRowset.

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

How to query an MS-Access table using OpenDataSource

Let's create an MS-Access database, c:\external\MyAccess.mdb, with Table1 as Table and create some sample data. Open Query analyzer and execute the SQL statement below.

Click for larger image

--Read MS-Access table 
  using OpenDataSource
SELECT   *
FROM  OPENDATASOURCE(
 'Microsoft.Jet.OLEDB.4.0',
 'Data Source="c:\external\myaccess.mdb";
 User ID=Admin;Password='
 )...Table1

The results will look like the table below.

Kleong

MAK

1000

123

Hsu

Claire

1245

123

Rooban

Sam

4000

1200

How to query MS-Access table using OpenRowset

Let's create an MS-Access database, c:\external\MyAccess.mdb, with Table1 as Table and create some sample data. Open Query analyzer and execute the SQL statement below.

--Read MS-Access table using OpenRowSet
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
   'c:\External\MyAccess.mdb';'admin';'', Table1) 

The results will look like the table below:

Kleong

MAK

1000

123

Hsu

Claire

1245

123

Rooban

Sam

4000

1200

How to Query a CSV file using OpenRowset

Let's create a CSV file, c:\external\mycsv.csv, with the below given data and execute the SQL query below.

--Read CSV using OpenRowSet
select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
  DefaultDir=C:\External;','select top 6 * from
MyCsv.csv')

The results 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 text files using OpenRowset

Let's create a text file, c:\external\mytext.txt, with the below given data and execute the SQL query below:

--Read Text using OpenRowSet
select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
  DefaultDir=C:\External;','select top 5 * from
Mytext.txt')

The results will look like the table below. All the four columns are considered to be one single column.

Smith William 1000 275

Scott Tiger 2000 275

John Jumangi 2500 345

Sam Rooban 3524 600

Peter Norton 1234 320

How to combine the results of two text files

Create c:\External\serverlist1.txt with the below given data.

Servername

ETL2
YUKON
SQL
SQL2k

Create c:\External\serverlist2.txt with the below given data.

Servername
ETL
YUKON
SQL2k

Execute the SQL query below.

select * from OpenRowset('MSDASQL', 
  'Driver={Microsoft Text Driver 
  (*.txt; *.csv)};
  DefaultDir=C:\External;',
  'select top 5 * from
Serverlist1.txt') union
select * from OpenRowset('MSDASQL', 
  'Driver={Microsoft Text Driver 
  (*.txt; *.csv)};
  DefaultDir=C:\External;',
  'select top 5 * from
Serverlist2.txt')

The results will look like the table below:

ETL

SQL2k

SQL

ETL2

YUKON

How to find the "not found" list comparing two files

Execute the SQL Query below:

--Not found List 
select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
  DefaultDir=C:\External;','select top 5 * from
Serverlist1.txt') 
where Servername not in
(select Servername from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
  DefaultDir=C:\External;','select top 5 * from
Serverlist2.txt') )

The results will look like the table below:

ETL2

SQL

How to compare an SQL Table with an External Text File

Create a table ServerList in SQL Server as shown below.

Create table serverlist (Servername varchar(100))
insert into serverlist select 'ProductionSQL'
insert into serverlist select 'Yukon'
insert into serverlist select 'YUKOnBeta'
insert into serverlist select 'SQL2000'

Execute the SQL Query below.

--Compare SQL Table and a external text file 
--Not found list comparing sql table and c:\External\serverlist2.txt
Select * from Serverlist where servername not in 
(select Servername from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
  DefaultDir=C:\External;','select top 5 * from
Serverlist2.txt') )

The results will look like the table below:

ProductionSQL

YUKOnBeta

SQL2000

How to combine the results of two text files and a SQL Server table

Execute the below SQL Query:

--Combined distinct rows from two text files and the SQL Table

select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
  DefaultDir=C:\External;','select top 5 * from
Serverlist1.txt') union
select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
  DefaultDir=C:\External;','select top 5 * from
Serverlist2.txt')  union
select * from serverlist

The results will look like the table below:

ETL

SQL2k

ProductionSQL

SQL

ETL2

SQL2000

YUKOnBeta

YUKON

How to create a View using OpenRowset or OpenRowSource

If you are planning to keep the Access, Excel, Text and CSV files in the same folder for a long time, then you can create views similar to the one below.

create view [MyText] as
select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
  DefaultDir=C:\External;','select top 5 * from
Mytext.txt')

Go

select * from [MyText]

go

select * from [MyText] where Fname_Lname_Salary_Tax like 'S%'
go

Create view [MyExcel] as 
SELECT * 
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\External\MyExcel.xls";
    User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
go
select * from [MyExcel] where Fname like 's%'

Conclusion

As mentioned before, the intent of this article is to guide the SQL Server Database Administrators in using OpenRowSource and OpenRowSet for infrequent ad-hoc query requests from an external data source.

» See All Articles by Columnist MAK

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved