0

I can manually run the stored procedure, but when I create job in SQL Server Agent, it is not running, execution failure.

SELECT * 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0 xml;IMEX=2;HDR=YES;Database=C:\Excel\LanConnection.xlsx;',
                'SELECT * FROM [Sheet1$]')*

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Karthik
  • 91
  • 2
  • 13
  • Have you granted permissions to the SQLSERVERAGENT account via file system permissions on the `C:\Excel` folder and its files? Additionally, is this `C:\Excel` folder on the SQL Server itself, or only on your local computer? – AlwaysLearning Oct 29 '21 at 08:02
  • Also, what is it that you think `IMEX=2` does? Only option `IMEX=1` is documented on various Microsoft pages, such as [Solution to import data with mixed data types from Excel](https://learn.microsoft.com/en-us/sql/integration-services/connection-manager/excel-connection-manager#solution-to-import-data-with-mixed-data-types-from-excel): _Add the IMEX extended property to the connection string to override the driver's default behavior. When you add the ";IMEX=1" extended property to the end of the connection string, Excel treats all data as text._ – AlwaysLearning Oct 29 '21 at 08:04
  • I have given SQLSERVERAGENT permission to that folder and corresponding file. Both are in local machine only. I tried like this `'Microsoft.ACE.OLEDB.12.0','Excel 12.0 xml;HDR=YES;Database=C:\Excel\LanConnection.xlsx;IMEX=1;'` but no luck. OpenRowSet is not working – Karthik Oct 29 '21 at 10:15

0 Answers0