none
Error using OpenRowSet

    Question

  • Running this script, I get error message :

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

    use

    master

    go

    DECLARE

    @dosStmt VARCHAR(200) =

    'copy C:\Users\dlivelsb\Documents\empty.xlsx C:\Users\dlivelsb\Documents\BRPDaveReport.xlsx'

    EXEC

    master..xp_cmdshell @dosStmt

     

    insert

    into OPENROWSET('Microsoft.ACE.OLEDB.12.0',

    'Excel 12.0;Database=C:\Users\dlivelsb\Documents\BRPDaveReport.xlsx;'

    ,

    'SELECT * FROM [Sheet1$]'

    ) SELECT country, ProductLineCode, AllocatedFunds, ClaimsPaid, AvailableFunds, PercentUsage

     

    FROM [Coop].[dbo].[RptFundsReimbursed]

     

    ORDER BY SortOrder

    If a member of my team (that has admin rights to the SQL Server) runs this script, it works. What permissions do I need on the server to run this script? I don't think I should have to be an admin? I appreciate any help you can give me.

     

    Monday, June 27, 2011 12:33 PM

Answers

  • Thank you Shenq and Stefan.

    Can you please explain why members on my team (that have admin rights to the server that has SQL Server) can run the script, but I cannot? I do not have admin rights on the server.

    • Marked as answer by mh53j_fe Wednesday, July 06, 2011 3:31 PM
    Thursday, June 30, 2011 6:25 PM

All replies

  • Search for that, the most common cause: You're running a SQL Server 64bit and have only installed the 32-bit driver.
    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Monday, June 27, 2011 1:14 PM
  • Stefan,

    I asked my systems administrator to install the 64 bit version of the Microsoft Access database engine on the SQL Server. At this time, I can't confirm if he installed the 32 bit or 64 bit version.

    I checked the server, and the database engine build is 14.0.4763.1000

    Are you saying this build is the 32 bit driver?

    Monday, June 27, 2011 1:23 PM
  • Build number doesn't tell 32bit or 64bit. If I'm not mistaken, Microsoft.ACE.OLEDB.12.0 is a 64 bit only OLE DB provider. Hence please make sure you're using a 64 bit SQL Server(use taskmgr.exe to check it).

    Please also make sure that your SQL Server process has access permission to those .xlsx files.

     

    Wednesday, June 29, 2011 9:56 AM
  • Thank you Shenq and Stefan.

    Can you please explain why members on my team (that have admin rights to the server that has SQL Server) can run the script, but I cannot? I do not have admin rights on the server.

    • Marked as answer by mh53j_fe Wednesday, July 06, 2011 3:31 PM
    Thursday, June 30, 2011 6:25 PM