Post reply

Reading an Excel 2007 file into SQL 2005 SP2

  • January 8, 2009 at 9:13 am

    #155104

    Hi All,

    After 24 hours of wasted time trying to find a solution to this problem i have finally given up and am turning to you all for help.

    Here is my problem.

    I have an excel 2007 file (Book1.xlsx) on my local c:\ in a folder called ExcelTest. I setup a linked server using the script below

    exec sp_addLinkedServer @server='PersonalTax',

    @srvproduct='ACE 12.0',

    @provider='Microsoft.ACE.OLEDB.12.0',

    @datasrc='C:\ExcelTest\Book1.xlsx',

    @provstr='Excel 12.0;HDR=No';

    This bit works fine.

    Then when i try "select * from openquery(PersonalTax, 'SELECT F1 as TaxServiceCustomers FROM [Sheet1$A1:A1]')"

    i get the following error

    Msg 7399, Level 16, State 1, Line 1

    The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "PersonalTax" reported an error. Access denied.

    Msg 7350, Level 16, State 2, Line 1

    Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "PersonalTax".

    I started to mess around with permissions on the file, the folder and the sql service user. Everyone has full rights on the file and the folder, ive tested this using 3 users and all can open the file in excel. Now the interesting part is this...

    If the sql service is local system the query works fine and returns data

    If the sql service is myself (domain/local admin) the query works fine and also returns data

    i got one of our other IT guys to login (he is a domain admin and also a local admin on my machine) - cannot access the data, same error as noted above.

    i decided to give my origional service log (sqlservice) local admin rights to see if it would work, but it still failed on the query.

    I have read close to a hundred articles on this problem on the web and nobody seems to have a solution apart from saving the file as 2003 and using the Jet provider, which i dont really want to do, but if i cant work this out then i may have to do the same. If anyone can shed any light on what may be happening i would be very grateful.

    Extra info

    OS: Windows XP SP3

    SQL 2005 Dev SP2 (no CU)

    regards

    John

  • January 8, 2009 at 3:25 pm

    #923779

    I am also open to any other suggestions of how i could read this data into SQL/ from SQL. I only have SQL 2008 workgroup so i dont get SSIS which has the option of importing excel 2007 files, i have 2005 standard but that doesnt have an option to import 2007.

    I have tried openrowset and also get very similar failure results (i have enabled the SAC option too so its not that).

    I cant do any coding in C/VB etc so thats also out of the question 🙁

    A fairly simple problem and sooooo much frustration! :w00t:

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply