Using SQL SERVER data connection in Excel Services
I generated a spreadsheet using Excel 2007 which only included a PivotTable and it's data was from SQL SERVER database. My sharepoint server and database server cubes were the same machine. I tried to publish the spreadsheet onto Excel Services and refresh it, but it always crashes. Here are following message:
"The data sources may be unreachable, may not be responding, or may have denied you access.
Verify that data refresh is enabled for the trusted file location and that the workbook data authentication is correctly set."
I have configured "Trusted file locations","Trusted data providers" and "Trusted data connection libraries". However, above issue exists also, Look forward to your feedback.
thank you in advance
- Edited byMike Walsh MVPMVP, ModeratorWednesday, October 01, 2008 6:22 PMExcel Service replaced with the correct Excel serviceS
Answers
Configuring Excel Services data access can be tricky.
You can follow the instructions that exist in this article on technet - once you do and it still does not work, please come back and give some more information (the event log should give some more info on what's going on).
All Replies
Configuring Excel Services data access can be tricky.
You can follow the instructions that exist in this article on technet - once you do and it still does not work, please come back and give some more information (the event log should give some more info on what's going on).
- Did this post address the issue?
- I am having a similar issue. The error being the following:
"The query did not run, or the database table could not be opened.
Check the database server or contact your database administrator. Make sure the external database is available and hasn't been moved or reorganized, then try the operation again."
I changed the query to a stored procedure that runs about 50 seconds. I have tried putting
Connect Timeout=0 and Command Timeout=0 in the string but neither have worked. Both procs run fine through SQL. I did not see where the article listed above offered any help regarding this issue.
The properties below work for the short proc but do not work for the longer running one:
Connection:
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=TESTDB;Data Source=SQ01;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=WKSTNID1;Use Encryption for Data=False;Tag with column collation when possible=False
Command Type: Default
Command Text:
EXECUTE test_short_proc
Any help would be appreciated. - Did you ever get a resolution FK1? I am having the same issue now
