none
Where must oledb providers be installed?

    Question

  • I've installed Office 2007 Data Connectivity Components and Access Database Engine 2010 Redistributable on the server.  I've created the following stored procedure and can execute it successfully in SSMS on the server.

    CREATE PROCEDURE [dbo].[usp_POLineFollowUpStaging_Insert]
    (
     @File VARCHAR(100) = NULL
    )
    AS
    SET NOCOUNT ON
    DECLARE @SQL VARCHAR(2000)

    SET @SQL =
    'SELECT *
    FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0''
    , ''Excel 12.0;Database=\\domain\FilePath\' + @File + ';''
    , ''SELECT * FROM [Sheet1$]'')'

    EXEC(@SQL);

    However, when I try to execute this procedure in SSMS on a client it fails.  I would like to understand why it fails when run on a client.  I'm guessing it's because Office 2007 Data Connectivity Components and Access Database Engine 2010 Redistributable are not installed on the client.  Since the procedure, components, and redistributable are all on server I thought it would work.


    Kevin

    Monday, April 07, 2014 8:00 PM

Answers

All replies

  • Hello,

    The Microsoft Access Database Engine 2010 Redistributable will do the trick. Install this on the client computer.

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    Monday, April 07, 2014 8:44 PM
  • Thanks for the reply Albert.  I forgot to mention that I have Office 2013 installed on the client.  Do you happen to know if installing the Microsoft Access Database Engine 2010 Redistributable will cause any problem?  I don't want to mess up my Office install.

    The instructions for installation say -

    "Note: Before installing this download, you must first remove any previous versions of the Access Database Engine from your computer by using Add/Remove Programs in the Control Panel."

    http://www.microsoft.com/en-us/download/details.aspx?id=13255

    Also, I would still like to understand why the driver must be installed on the client when the driver and sp are on the server.  I thought stored procedures performed all processing on the server and therefore would use the drivers on the server.


    Kevin

    Monday, April 07, 2014 9:13 PM
  • Hello,

    For sure won’t cause any problems.


    The installation instructions on the link you provided specified that redistributable package installs the Microsoft.ACE.OLEDB.12.0 driver.

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com


    Monday, April 07, 2014 10:17 PM
  • Thanks Albert. 

    I would still like to understand why the driver must be installed on the client when the driver and sp are on the server.  I thought stored procedures performed all processing on the server and therefore would use the drivers on the server.

    I would just like to understand how this works.


    Kevin

    Monday, April 07, 2014 11:59 PM
  • Hello,

    You need that version of the driver to be able to access/open the Excel using OPENQUERY.


    Hope this helps.


    Regards,

    Alberto Morillo
    SQLCoffee.com

    Tuesday, April 08, 2014 10:55 AM
  • You mention it fails when run from the client.  What is the error message?   Does the user have permissions to access the share?


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Tuesday, April 08, 2014 11:39 AM
  • Hi Dan,

    I'm a domain admin and have permission to the folder.

    When I'm on the server and run EXEC usp_POLineFollowUpStaging_Insert 'FileName.xlsx' I am able to read the file.

    When run from the client I get

    Msg 7399, Level 16, State 1, Line 1
    The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
    Msg 7303, Level 16, State 1, Line 1
    Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

    I hope my question is clear.  I just want to understand why the driver is required on the client.  If an application (in this case SSMS) creates a connection to the server (which has the driver installed) and executes a stored procedure on the server, why is the driver required on the client?


    Kevin

    Tuesday, April 08, 2014 2:22 PM
  • I think I've been confused about what is happening.  Here's more detail about my tests.

    On the server logged in as domain admin-

    Microsoft Access Database Engine 2010 Redistributable installed

    Running SSMS as the domain admin

    OPENROWSET works

    On the client logged in as myself-

    Microsoft Access Database Engine 2010 Redistributable installed

    Running SSMS as myself

    OPENROWSET fails

    On the client logged in as myself-

    Microsoft Access Database Engine 2010 Redistributable installed

    Running SSMS as sa

    OPENROWSET works

    So it appears it is a permissions issue.  BOL mentions the ADMINISTER BULK OPERATIONS permissions but I'm not using the BULK option.  Now the question is how to enable this for a non-sa user?


    Kevin

    Tuesday, April 08, 2014 8:52 PM
  • So it appears it is a permissions issue.  BOL mentions the ADMINISTER BULK OPERATIONS permissions but I'm not using the BULK option.  Now the question is how to enable this for a non-sa user?

    Are you using Windows authentication for the non-sa user?  Does the non-sa user have permissions to the share?


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Wednesday, April 09, 2014 12:29 AM
  • Yes - Using Windows authentication for the non-sa user.  The non-sa user has permissions to the network folder.

    So for some reason the sa user can execute the sp with a UNC path in it (\\domain\FilePath\) but the non-sa user cannot.

    If I change the path in the sp to just C:\Temp both users can execute it successfully.

    I would like to use a UNC path if possible.  It seems odd that sa (a non-Windows user) can use the UNC but the non-sa user (Windows user) cannot.


    Kevin

    Wednesday, April 09, 2014 3:17 PM
  • The SQL Server service is running under LocalSystem.  I'm thinking that's the problem but I'm still not sure why sa can use the UNC.


    Kevin

    Wednesday, April 09, 2014 4:42 PM
  • I believe SQL Server authenticated sysadmin users (e.g. 'sa') access Windows resources under the context of the SQL Server service account.  That would be the machine account in the case of Local System.  Is your Windows account a sysadmin role member?  Does it work if you launch SSMS with "Run as Administrator" using your account?


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Friday, April 11, 2014 11:47 AM