CODEWITHSUNDEEP
sql-serverexcelsql-server-2008-r2
Gio Venice
Gio Venice

Reputation: 59

Import data from XLS file to SQL Server 2008 R2 database

Here's our situation:

We have:

NO Microsoft Office package are installed on this server (we can't open .xls, .ppt, .doc etc)

We tried to import an Excel 97-2003 file using the wizard and everything worked!

The problem is: we need to execute a query with some control in it that imports data from the Excel file into our database.

SELECT * 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\file.xls', 'SELECT * FROM [Clienti$]')

but we get the following error:

Msg 7308, Level 16, State 1, Line 1
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.

We already configured some options following this guide

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

Upvotes: 0

Views: 692

Answers (1)

Stephen Whitfield
Stephen Whitfield

Reputation: 81

Unfortunatly according to this thread:

Microsoft.Jet.OLEDB.4.0 is not supported for 64-bit OS.

Additionally this link may be helpful to work through.

Try change 'Microsoft.Jet.OLEDB.4.0' to 'Microsoft.ACE.OLEDB.12.0'?

Upvotes: 0

Related Questions