Accessing a Text file from SQL Server....

[Marius Traelnes]

Hello!

I want to read a text file as a table from SQL server. That is simple
enough like this:

--Create a linked server
EXEC sp_addlinkedserver pserv, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'm:\product_import',
NULL,
'Text'
GO

This creates a linked server and i can query the text file when i have a
proper Schema.ini file.
The only problem is that i have to be logged on as 'admin' with no password
to access it, if not i
get this error message:

Server: Msg 7303, Level 16, State 2, Line 1
Could not initialize data source object of OLE DB provider
'Microsoft.Jet.OLEDB.4.0'.
[OLE/DB provider returned message: Cannot start your application. The
workgroup information file is missing or opened exclusively by another
user.]

After that i tryed the following procedure:

--Set up login mappings
EXEC sp_addlinkedsrvlogin pserv, FALSE, 'username', 'password'
GO

But the same error message came up.

The problem is that i can not have a user called admin without password
becouse of security problems so then i read about openrowset and i have
tried
it also but i get error messages there also. Like this:

SELECT a.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'm:\product_import';'admin';, [test#txt])
AS a
GO

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot
open the file 'm:\product_import'. It is already opened exclusively by
another user, or you need permission to view its data.]

So now i do not know what to do.....

I suspect i can change the admin user i registry settings but i don't know.

I use SQL server 7.0 on Window 2000.

Hope for help!!

Marius

And how?

Thanks for any help!

[François Kreutz]

Marius,

Try this :

--
François Kreutz

"Marius Traelnes" <m...@c2i.net> a écrit dans le message news:
uy9ebgJUAHA.265@cppssbbsa05...

[François Kreutz]

Sorry.

Try this :

EXEC sp_addlinkedserver txtserver, 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
'c:\path', NULL, 'Text'

EXEC sp_droplinkedsrvlogin 'txtserver', NULL

EXECUTE sp_addlinkedsrvlogin 'txtserver' , False, NULL, NULL, NULL

Regards,

[Marius Traelnes]

Thanks!!!!!

:-))

"François Kreutz" <hello> wrote in message
news:ehIwZkKUAHA.251@cppssbbsa04...

[François Kreutz]

For any interested reader, this also solves the security problem when
accessing an Excel file with a linked server.

--
François Kreutz

"Marius Traelnes" <m...@c2i.net> a écrit dans le message news:

OiZ1KRu...@cppssbbsa02.microsoft.com...