OLE DB Error

• andyc209

  Say Hey Kid

  Points: 703

  More actions

  August 19, 2016 at 7:49 am

  #311370

  I am using 2008 R2 and trying to build an Excel file from data within SQL

  I am using the following code

  SET @sql=N'INSERT INTO OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',''Excel 8.0;HDR=YES;DATABASE='+@FILENAME+''',''Select * from [Whitelist$]'')SELECT PHRASE FROM dbo.TBL_PHRASE WHERE TID = '+@TID+''

  where @filename and @tid are declared varchar values

  i have to use @filename as the excel file is unique every time and has a changing filename

  I get the following error :

  Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

  My server is 64 bit and i have Microsoft.ACE.OLEDB.12.0 in the provider list within linked servers - all options are unchecked except Dynamic Parameter

  I think everything else is enabled on the server

  the folder in which the file sits has rights for everyone set to full access

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  August 19, 2016 at 12:05 pm

  #1896414

  You also need to enable Allow In Process for the provider. May not completely resolve the issue but you need that enabled as well.

  Sue