Trouble with ACE.OLEDB.12.0

• BenWard

  SSCertifiable

  Points: 5903

  More actions

  October 26, 2015 at 10:12 am

  #399228

  Hello,

  I've been happily using OPENROWSET with Microsoft.Jet.OLEDB.4.0 to open CSV files interactively in SSMS and recently had cause to attempt the same with an xlsx file.

  I've installed the 'AccessDatabaseEngine.exe' package from microsoft.com and now have 'Microsoft.ACE.OLEDB.12.0' available as a 'Provider' under 'Linked Servers'

  I'm a local admin on the server.

  'Allow Inprocess' is enabled.

  select * from openrowset

  ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=NO;Database=f:\flatfiles\path_to_my_file.xlsx','select * from [Sheet1$]')

  This returns the following error;

  Msg 7357, Level 16, State 2, Line 1

  Cannot process the object "select * from [Sheet1$]". The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

  There is a tab called 'Sheet1' which is full of data.

  Am I being particularly thick here?

  Thanks

  Ben

  Ben

  ^ Thats me!

  ----------------------------------------
  01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
  ----------------------------------------