Create a Linked Server to a text file

[jackied]

I am trying to create a Linked Server in order to query the contents of a text file in SQL server.

I can not seem to see a suitable option in the Provider Name list.

Has anyone done this successfully? Could you tell me how you set the Linked Server settings, and if you had to install another OLEDB provider how you did it and where you got it from?

Thanks in anticipation.
Jackie

 

[Guest_imported]

First you may create a DSN using text file, then create
a linked server point to this dsn, I didn't try that
it maybe helpful.

 

[Guest_imported]

I tried add a linkedserver pointing to a directory
which include some text file, but it fails when I
ran a query on this linkedserver.

select * from openquery(textfile,'select * from company')

it fails with error " ...\winnt\system32\user32.dll"

do you have solution till now?

 

[jackied]

Hi wesix.

I have found out how to do it. The key is to have a schema.ini file in the same folder as the text file which looks a bit like this:

[myfile.txt]
ColNameHeader=False
Format=CSVDelimited
MaxScanRows=1
CharacterSet=Ansi
Col1=CustID Text
Col2=Reference Text
Col3=NumberofThings Integer
...

When setting up my linked server:
Provider Name = Microsoft Jet 4.0 OLEDB Provider
(this must appear in the drop down list)
Provider String = Text
Data source = d:\data