"We couldn't get data from the external source" refreshing data from SQL Server in Excel


Have you ever experienced an issue where you are working in an Excel file, connected without a problem to your SQL Server cube data, but after letting the application sit idle for a time (ex. 3-6 minutes), you get an error similar to the one below when you come back and try to refresh the connection again or click on a slicer?

 ERROR:  "We couldn't get data from the external data source. Here's the error message we got: An error was encountered in the transport layer."

If you continue to try to refresh the connection again, does it then eventually return and refresh successfully?

If you've seen this before, first check to ensure your MSOLAP driver is updated on your client and your server. In addition, verify that all of your SQL Server settings such as Idleconnectiontimeout, IdleOrphanSessionTimeout, MaxIdleSessionTimeout, MinIdleSessionTimeout are set to the default values. If yes, we'd then recommend removing any encryption, antivirus or monitoring types of software from the environment and testing for improvement. As it's typically simpler to perform these types of tests on the client machine, we'd recommend testing there first, but if the issues persist, try doing the same on your server.

Additional Note:

If you've experienced an error similar to above or "Operation cancelled by user" , and the issue appears specific to something on your client machine, you may also want to install the hotfix below.

3114734 MS16-015: Description of the security update for Excel 2013: February 9, 2016
http://support.microsoft.com/kb/3114734/EN-US

Comments (0)

Skip to main content