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

Posted on 2012-08-16
Last Modified: 2012-09-03
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]
opendatasource('Microsoft.ACE.OLEDB.12.0','Data Source=c:\convdata\2265\;Extended Properties="Text;"')...FILE1#TXT

Open in new window

Question by:bdpcpa
    LVL 39

    Expert Comment

    "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.

    Author Comment

    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.

    Accepted Solution

    No Answer for this question.

    Author Closing Comment

    closing question

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now