2

I am trying to call OPENROWSET command in SQL server 2012 to read data from xls excel file below is my code

SET @sql = 'SELECT * FROM [' + @Query + '$]'


Exec(
 'select * 
  FROM OPENROWSET(
 ''Microsoft.Jet.OLEDB.4.0''
 ,''Excel 16.0;Database=' + @FileDirectory + '\'+ @FileName + ';HDR=YES''
  ,''' + @sql + ''')' 
 )

after running the command it gives

Msg 7302, Level 16, State 1, Line 1 Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

I tried to call 

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

but also not working . Also both sql and excel are 64 bit

any other solution??

Imran Ali Khan
  • 8,469
  • 16
  • 52
  • 77
Malo
  • 1,232
  • 2
  • 17
  • 28
  • Did you try these solutions? https://stackoverflow.com/questions/36987636/cannot-create-an-instance-of-ole-db-provider-microsoft-jet-oledb-4-0-for-linked – QHarr Mar 15 '18 at 12:05
  • 1
    Note additional lines for the reconfig (as well as other recommendations) RECONFIGURE; EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1 ; EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParam', 1 ; – QHarr Mar 15 '18 at 12:06
  • 1
    i changed to `Microsoft.ACE.OLEDB.12.0` and it worked fine thank you – Malo Mar 15 '18 at 12:29
  • 1
    Please consider posting code revision as answer so others can find going forwards. – QHarr Mar 15 '18 at 12:32

0 Answers0