How to solve this SQL Server Agent Job Error Message?

Question

I am running SQL Server 2012 and I have set up a SQL Server Agent job which will basically run a SQL query on the database and export the output to an Excel file (with xlsx extension).

When I 'parse' the query in the 'Job Properties' window to test whether my query will be running fine, I get the message 'The command was successfully parsed!'

However when I check the result of the scheduled job, it is showing me that it has failed. The error log gives the following details:

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" could not INSERT INTO table "[Microsoft.ACE.OLEDB.12.0]". [SQLSTATE 42000] (Error 7343) OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value if available. No work was done.". [SQLSTATE 01000] (Error 7412) OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "No current record."

My SQL query stands as follows:

INSERT INTO OPENDATASOURCE (
  'Microsoft.ACE.OLEDB.12.0',
 'Data Source=c:\test\myfile2.xlsx;Extended Properties=Excel 12.0 Xml')...[Sheet1$]

SELECT TOP 100 ReservationStayID,NameTitle,FirstName,LastName,ArrivalDate,DepartureDate FROM [GuestNameInfo]

I have already created the file 'myfile2.xlsx' in C:\test folder and that Excel file contains the column names from the SQL query above as their column headers. I am expecting the SQL job to fill in the Excel table with the output of the SQL query.

Any help on the issue or any pointers on whether I am doing something wrong here would be highly appreciated.

Have you tried to run your query in a query window (instead of in the agent)? Have you checked if the installed driver is the correct one (32 vs 64 bit, for the correct Office version, etc). Have you checked if the SQL Server Agent's user have sufficient permission to write to the path you want to? — Pred, Apr 27 '17 at 07:29
@Pred (1) Driver has been checked and is OK. (2) Running the query in SSMS gives me the following error message: The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" could not INSERT INTO table "[Microsoft.ACE.OLEDB.12.0]". (3) How do I check whether SQL Server Agent has sufficient permissions to write to the path? — user3115933, Apr 27 '17 at 07:40
@Pred Creating an empty Excel file (that is, with no headers) gives me the following error message from the job: Column name or number of supplied values does not match table definition. [SQLSTATE 21S01] (Error 213). The step failed. — user3115933, Apr 27 '17 at 09:30
the list of columns in the insert and the column count in the values/select list are the same? Share the query pls. — Pred, Apr 27 '17 at 10:48
In the meantime possible dupe: http://stackoverflow.com/questions/26267224/the-ole-db-provider-microsoft-ace-oledb-12-0-for-linked-server-null — Pred, Apr 27 '17 at 10:49

score 0 · Answer 1 · answered Apr 27 '17 at 14:49

SQL/Server will look in it's local c:\test. So if you have the file on your PC's C: drive and you are not running SQL/Server locally then SQL/Server won't be able to find the file on your c: drive

Also.. "Insert into OpenDataSource" is not valid SQL. Try...

Select * Into GuestNameInfo 
  From OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0',
                       'Data Source=\\ServerName\Directory\Test.xlsx;Extended Properties=Excel 12.0 Xml')...[Sheet1$]

or you could use a Temporary Table...

If Object_Id('TempDB..#GuestNameInfo') Is Not Null Drop Table #GuestNameInfo;

Select * Into #GuestNameInfo
  From OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0',
                       'Data Source=\\ServerName\Directory\Test.xlsx;Extended Properties=Excel 12.0 Xml')...[Sheet1$];

Select * From #GuestNameInfo;
Drop Table #GuestNameInfo;

How to solve this SQL Server Agent Job Error Message?

1 Answers1