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

OH my gosh.. That gives me SUCH a headache... But thank you -- I'll pass
this along. :) 

Susan H. 

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


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com