[dba-SQLServer] FW: FW: Element K Journal article

On 26 May 2005 at 8:53, Susan Harkins wrote:

> The following came from a reader -- the reader is trying to adapt an Access
> technique for SQL Server. Gustav was the technical end of this article and
> his initial suggestion was to omit the AS keyword, but that didn't work. 
> 
> 
> select * from Test#csv] as t in '' [Text;DATABASE=E:\]
> 

I don't think you can do it without defining the text file as a "linked server" first.

Here's an example for sp_addlinkedserver in BOL:

I. Use the Microsoft OLE DB Provider for Jet to access a text file
This example creates a linked server for directly accessing text files, 
without linking the files as tables in an Access .mdb file. The provider is 
Microsoft.Jet.OLEDB.4.0 and the provider string is 'Text'.

The data source is the full pathname of the directory that contains the 
text files. A schema.ini file, which describes the structure of the text 
files, must exist in the same directory as the text files. Refer to the Jet 
documentation for information about creating a schema.ini file. 

--Create a linked server

EXEC sp_addlinkedserver txtsrv, 'Jet 4.0', 
    'Microsoft.Jet.OLEDB.4.0',
    'c:\data\distqry',
    NULL,
    'Text'

GO

--Set up login mappings

EXEC sp_addlinkedsrvlogin txtsrv, FALSE, Admin, NULL

GO

--List the tables in the linked server

EXEC sp_tables_ex txtsrv

GO

--Query one of the tables: file1#txt

--using a 4-part name 

SELECT * 
FROM txtsrv...[file1#txt]

-- 
Stuart