none
SQL Server Agent and OpenDataSource failing

    Question

  • I have a stored procedure that is using an OpenDataSource query to export data from SQL Server into a excel file for download. I’ve installed the “Microsoft Access database engine 2010”, and can see it listed in SQL Server Management Studio under Server Objects > Linked Servers > Providers > Microsoft.ACE.OLEDB.12.0.

    If I manually run the stored procedure (when logged in as the OS’s administrator), the stored procedure runs fine, and the export is completed successfully.

    The stored procedure is set up to be run periodically by a SQL Server Agent job.

    However, if I start the SQL Server Agent job (when logged in as the OS’s administrator), the job fails with the following error message:

    Executed as user: NT SERVICE\SQLSERVERAGENT. Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)". [SQLSTATE 42000] (Error 7303) OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error". [SQLSTATE 01000] (Error 7412). The step failed.

    I have given the “NT SERVICE\SQLSERVERAGENT” user full control permissions on the destination directory where the export file resides (note that it starts out as an empty file just with the column headers defined).

    I’ve applied the following configuration settings to SQL Server:

    sp_configure 'show advanced options', 1;
    RECONFIGURE;
    GO
    sp_configure 'Ad Hoc Distributed Queries', 1;
    RECONFIGURE;
    GO
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
    GO
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
    GO

    What am I missing? Can anyone shed any light on what might be causing the failure?

    This is running on SQL Server Web Edition 2012 on Windows Server 2012 Standard.

    Thanks!

    Monday, September 23, 2013 8:53 PM

Answers

  • Thanks for the suggestions Allen.

    I altered SQLSERVERAGENT from running as Local Service to Network Service (via the SQL Server Configuration Manager), but that still didn't resolve the issue.

    However I was successful by altering SQLSERVERAGENT to run as a local user (again via the SQL Server Configuration Manager, and supplying the user's credentials).

    • Marked as answer by JonathanT1 Thursday, September 26, 2013 5:17 PM
    Thursday, September 26, 2013 5:15 PM

All replies

  • Hi JonathanT1,

    First, I want to inform that, if SQL Server Agent log on account is “NT SERVICE\SQLSERVERAGENT”, we need to grant read and write permission to the destination folder to this account (you have done). From the following message:

    Executed as user: NT SERVICE\SQLSERVERAGENT

    It seems that you filled “NT SERVICE\SQLSERVERAGENT” in job step properties, “Run as user” option. I suggest changing it to another account who is in SQLAgentUserRole, SQLAgentReaderRole or SQLAgentOperatorRole. For more detail information, you can refer to the following link:

    Create a Transact-SQL Job Step
    http://technet.microsoft.com/en-us/library/ms187910.aspx

    SQL Server Agent Fixed Database Roles
    http://technet.microsoft.com/en-us/library/ms188283.aspx


    Allen Li
    TechNet Community Support

    Wednesday, September 25, 2013 9:41 AM
    Moderator
  • Thanks for the suggestions Allen.

    I altered SQLSERVERAGENT from running as Local Service to Network Service (via the SQL Server Configuration Manager), but that still didn't resolve the issue.

    However I was successful by altering SQLSERVERAGENT to run as a local user (again via the SQL Server Configuration Manager, and supplying the user's credentials).

    • Marked as answer by JonathanT1 Thursday, September 26, 2013 5:17 PM
    Thursday, September 26, 2013 5:15 PM
  • Hi,

    I also got same problem while extracting Excel through stored procedure using job on sql server 2014.But, my other jobs are working fine which doesn't have excel extraction.

    can any one help me out.

    Regards,
    Naeem
    Saturday, August 30, 2014 7:59 AM