.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Excel Services no refresh data & no error logs

Posted By:      Posted Date: September 14, 2010    Points: 0   Category :SharePoint
Hi! I'm trying to publish an Excel workbook which loads data from SQL Server. The result is: no data loaded in the workbook and no error logged on Sharepoint logs. Notice that if I try to refresh the workbook in Office Excel all works fine! I try to set up Windows Authentication, None (to use unattended account) and with .odc file but with no result! The procedure I follow is: 1) create a new Excel file and create new data connection to the database (SQL Server 2008) 2) upload the file in the trusted file location 3) open the file in a web part and click on the "refresh all" button I used the same unattended account which I setup for this purpose with BCS and it works fine! I also try to set with PowerShell the Application ID created ("ES"): "set-spexcelserviceapplication -identity 6d871d92-19dd-4421-b33b-0012f9a5ea18 -unattendedaccountapplicationid ES" with no result. I noticed that the identity of the Excel Service application shown in the Central Administration isn't in the IIS Application Pool list: could it be the problem? I also tried to delete / re-create the application but the two IDs remains disequal! Have someone of you already worked with Excel Services and could give me any help? Thanx in advance, Elena

View Complete Post

More Related Resource Links

Excel Services data refresh from SSAS: How to?


Hello everybody,

I know there's been several threads already on this topic, but I haven't been able to distill a solution to my particular situation. The problem is largely because of my ignorance on the SharePoint part, I'm afraid.

Anyway, here's the situation:

  • SharePoint 2010 Ent in Stand-Alone mode
  • Windows Server 2008
  • Analysis Services 2008 R2 on the same box
  • No windows domain, all people using the system are outside the company
  • All users are created as local windows users
  • We're using the msmdpump to allow access to Analysis Services
  • Excel 2010 is the client for SSAS
  • The user id and password are both stored in the ODC file
  • The user in the ODC is member of a role that allows read-access to the cube
  • The ODC is in a trusted location
  • Since I want Excel Services to use the connection string, the Authentication setting is set to None in the ODC

Now, if I publish an Excel Pivot table to SharePoint and the try to open it in the browser, the initially cached data is displayed. As soon as I try to refresh the data, I get a Data Refresh Failed error. I've been reading for two days straight, and I think I understand that I need to configure the unattended service account using the Secure Store Service.

This is where I'm getting confused. i

visio services - can't get rid of data refresh error


keep getting this error message on trying to refresh data

"Viso services was unable to refresh external data connections in this web drawing because of changes in teh structure od an underlying data source. one or more columns in this data source have been deleted after this web drawing was last saved."

The underlying data source has not been changed and I have tried re-creating the visio web drawing from scratch - always this error!

any ideas on how to progress?



Cannot refresh OLAP-based data in Excel Services workbook in browser (SP 2010 / Project Server 2010)



In SharePoint 2010, Project Server 2010, Business Intelligence Center, upon attempting to refresh OLAP data in an Excel chart in the browser, the following error appears:

An error occurred during an attempt to establish a connection to the external data source.  The following connections failed to refresh:

My ODC Connection Name

