Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Import data using excel file into SQL 2005 Database

Posted on 2009-04-25
9
Medium Priority
?
4,213 Views
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.
0
Comment
Question by:chelpoolunited
  • 4
  • 2
7 Comments
 
LVL 3

Expert Comment

by:mfreuden
ID: 24232081
You'll have to use the new Office 2007 OLE Drivers

Download at and install on the server:  http://www.microsoft.com/downloads/details.aspx?familyid=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

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 *
from
openrowset('Microsoft.ACE.OLEDB.12.0',
'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:

http://social.technet.microsoft.com/Forums/en-US/sqldataaccess/thread/8514b4bb-945a-423b-98fe-a4ec4d7366ea/

0
 

Author Comment

by:chelpoolunited
ID: 24235879
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!!!!
0
 

Author Comment

by:chelpoolunited
ID: 24239327
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!!!!
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 29

Expert Comment

by:Michael Pfister
ID: 24247704
Its for Access but should do for Excel as well:
http://gorm-braarvig.blogspot.com/2005/11/access-database-from-sql-200564.html

HTH
0
 

Author Comment

by:chelpoolunited
ID: 24269275
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!!!!
0
 
LVL 29

Accepted Solution

by:
Michael Pfister earned 2000 total points
ID: 24269306
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.
0
 

Author Comment

by:chelpoolunited
ID: 24468138
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 (http://gorm-braarvig.blogspot.com/2005/11/access-database-from-sql-200564.html) 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!!!!!!!!
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

577 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