how to import excel file to SQL Server 2008 via stored procedure?

Question

OS: Windows 7

File: Excel 2007

SQL Server 2008  X64

How do I import excel file to SQL Server 2008 via stored procedure? I used this statement:

 

 

* INTO Table FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',

'Data Source=C:\something.xls; Extended Properties=Excel 8.0')...[xxxxx$]

When I try to execute I got this error:

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

Then, I reconfigured Ad Hoc settings like this:

sp_configure

sp_configure 'show advanced options', 1

reconfigure

sp_configure 'Ad Hoc Distributed Queries', 1

reconfigure

 

 

 

 

 

 

This did not help either; I got this 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.

 

Any help would be appreciated! 

Thanks!

SELECT

Answer 1

Try this:

 

 
SELECT * 
INTO #temp 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=C:\something.xls;HDR=YES', 
'SELECT * FROM [Sheet1$]')

 

Hope this helps

---

David Dye

Answer 2

@David,

I thought that there was no support for the Jet driver in x64 editions using openrowset. Has there been an update to this?

 

@Red Rose,

You can however use an SSIS package that is set to run as 32-bit using the Excel file.

Hope this helps,
Sean

Answer 3

Yes, you may want to check https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125117 and vote.

(Taken from a recent thread here http://social.msdn.microsoft.com/Forums/en-US/sqlserverdiscussionsru/thread/b81bf531-5d7a-432f-9300-b654c58af825 (in Russian))

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 4

Absolutely correct.  Didn't pay attention to the details.

Thanks

---

David Dye

Answer 5

Naomi,

Thank you, I have voted it up! So there is a beta driver out as references by Naomi HERE. I'm downloading it to try!

-Sean

Answer 6

This did not work for me either. Has anybody managed to solve this problem?

Answer 7

Try using the other provide Microsoft.ACE.OLEDB.12.0 if you have it in your machine.

Answer 8

Hi,

However, there is a beta 64 bit version of Jet   available.  If the beta doesn’t work for you, the following workaround may help you to resolve the issue.

You can install a 32-bit SQL Server Express on your 64-bit environment as a medium. Create a linked server with Microsoft.Jet.OLEDB.4.0 provider to your Excel/Access file on your SQL Server Express instance, and then create a linked server to your SQL Server Express instance on your 64-bit SQL Server instance.

Here are the detailed steps for your reference:
1. Install SQL Server Express 2008 in your 64-bit environment. SQL Server Express   2008 is freely available for download from Microsoft Downloads:
SQL Server Express 2008
http://www.microsoft.com/downloads/details.aspx?familyid=01AF61E6-2F63-4291-BCAD-FD500F6027FF&displaylang=en
2. Open SQL Server Management Studio (SSMS), connect to the 32-bit SQL Server instance MYSERVER\SQLEXPRESS, create a linked server to your Excel file by running the following T-SQL statements:
--Create a linked server.
EXEC master.dbo.sp_addlinkedserver
@server = N'MYACCESS',
@srvproduct=N'Access',
@provider=N'Microsoft.Jet.OLEDB.4.0',
@datasrc=N' D:\ test.xls'
Go
--Create a mapping to use the current user credentials to connect to the linked server.
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'MYACCESS',
@useself=N'True'
GO
3. Connect to the 64-bit SQL Server instance MYSERVER from SSMS, create a linked server to the 32-bit instance with the following T-SQL statements:
EXEC sp_addlinkedserver
@server='MYEXPRESS',
@srvproduct='',
@provider='SQLNCLI',
@datasrc='MYSERVER\SQLEXPRESS'
GO
EXEC sp_addlinkedsrvlogin
@rmtsrvname=N'MYEXPRESS',
@useself=N'True'
GO
4. Create a new query in your 64-bit SQL Server instance and run the following T-SQL statement to query the Sheet1in the Excel:
SELECT * FROM OPENQUERY(MYEXPRESS, ‘SELECT * FROM MYACCESS…Sheet1$)

If there are any more problems, please let me know.
Thanks.

---

***Xiao Min Tan***Microsoft Online Community***

Answer 9

while executing this, i end up with following error:

OLE DB provider "SQLNCLI10" for linked server "MYEXPRESS" returned message "Login timeout expired".

OLE DB provider "SQLNCLI10" for linked server "MYEXPRESS" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".

Msg 65535, Level 16, State 1, Line 0

SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

kindly resolve this error

Thanks,

Sam