Update: I have added some links to tools that should help you overcome this limitation.
We were told that Excel Services has support for displaying data from external locations, such as SQL server, and that's great! Until you try it out and fail..
Try creating a simple excel document with a Table that displays data from a SQL server table . Now try to view it in Excel Web Access. You will fail with the following error:
The thing is, Excel services supports displaying data from external data sources, but not in a table format, only in a pivot table! How lame is that...
Now try converting that table into a pivot table.. it works!
Here are links to 2 tools that converts pivot tables to tables (Courtesy of Shahar Prish):
http://blogs.msdn.com/cumgranosalis/archive/2006/11/03/query-tables-work-around-for-excel-services.aspx
http://blogs.msdn.com/cumgranosalis/archive/2006/11/27/another-tool-for-working-around-excel-services-not-supporting-querytable.aspx
[This is the error text for search engines: Unable to Load Workbook The workbook that you selected cannot be loaded because it contains the following features that are not supported by Excel Services: External data ranges (also called query tables) Contact the workbook author.]
Today Microsoft has announced a new product called Microsoft Search 2008.
As you can guess it is an enterprise search solution. Very much like Moss Search, and it's FREE! (the express edition).
More info on this here: http://www.microsoft.com/enterprisesearch/default.aspx
This is how Microsoft's Search products family looks today:
www.MSD2D.com was one of the good resources for technical articles out there.
As of 2.11.07 it is being relaunched as tow separate communities, one of them targeted only at SharePoint stuff!
The other community is about .net related content (www.WindowsDevPro.com).
So, if you are into SharePoint check out the new www.OfficeSharePointPro.com . You can use your old UserName and Password from msd2d in the new communities.