OpenRowSource and OpenRowSet in SQL Server 2000 - Page 2

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.

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:

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:

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.