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 - Page 2

By Muthusamy Anantha Kumar aka The MAK

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.

--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



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