Error message when importing data from excel to SQL Server

Question

I am trying to import data from Excel to SQL without using the wizard as it is a process that needs to take place every week, so automation is a must. When I run the following script however, I get an error message:

Script:

IF OBJECT_ID('TEST Weekly Sales D') IS NOT NULL
DROP TABLE [TEST Weekly Sales D]

SELECT *
INTO [TEST Weekly Sales D]
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
  'Excel 12.0;Database=C:\.....\Weekly Sales D.xlsx',
   ['SHEET1$'])
GO

Error Message:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error". Msg 7303, Level 16, State 1, Line 5 Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

The server does not have excel on it, but I was told this is not a problem. I installed the ACE OLEDB driver, but it seems to working intermittently.

Can someone please point out to me where I am going wrong?

AdamL · Accepted Answer · 2013-01-15 14:38:55Z

2

First check, if ACE provider appeared under ServerObjects>Linked Servers>Providers tree in Management Studio.

Then run following commands (run them separately to notice if "changed from 0 to 1" info appears).

To enable OPENROWSET:

sp_configure 'show advanced options'
GO
RECONFIGURE
GO

sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

To configure ACE driver:

EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO

Also, which version of the driver did you install, and which veriosn of SQL Server are you running? x86 or 64?

Regards

answered Jan 15, 2013 at 14:38

AdamL

13.3k5 gold badges57 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Error message when importing data from excel to SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related