We have loaded and configured Project Server 2010 according to spec (http://technet.microsoft.com/en-us/library/ee662109.aspx).  Excel Services is configured according to spec (global settings, trusted file locations, trusted data providers and trusted data connection libraries).  The Secure Store Service is configured with the ProjectServerApplication target application. 

Non-OLAP data refreshes of spreadsheet data work fine in the browser.  OLAP data refreshes in the Excel client itself work fine.  Only the OLAP refreshes in the browser fail.  The problem occurs whether using a Microsoft ODC or one that we created (saved in a trusted location).

The ULS log has only one error, which is:

06/21/2010 13:47:17.86  w3wp.exe (0x1238)               &

Excel: Integrate Far-Flung Data into Your Spreadsheets with the Help of Web Services


Excel 2003 lets you dynamically integrate the data provided by different Web services. It also lets you take advantage of the latest capabilities in Office 2003 to customize list views, graphs, and charts, and to catalog bulk items online or offline. Find out how you can makle the most of the data returned from your Web services with the Office 2003 Web Services Toolkit API.

Alok Mehta

MSDN Magazine February 2005

Consuming External Data Using SharePoint Server 2010 Business Connectivity Services and an Excel 201

Learn how to use BCS in SharePoint Server 2010 to access and update external data by using Microsoft Excel 2010 as a client.

Excel Data Source SSIS AcquireConnection failed 0x80004005 unspecified error

Nuances of using Excel data sources with SSIS Your Development Machine Setup Needs Jet Drivers, available along with the default Office install so if you have Excel working fine locally, this is enough. Installing the "Office Data Connectivity Components" (download file name AccessDatabaseEngine.exe) as well as having Office installed corrupts the Jet Driver stack so that SSIS in VS 2008 cannot create the object to read the XLS file. You will see the title error "Unspecified Error" and "80004005" code. Resolution: Uninstall the Jet Engine pack on your development XP machine if you have Office. It will be listed as something like "Microsoft Access Database Engine (2010)". Run Office 200x setup with the Repair option and reboot. Try configuring the task in SSIS and it should be able to read the Sheet names again and columns.  Live Server (32-bit and 64-bit) Deploying your SSIS package to a server without Office 200x means it will not have the Jet Engine drivers to read XLS, XLSX files. You must install the 32-bit Office Data Connectivity Components - Jet Drivers mentioned above found here  (http://www.microsoft.com/downloads/details.aspx?familyid=7554f536-8c28-4598-9b72-ef94e038c891&displaylang=en). There are no 64-bit drivers; therefore regardless of 32 or 64-bit live environments, this step is the same. You shoul

Excel Services 2010 Error - "The workbook cannot be opened."

I enabled Excel Services and added a Excel Web Access viewer to my page. It's just a simple spreadsheet (which incidentally works fine on my standalone development server) that displays the error "The workbook cannot be opened." when the page loads. The trusted file locations are set up appropriately. We are using Kerberos authentication. My event viewer showed this "Critical" event (ID: 3760): SQL Database 'Prod_WSS_Content' on SQL Server instance 'servername' not found. Additional error information from SQL Server is included below. Cannot open database "Prod_WSS_Content" requested by the login. The login failed. Login failed for user 'domain\user'. In other words, the service account that ECS was running under did not have permission to the content database. So I granted the user read permission on the database. Then I got the same error and this "Error" level event in the event viewer (ID: 5617): There is a compatibility range mismatch between the Web server and database "Prod_WSS_Content", and connections to the data have been blocked to due to this incompatibility. This can happen when a content database has not been upgraded to be within the compatibility range of the Web server, or if the database has been upgraded to a higher level than the web server. The Web server and the database must be upgraded to

workbooks inside of sharePoint site has data refresh error


We have workbooks inside of our SharePoint site, using NTLM authentication and Excel Services connectors. They correctly display graphs when the page is first opened, but all of them throw an error when users try to trigger an update. The error message sayss "Excel. An error occurred during an attempt to establish a connection to the external data source. The following connections failed to refresh: [name of any data cube in use]. Learn more about data refresh." We are not using Power Pivot (which may make it easier, but is not authorised for use by our server administrators yet).

2010 Visio Services Data Auto Refresh Issue


I have published vdw diagram in Sharepoint 2010 Shared Documents. The diagram data is linked to SQL database.

When I view the diagram in the browser, unless I click dedicated "Refresh" button on the top left at the sharepoint page, data is not updating.

I have changed all the SP 2010 Global settings to minimal value = 0 (as per previous post called 2010 Visio Services Data Refresh Issue).

Still you need to click dedicated "Refresh" button on the top left at the sharepoint page in order to see updated data.

In the article called "Visio Services for SharePoint 2010" is stated that new Visio service supports AUTOMATIC REFRESH.

Am I missing something? Is it possible to have a visio diagram with AUTOMATIC REFRESH published in SharePoint 2010?

I can do some workarounds like publishing in web parts page that has auto pooling, but my understanding was that the Visio diagram refresh is event based on data change only - means if data do not change 10 minutes there will be no calls?



Excel services external data source connection


can't connect to an excel services data source on the sharepoint 2010

Error : ... can't connect to external datra source .....

Excel Services data connection issue. Single environment, none authentication


I really need someone's help to configure my SharePoint 2007/ Excel services connectioon to SSAS 2008. It surely cannot be that complicated???

MOSS on server 1
SSAS 2008 cube on server 1
Trusted data provider created (OLE DB, MSOLAP.4)
Trusted data connection library created
Trusted File location created - children trusted, allow external data = "Trusted data connection libraries and embedded"
Unattended Service Account - setup

I use Excel and create a new .odc file with athenticated type = NONE and save it to the Trusted data connection library. Using the odc I open a new workbook. I firstly get a security warning saying that "MS Office has identified a potential security concern. Data connections have been blocked. If you choose to enable data connections..."

I click on Enable, and now have access to my cube. I create a pivit table and chart and publish the workbook back to a child folder in the Trusted File location. All okay so far!!

Now I open the new workbook, and as soon as I try and interact with it, I get the dreaded error "Unable to retrieve external data for the following connections:


The data sources may be unreachable, may not be responding, or may have denied you access

Verify that data refresh is enabled for the

SSAS 2008 data refresh problem in Excel 2007



My problem sounds like that:

I have some cube created with SSAS 2008 and I'm using Excel 2007 as browser to view cube data.

I made pivot table with nessesary data, everything works fine till I save excel file and sent it to my client.

When client uses some filter, some row measures disapear. Dimension data are shown, measures are blank cells, but calculations from those measures is filled.

e.g. I see difference of sales between years in percent, but I can't see measures, which were used to count that difference, because in excel I see blank cells.

Any ideas?

Excel Services establish a connection to the external data source


Hello All,

I am getting below error when I click Slicer in excel 2010 powerpivot chart.

An error occurred during an attempt to establish a connection to the external data source. The following connections failed to refresh:

PowerPivot Data

I am using SQL Analysis services cube to fetch data into my powerpivot using windows authentication mode.

Appriciate any help.




Integration services(data flow error)



when executing my data flow package that contains only one source and one destination

OLE db source -> SQL server destination

the following errors occurs in my output

Error: 0xC0202009 at Data Flow Task(infraction action), SQL Server Destination [3600]: An OLE DB error has occurred. Error code: 0x80040E14.

Error: 0xC0202071 at Data Flow Task(infraction action), SQL Server Destination [3600]: Unable to prepare the SSIS bulk insert for data insertion.

Error: 0xC004701A at Data Flow Task(infraction action), DTS.Pipeline: component "SQL Server Destination" (3600) failed the pre-execute phase and returned error code 0xC0202071.

i've checked the structure of my source and destination table but nothing seems to be wrong

if someone have ever faced these errors help me :D

SQL Express R2 - Import / Export Data (32-bit) - Excel - Unexpected Unrecoverable Error


Preface - I'm BRAND NEW to SQL! Bought a book and working to learn :) I've have a lot of experience in Access and other msft titles.

I've installed SQL Express 64 bit on Win 7 (64) and I'm trying to setup my 1st table to start practicing running queries on and I’m having nothing but trouble!! I'm trying to use the import / export wizard to import Excel '07 spreadsheet that has a table of data and I CANNOT get through this without crashing! EVERY SINGLE TIME!

My Steps:

1.  Open Wizard

Error inserting data in Excel spreadsheet from SQL 2005 SP



I am using a SP on a SQL 2005 server to insert data into an Excel spreadsheet.

If I run the following query:



INTO OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source ="\\wvns01\data\Finance\ZIP\FY2011_testZIP\BankFile.xls";extended Properties=Excel 8.0')...

Using the Excel Services REST API to Access Excel Data in SharePoint Server 2010 (Visual How To)

Learn how to use the REST API to retrieve resources such as ranges, charts, tables, and PivotTables from workbooks stored on SharePoint Server 2010.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend