My objective: Execute a stored procedure from an SSRS report that will import data from an Excel file in a network folder, transform the data, and output to a SQL table. The output table will then be linked to an SSRS dataset. **Note: I do NOT want to link the Excel file directly to the SSRS report as a data source.** Attempts so far: I've created a test sproc "udp_ImportTest" that simply imports the spreadsheet and writes the results to the output table with no transformation. This sproc is executed by a SSRS dataset. I tried 2 different approaches and both work perfectly when executed from SSMS but both fail when invoked from SSRS. **Approach 1**: Import the spreadsheet using OPENROWSET... CREATE PROCEDURE dbo.udp_ImportTest AS TRUNCATE TABLE temp_AUMExtract; INSERT INTO temp_AUMExtract SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0' ,'Excel 8.0; Database=\\SCDATA\Import\AUM_Extract.xls' ,'SELECT * FROM [Sheet1$]') SELECT * FROM temp_AUMExtract GO Result: Output table filled and displayed successfully However, when I tried to create a Dataset in SSRS... ![alt text] It throws this error: ![alt text] **Approach 2**: So I tried creating a linked server rather than use OPENROWSET... 1. *Create Linked server* EXEC sp_addLinkedServer @server= N'AxysAUMExtract1' ,@srvproduct = N'Excel' ,@provider = N'Microsoft.ACE.OLEDB.12.0' ,@datasrc = N'\\SCDATA\SCMAdmin\Import\AUM\AUM_Extract.xls' ,@provstr = N'Excel 8.0; IMEX=1; HDR=No' ; SELECT * FROM AxysAUMExtract1...[Sheet1$] Result: Output from linked server successful. 2. *Change OPENROWSET to Linked Server in the udp_ImportTest stored procedure:* TRUNCATE TABLE temp_AUMExtract; INSERT INTO temp_AUMExtract SELECT * FROM AxysAUMExtract1...[Sheet1$] SELECT * FROM temp_AUMExtract ; Result: output successful But when I tried again to create SSRS dataset that calls udp_ImportTest I got the same error (7399) as above. I have followed all of these steps that I picked up from previous posts: 1. Download and install “AccessDatabaseEngine_x64.exe” on the server. 2. Enable the "show advanced options" and “Ad Hoc Distributed Queries” configuration settings 3. Register the Microsoft.ACE.OLEDB.12.0 provider for AllowInProcess & DynamicParameters properties 4. Add a linked server login for the SQL Authenticated account 5. Give access to Read, write and execute permission to Temp directory to the Service account of SQL Server. Is there a way to make this work? : /storage/temp/4439-cimagesdatasetproperties.jpg : /storage/temp/4440-cimagesssrs-linkedserver-error.jpg
Hi Oleg. As I mentioned above, the OPENROWSET works fine when called from SSMS. Excel 8.0 reads xls files. I change it to 12.0 when I read xlsx files. And yes, I granted full control access rights to the Temp folder to the SSRS service 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. Upon further research, I think I need to register an SPN for the SSRS service account in order for Windows Auth to work. I tried executing a SetSPN from the server's command line... Setspn -s http/
SCMSQL.companyname.com:80 MSSQLSSRS ...but was blocked by "insufficient access rights". Still trying to figure out how to get around that.