1) Create your text folder on the server, e.g. D:\Mssql\LinkedServer

2) Populate your text folder. For a simple example, I'm going to use the following tab-delimited file, called Example.txt

MyCol1(TAB)MyCol2(TAB)MyCol3

12138(TAB)129(TAB)Row 1 description

12138(TAB)129(TAB)Row 2 description

12165(TAB)133(TAB)Row 3 description

12212(TAB)134(TAB)Row 4 description

12243(TAB)140(TAB)Row 5 description

3) Create a Schema.ini file in the same folder. This file is used to describe all the text files you want to refer to.

At the very least, it contains the following lines for each filename:

[MyExample.txt]

CharacterSet=OEM

ColNameHeader=True

Format=TabDelimited

But I find it better to describe each column, as in:

[MyExample.txt]

CharacterSet=OEM

ColNameHeader=True

Format=TabDelimited

Col1=NamedCol1 Long

Col2=NamedCol2 Long

Col3=NamedCol3 Text Width 50

2) Add the linked server

exec sp_addlinkedserver ServerName, 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', 'FolderLocation', NULL, 'Text'

ServerName is your name for the server

FolderLocation refers to the folder on the server where your text files are located, e.g. D:\Mssql\LinkedServer

3) Use the following syntax to access your text file:

SELECT * FROM TestServer...MyExample#txt (note the three dots, and the hash symbol - a period would interfere with SQL Server syntax)

If you use the shorter version of the Schema.ini file, your column names are those contained in the first row of the text file, e.g. MyCol1.

If you use the longer version of the Schema.ini file, your column names are those contained in the schema.ini file, e.g. NamedCol1

Notes

-----

- You can SELECT and JOIN and INSERT INTO your text files. You cannot use the DELETE or UPDATE commands.

- If you do SELECT *, your columns will appear in alphabetic sequence!!!

- If you need to give users access to your linked server, you may have to grant your users read/write access to the Temp folder on SQL Server. This is because one source I read stated that SQL Server needs to create its own temporary files in the Temp folder. I know that I had some problems in enabling users to read files using this approach; but we were also experiencing many problems with the database server, and eventually dumped that machine and used a different one (actually a virtual machine), so my problems may have been caused by something else.