Import data using excel file into SQL 2005 Database

Posted on 2009-04-25
Last Modified: 2013-11-10
I have registered msjet40.dll and msjetloedb40.dll on my mssql database server. But it prompts with the following error.
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
I am using 64 bit windows OS and 64 bit MS SQL 2005 enterprise edition. Please let me know if there is a workaround for that or I have o go with 32 bit version of SQL 2005 along with 32 OS.
Question by:chelpoolunited
    LVL 3

    Expert Comment

    You'll have to use the new Office 2007 OLE Drivers

    Download at and install on the server:

    You'll also need to enable: "Ad hoc remote queries" in the surface area config tool.

    Then the sytax would be something like this:

     select *
    'Excel 12.0;Database=c:\test.xls;HDR=Yes',
    'select * from [sheet1$]')

    If you're still having problems check out this thread for 64bit issues depending on your os:


    Author Comment

    Thanks for the suggestion. I tried it but only to pop up with new error as follows:

    Ad hoc access to OLE DB provider 'Microsoft.ACE.OLEDB.12.0' has been denied. You must access this provider through a linked server.

    As mentioned in the error if i go with the solution of creating a linked server for accessing the provider then do i need to create linked server for each of the excel files i will be using for data import.

    Thanks in advance!!!!

    Author Comment

    Hello all,
    Can you please guide me as to how I can access the EXCEL file using a linked server, rather how can I create a linked server for EXCEL file on MS-SQL server 2005 64 bit (OS:Windows server 2003 64 bit)

    Thanks in advance!!!!
    LVL 28

    Expert Comment

    Its for Access but should do for Excel as well:


    Author Comment

    Hello mpfister,

    I have already gone through that but one thing thats troubling me is where do I install this Express Edition. On same server or on another server. Also what should be the hardware configuration for the server? (i ll be using virtual machine most probably).

    Thanks in advance!!!!
    LVL 28

    Accepted Solution

    I'd rather put it on the same server. If you're not going to import Gbytes of data, the extra load on the server should be minimal.

    Author Comment

    Hello guys,

    Finally we got this working. Sorry for putting it so late. Actually we did it in the same way mentioned in the article ( with few modifications to the stored procedure. The thing is that this procedure onlt allows you to select data from excel file but insert statement before that does not work. So to work around this we created linked server on 32-bit SQL server pointing to 64-bit SQL server and modified te stored procedure to accomodate the insert statement. Insert statement actually writes the data to a database on your 64-bit SQl server.

    Thanks a lot!!!!!!!!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

    779 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

    19 Experts available now in Live!

    Get 1:1 Help Now