Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Apr 7, 2004

OpenRowSource and OpenRowSet in SQL Server 2000

By Muthusamy Anantha Kumar aka The MAK

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date