Trying to create a Dataset in SSRS that executes a stored procedure that imports data from an Excel file. The sproc transforms the imported data and outputs the reformatted data for the SSRS report. (Note: I do NOT want to create a data source directly to the Excel file).
I'm using the following stored procedure to test the import. It executes successfully when run from SSMS...
CREATE PROCEDURE dbo.udp_ImportTest AS SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0' ,'Excel 8.0; Database=\\SCDATA\Import\AUM_Extract.xls' ,'SELECT * FROM [Sheet1$]') GO
I created a dataset in SSRS that executes the above sproc, which throws this error:
An error has occurred during report processing.
(rsProcessingAborted) Query execution failed for dataset 'DataSet1'.
(rsErrorExecutingCommand) 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. Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
The SSRS account has full-control permission on the folder where the Excel file resides.
Why won't this work from SSRS?