Occasionally I’ve ran into this problem as part of a SharePoint installation. Typically this is with Excel Services – but since Excel Services is so central to the PowerPivot architecture, we run into this too. The ULS shows something like:
Medium ExcelServiceBase.BeginProcessOperation: Caught an exception: System.TypeInitializationException: The type initializer for ‘Microsoft.Office.Excel.Server.CalculationServer.ExcelServerApp’ threw an exception. —> System.IO.FileLoadException: Loading this assembly would produce a different grant set from other instances. (Exception from HRESULT: 0×80131401) at Microsoft.Office.Server.Diagnostics.ULS.SendWatsonOnExceptionTag(UInt32 tagID, ULSCatBase categoryID, String output, Boolean fRethrowException, TryBlock tryBlock, CatchBlock catchBlock, FinallyBlock finallyBlock) at Microsoft.Office.Excel.Server.CalculationServer.ExcelServerApp..ctor() at Microsoft.Office.Excel.Server.CalculationServer.ExcelServerApp..cctor() — End of inner exception stack trace — at Microsoft.O… 555ec6e6-4a53-49cf-8cb7-4b8614a8fa21
10/14/2010 11:56:42.20* w3wp.exe (0×1E38) 0×25B8 Excel Services Application Excel Calculation Services ecd1 Medium …ffice.Excel.Server.CalculationServer.ExcelServiceBase.SafeSetContext() at Microsoft.Office.Excel.Server.CalculationServer.ExcelServiceBase.BeginProcessOperation(CommandParameter parameter, WebMethodBehaviorAttribute webMethodBehavior, WebMethodType webMethodType, AsyncCallback callback, Object state, UserOperation operation) 555ec6e6-4a53-49cf-8cb7-4b8614a8fa21
10/14/2010 11:56:42.20 w3wp.exe (0×1E38) 0×25B8 Excel Services Application Excel Calculation Services d64g Medium UserOperation.Dispose: Disposing Microsoft.Office.Excel.Server.CalculationServer.Operations.EmptyOperation, WebMethod: . 555ec6e6-4a53-49cf-8cb7-4b8614a8fa21
What I believe is happening is that as part of the installation process, an object is being registered in a different app domain (if you follow the long convoluted threads on this topic from the web). On a reboot this is cleared up. I’ve never been able to discover the circumstance root cause – but always a reboot has solved it.
For most problems with the Gallery snapshot problem, there are entries in the ULS – but sometimes there are no items or if there are, there is just the “Timeout Exception”. Under these circumstances there is so little to go on that it is hard to figure out how to even get started. Here are some of the general ‘how to get started’ recommendations that I have:
- First and most importantly, the rendering engine for the entry must be running.
- For workbooks, Excel Services must be running. The way that snapshots are taken is that a web browser is brought up by the GetSnapshot.exe process and inserted into the document library as a collection of images (representing the worksheets in the workbook).
- For reports, Reporting Services must be running in SharePoint integrated mode so that the .rdl files can be rendered in a browser.
- Go into the site collection and look to see if the PowerPivot feature is enabled.
- Sometimes you will find that it is disabled. Typically this happens if the user starts from the “Blank” Site template. For most templates, such as the Team Site, we use feature stapling so that the PowerPivot feature is automatically enabled. However the Blank Site is specifically designed by SharePoint as having all features disabled. So if the end-user starts with the Blank Site and then adds a PowerPivot Gallery (which is a document library), you will find that snapshots will just never happen.
- Sometimes even if the PowerPivot feature is enabled, sometimes the SharePoint event handler just stops firing. The GetSnapshot.exe process is started from the Gallery event handler (when a new document is added or if an existing document is being updated). If GetSnapshot.exe isn’t being started (see http://powerpivotgeek.com/2010/10/24/snapshots-not-being-taken/ for another reason why it might not be started, but at least it had an error being recorded) then it could be an issue with the event handler. The workaround that we have found works is to deactivate and then re-enable the PowerPivot site collection level feature.
- Look to see if GetSnapshot.exe process starts. If it does, the ULS should contain something, even if it just a Timeout Exception. If this is happening:
- Gallery should set the doclib advanced setting "Opening documents in the browser" from "Use the system default" to "Open in the browser" – we are finding that when some apps are installed on SharePoint, that the default value is being changed to “Open in client application”
- If all else fails, log onto the server interactively using the web application app pool service account. Then try to view the file that you are having problems with. As the first item above mentions, the rendering engine must be working. It may be that there are issues with the web application app pool service account that is stopping the browser control from rendering the content.
Hope this helps.
Recently I ran into an interesting customer situations with Gallery snapshots not being taken – just an hourglass is showing (meaning that the snapshot generation process is on-going, i.e. the GetSnapshot.exe process is running).
The problem is visible in the ULS log with this error:
w3wp.exe (0×1508) 0×0CF4 SSAS Mid-Tier Service Report Gallery 99 High EXCEPTION: System.Exception: CreateProcessAsUser failed with 1058 at Microsoft.AnalysisServices.SharePoint.Integration.SnapshotExtraction.InternalSnapshotExtraction(SnapshotExecutionContext context, Int32& snapshotToolExecutionMask) at Microsoft.AnalysisServices.SharePoint.Integration.SnapshotExtraction.ExtractSnapshots(SnapshotExecutionContext context) at Microsoft.AnalysisServices.SharePoint.Integration.SnapshotHandler.CaptureSnapshot(ListItemInfo itemInfo, WorkbookConnections connections)
The error 1058 can have as a root cause the fact the Windows service “Secondary Logon” is disabled.
The service as to be at least in manual (the service will be started by the call to CreateProcessWithToken if the permissions permit it).
Note: To totally solve the issue, the server needs to be restarted and the properties on the documents to be changed in order to generate a new snapshot.
1 — Is this a single machine farm? Will it be used for departmental use? Then go for “New Server”: http://msdn.microsoft.com/en-us/library/ee210708.aspx This is NOT a standalone machine in SharePoint terms (PowerPivot does not support standalone SharePoint installations) — It is a full farm with all the capabilities of a farm, it is just that we have a single machine so far.
Another interesting resource is a 4-part video series available here: http://social.technet.microsoft.com/wiki/contents/articles/install-powerpivot-for-sharepoint-on-a-domain-controller.aspx. This shows you how to install a single “All-In-1″ standalone PowerPivot server with the domain controller role installed, SharePoint, and a “New Server” installation. Very useful if you want to build your first demo system; particularly if you want it installed on a laptop (you must have a domain controller if you disconnect from the network).
2 — If you change your mind and wish to later *add* a server to this single server farm, then use: http://msdn.microsoft.com/en-us/library/ee210610.aspx for them — However you might want to move your RDBMS off to another machine prior to doing that. Look to the SharePoint BOL for how to move the content and config databases to another server in the farm.
3 — Is this an existing SharePoint farm? If Yes, then is this the first server you are installing PowerPivot? If so, use: http://msdn.microsoft.com/en-us/library/ee210616.aspx Is this the 2nd or later server you are installing PowerPivot? If so, use: http://msdn.microsoft.com/en-us/library/ee210610.aspx
If there is any error trying to load the bubble chart data for the dashboard, instead of getting a page load failure, the bubble chart simply displays a red X. Whenever you get a red X in the bubble chart there will be an associated log entry in the ULS log on the machine running the Central Admin site. It will look something like this:
SSAS Mid-Tier Service Administration 106 High Error loading history for workbook history bubble chart
SSAS Mid-Tier Service Administration 99 High EXCEPTION: Microsoft.AnalysisServices.AdomdClient.AdomdUnknownResponseException: Unsupported data format : application/vnd.ms-excel.12
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.SendMessage(Boolean endReceivalIfException, Boolean readSession, Boolean readNamespaceCompatibility)
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.Discover(String requestType, String requestNamespace, ListDictionary properties, IDictionary restrictions, Boolean sendNamespacesCompatibility)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.AdomdConnection.IXmlaClientProviderEx.Discover(String requestType, IDictionary restrictions, InlineErrorHandlingType inlineErrorHandling, Boolean sendNamespaceCompatibility)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.ConnectToXMLA(Boolean createSession, Boolean isHTTP)
at Microsoft.AnalysisServices.SharePoint.Integration.WorkbookHistoryDataProvider.GetHistory(WorkbookHistoryDataSet& historyDataSet)
at Microsoft.AnalysisServices.SharePoint.Integration.WebServices.PowerPivotOperationsServiceImpl.GetWorkbookHistory(WorkbookHistoryDataSet& history) 1118bdcb-bf75-4f0d-b560-ede92ed25d1b
The key string that you are looking for is “Error loading history for workbook history bubble chart”. The exception causing the error should immediately follow that entry for logging on that thread and should hopefully give you some indication as to why the chart didn’t load (it is what I look at). In the majority of cases I have seen, the issue is simply not having the latest version of the ADOMD.Net client library and all you need to do is upgrade your version of the library to that of SQL Server 2008 R2 and the error will go away. Our code is actually meant to provide a user friendly error when SQL Server 2008 R2 Analaysis Services ADOMD.Net library is not installed on the server hosting SharePoint’s Central Administration site. However, as of the most recent RC build (and so most probably the RTM build), SharePoint is installing ADOMD.Net from an earlier version of SQL Server and so this user friendly error is not always coming through and users might simply get a red X. To know if that is what you are hitting, you can either look in the Control Panel’s Programs List and see if the SQL Server 2008 R2 Analysis Services ADOMD.Net library is installed (look for a version number of 10.50.XXXX.X … I don’t know what the final RTM version will be) or you can look in the ULS log. The above (cryptic) example is what you will see if you have an older version of Analysis Services ADOMD.Net client library installed on your machine. If you have installed PowerPivot for SharePoint on the same machine as Central Admin, then we will have automatically installed the latest bits of ADOMD.Net and so this would not be the root issue.
I am not sure what other errors would be “expected” misconfigurations, but as I(we) find them I will try to add them. HTH
Updated: 20-Oct-2010 (by Dave Wickert)
Additional debugging techniques — how to determine which version of ADOMD.NET you are running:
From a command line, run this:
%SystemRoot%\System32\inetsrv\appcmd.exe list wp
The output will be something like this:
WP “3428″ (applicationPool:SharePoint Central Administration v4)
WP “4568″ (applicationPool:01bc80ba53c64beb8d5d115414aebb34)
WP “4500″ (applicationPool:SecurityTokenServiceApplicationPool)
With a debugger, attack to the Central Admin process (3428 in my example).
In the loaded modules, look for Microsoft.AnalysisServices.AdomdClient.dll.
Please include a copy-paste of that line with any bug report.
Remember, that the lastest SQL Server 2008 R2 ADOMD.NET must be installed on the machine where Central Administration is running.
Make sure you buy *big* disks for your system drive!
I just ran into an interesting thread from one of the SharePoint support forums. As I am sure that you are aware of (sic) SharePoint has a new high severity health rule for SharePoint 2010 that raises an error if your machines does not have 2X the amount of physical memory in free disk space. Yep, that’s right. I will leave it as an exercise for the student to investigate why, but this has a BIG impact on PowerPivot servers.
If you have a large memory machine, for example both DELL and HP now have low-end servers with 256GB of physical memory on them, this means that you must have 512GB of free disk space on them or the SP health rules will start kicking in. While this is not common for SharePoint servers to have this kind of memory (SharePoint typically runs on web server-class machines); it is a good idea for PowerPivot servers to have lots and lots of memory (PowerPivot is an in-memory service that needs database server-class machines). PowerPivot has a HUGE demand for memory.
Tip: (I spent several hours discovering this one)
When specifying your query for importing data into PowerPivot, make sure you columns are unique and only returned once in the query!
This is easy to do. In my case, I had a query that looked like this: SELECT FIELD1, * FROM TABLE – where field1 was a field in the table. TSQL is perfectly fine with this query. The result set has field1 included twice. Unfortunately the client add-in does not like this result set and returns an unexpected (at least to me) error. In my case it was something like:
Cannot load preview for table "summary1". Details:
Failed to retrieve data from summary1#csv. Reason: The expression contains invalid date constant ‘#csv.StartTime’.
All because StartTime was specified as a column twice in the result set. Learn from my mistakes
I having been playing around with a new SharePoint 2010 feature called the Developer Dashboard. It is a great tool to use when figuring out how your pages work behind the scenes.
See some previous postings on the topic here:
To disable the Developer Dashboard:
STSADM.exe –o setproperty –pn developer-dashboard –pv Off
Then enable the Developer Dashboard with OnDemand Switch :
STSADM.exe –o setproperty –pn developer-dashboard –pv [ OnDemand | On | Off ]
‘OnDemand’ turns the Dashboard on, but hides it until the click of an icon beside the ‘Open Menu’ on the top of the SharePoint page.
Very cool stuff!
Clearly if you read my blog, you will see that as a development team we could have done a better job of testing all of the various installation combinations with SQL Server on ‘test servers’. Unfortunately, our limited test time was focused on production configurations; not on single demo machines. So as a result you can see some of our installation issues that have arisen. Recently we ran into another installation problem when a previous SQL Server installation has been done on a machine prior to installing PowerPivot.
SQL Server stores it files in “instance” folders and a “Shared” folder of tools, SDK, and installation files. Normally the shared folder is specified in:
C:\Program Files\Microsoft SQL Sever\100\
but it does not have to . . . Many times DBAs specify a folder to a different location (off of the system drive) when you do your first SQL Server installation. However if you do this, you will find that PowerPivot will return errors, either:
Object reference not set to an instance of an object.
Or: (if you look at details.txt installation file)
2010-08-17 16:56:29 AS: The location is C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.AnalysisServices.SharePoint.Integration.DLL.
2010-08-17 16:56:29 AS: Microsoft.AnalysisServices.SharePoint.Integration.DLL not present in SDK
So how do you fix it? A quick workaround is to copy the file from the folder that you specified to its default location:
C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.AnalysisServices.SharePoint.Integration.DLL
The issue is that the SQL installer copies the file to shared folder but it will look for it in C: in its default location (this is a registry key/directory sync problem). So the workaround is to just copy it there. Smart huh?
So I am playing with importing data from an an SSAS cube. The most distracting issue is that numeric columns are imported as strings, and the default aggregation for strings is Count. Thus when I try to drag & drop measures into Values field, I found that it treated as Count rather than Sum. When I change Count to Sum, following error message is displayed:
“ERROR – CALCULATION ABORTED: MdxScript(Sandbox) (12, 72) The function SUM takes an argument that evaluates to numbers or dates and cannot work with values of type String”.
Bingo – clearly what is happening is that what I think is numeric (i.e. measures), is coming in a strings. What is happening is that during the import process everything comes in as dimension attributes (i.e. as strings). As I quick workaround, in PowerPivot window, I found that I had to do was to convert the column to a numeric data type and it will work.