CODEWITHSUNDEEP
t-sqlsql-server-2012
Sachin Kainth
Sachin Kainth

Reputation: 46750

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)"

When I run the following commmands in SQL Server 2012

 exec sp_configure 'Advanced', 1 RECONFIGURE
 exec sp_configure 'Ad Hoc Distributed Queries', 1 RECONFIGURE
 EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
 EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1

 SELECT * INTO dbo.Normalization FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=c:\_sandbox\Consolidations.xlsx','SELECT * FROM [Codes and Values$]')

I get this error

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Any idea why?

Upvotes: 1

Views: 21937

Answers (2)

M_Idrees
M_Idrees

Reputation: 2172

I was getting the same error with access database. I was using this syntax:

SELECT * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','DataSource=D:\test.mdb')...[MyTableName]

Then I come to know that 'DataSource' should be 'Data Source'.

SELECT * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Data Source=D:\test.mdb')...[MyTableName]

It works in my case.

Upvotes: 0

steoleary
steoleary

Reputation: 9278

Is the provider installed? The Microsoft.ACE.OLEDB.12.0 provider isn't there by default and so if you haven't installed it the it won't be able to be initialised.

Have a look in SQL management studio in Server Objects -> Linked Servers -> Providers and see if it is listed.

If not, you need to install the Microsoft Access Database Engine (either x86 or x64 depending on your SQL server install) which will install the provider for you.

Download the software here:

Microsoft Access Database Engine 2010 Redistributable

Upvotes: 3

Related Questions