Import into SQL 2008 using opendatasource('Microsoft.ACE.OLEDB.12.0 Fails

I import flat text files with a schema.ini file for the properties of the files. I am using Microsoft.ACE.OLEDB.12.0 to import the files. We login as with windows user authentication.

The import command only works when user ABC is logged in. If I am logged in as any other windows user or even the SA Admin the import fails.

Error Message
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "'c:\convdata\2265\' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.".
Msg 7303, Level 16, State 1, Line 7
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Please help,

Import Command
USE [database1]
select * INTO [tablename1]
from 
opendatasource('Microsoft.ACE.OLEDB.12.0','Data Source=c:\convdata\2265\;Extended Properties="Text;"')...FILE1#TXT
go 

Open in new window

bdpcpaAsked:
Who is Participating?
 
bdpcpaConnect With a Mentor Author Commented:
No Answer for this question.
0
 
lcohanDatabase AnalystCommented:
"We login as with windows user authentication. " - I assume that is into SQL and obviously that user has access to that physical path/file but rest of the users don't. You may need to create a SQL Proxy if you want to use a SQL Login to run the import as SQL logins can't be granted Windows OS folder/file access.
0
 
bdpcpaAuthor Commented:
We dont want to use SQL logins, we would like to login with windows authentication. I didnt have any issues with this process when i was using SQL 2005.
Thanks,
0
 
bdpcpaAuthor Commented:
closing question
0
All Courses

From novice to tech pro — start learning today.