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

• elham_azizi_62

  SSCertifiable

  Points: 5095

  More actions

  February 14, 2012 at 10:21 pm

  #391560

  hello all.

  I work with sql server 2008 on windows xp service pack3.I use this : set @STR='select *

  FROM

  OpenDataSource( ''Microsoft.ACE.OLEDB.12.0'',''Data Source="'+@sPath+'";Extended properties=Excel 8.0'')...'+@sSheet+'

  '

  EXEC sp_executesql @STR

  for read excel file and this work fine in windows srver 2008 but get error in windows xp:

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

  I install AccessDatabaseEngine.exe for 32bit os and use this scripts:

  sp_configure 'show advanced options', 1

  reconfigure

  go

  sp_configure 'Ad Hoc Distributed Queries', 1

  reconfigure

  go

  sp_configure 'show advanced options', 0

  reconfigure

  go

  USE [master]

  GO

  EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess' , 1

  GO

  EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1

  GO

  but donot solve this problem.please help me and tell me how to solve this problem?

• Koen Verbeeck

  SSC Guru

  Points: 259115

  More actions

  February 14, 2012 at 11:42 pm

  #1447135

  The first thing that I notice that is wrong is the extended properties of your connection string.

  If you work with the ACE OLE DB provider, it should be Excel 12.0.

  Connection strings for Excel 2007

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP