Sometimes it's handy to create a Linked Server to text files in order to perform distributed queries or bulk inserts of flat-file data into SQL Server.
In order to demonstrate this capability with SQL Server 2000, create a text file copy of the Categories table from Northwind database and place the text file named CATEGORIES.TXT in a subdirectory named TEXTFILES on a hard drive attached to the SQL Server machine.
Then add a Linked Server to the text file with a script like this:
exec sp_addlinkedserver NW_TEXT, 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', 'Z:\TEXTFILES', NULL, 'Text'
Alternatively, you can add a Linked Server in Enterprise Manager:
Step 1
Step 2
Step 3
Step 4
This now allows you execute queries against the text file like this:
select * from NW_TEXT...categories#txt
You can use a schema.ini file (Text File Driver) to provide formatting like column headers in a text file and make the file(s) browsable just like tables are when displaying them from the Linked Server node in Enterprise Manger.
For this example, create a schema.ini file that looks like this:
[CATEGORIES.TXT]
Format=CSVDelimited
CharacterSet=ANSI
ColNameHeaders=True
Col1=CategoryID Short
Col2=CategoryName Text Width 15
Col3=Description Text Width 100
For further information, check Schema.ini (Text File Driver) on Microsoft MSDN.
One caveate I ran across though, is that the maximum number of columns or fields that you can specify in a schema.ini file is 255. So, if you have a really W-I-D-E data file with lots of columns, and you are considering Linked Server with a schema.ini file, be aware of this limitation.
Hope this helps.
-pj