0

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?

  • The error is thrown at the point the stored procedure calls OPENROWSET, so there is no table returned. – SDBurgher Jan 2 '18 at 19:02
0

Something Tells me that you are trying to populate a table using the file that you are doing OPENROWSET with. This is a case for SSIS not SSRS, so please try that instead.

Anyway, try givign SQL Server full permission on the folder where the ACE.OLEDB.12.0 is located. Here is a good blog post to find out how: https://visakhm.blogspot.ae/2013/12/how-to-solve-microsoftaceoledb120-error.html

  • Yes, I am trying to populate a table with the results of the OPENROWSET. I've done this with no problem in several other sprocs, but this is the first time I've tried executing the sproc thru an SSRS dataset. I prefer not to use SSIS. Yes, I have assigned full control to the temp folder to the SSRS user account. What I have since discovered is the import succeeded when I changed the SSRS report data source credentials from Windows Authentication to my SQL Server login. I believe I need to register an SPN for the SSRS service account in order for Windows Auth to work. Does that make sense? – SDBurgher Jan 5 '18 at 16:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.