Error when opening TIBCO Spotfire analysis file linked to an .xlsb file: "InvalidOperationException at Spotfire.Dxp.Framework: The current thread does not have an open read transaction. (HRESULT: 80131509)"

Date Posted: 2019-08-30
Product: TIBCO Spotfire®

Problem:

Solution:

When opening a TIBCO Spotfire analysis file (.dxp) with a linked data source to an .xlsb file, the report may fail to open and the following error is seen:

Error message: The file '/MyFolder/MyFile' cannot be opened. It may be damaged or in an unknown format.
 
TargetInvocationException at Spotfire.Dxp.Framework:
Exception has been thrown by the target of an invocation. (HRESULT: 80131604)
 
Stack Trace:
   at Spotfire.Dxp.Framework.ApplicationModel.ProgressService.ExecuteWithProgressCancelable(String title, String description, ProgressOperation operation, Boolean showCancelButton)
   at Spotfire.Dxp.Forms.WebView.FormsAddDataController.OpenNewDocumentWithProgress(String title, String description, String fileName, ProgressOperation operation)
 
InvalidOperationException at Spotfire.Dxp.Framework:
The current thread does not have an open read transaction. (HRESULT: 80131509)
 
Stack Trace:
   at Spotfire.Dxp.Framework.DocumentModel.ThreadTransactionInfo.ValidateTransactionIsOpen()
   at Spotfire.Dxp.Framework.Commands.CommandHistory.SetIdleState()
   at Spotfire.Dxp.Framework.Commands.CommandHistory.RollbackOutermostTransaction(Exception exception, Object busyProof)
   at Spotfire.Dxp.Framework.DocumentModel.TransactionHandle.Dispose(Boolean disposing)
   at Spotfire.Dxp.Framework.Commands.CommandHistory.OutermostTransactionHandle.Dispose(Boolean disposing)
   at Spotfire.Dxp.Application.AnalysisApplication.<OpenPartiallyOpenedDocument>d__90.System.IDisposable.Dispose()
   at Spotfire.Dxp.Application.AnalysisApplication.ConsumeDataLoadPromptRequests(IEnumerable`1 prompts)
   at Spotfire.Dxp.Application.AnalysisApplication.OpenFromLibraryCore(Guid entryId, DocumentOpenSettings settings)
   at Spotfire.Dxp.Application.AnalysisApplication.Open(LibraryItem libraryItem, DocumentOpenSettings settings)
   at Spotfire.Dxp.Forms.Application.FormsProgressService.ProgressThread.DoOperationLoop()

Opening the source .xlsb file in Microsoft Excel first before opening the TIBCO Spotfire report will avoid the error and allows for the opening of the analysis (.dxp) file.

If you try to add the .xlsb file in a new analysis (.dxp) file, you might see this error instead:

Error message: The process reading data unexpectedly stopped with exit code -1073740791.
 
AggregateException at :
One or more errors occurred. (HRESULT: 80131500)
 
Stack Trace:
 
Aggregated exception:
 
ImportException at Spotfire.Dxp.Data:
Failed to execute data source query for data source "MyData". (HRESULT: 80131500)
 
Stack Trace:
at Spotfire.Dxp.Data.DataSourceConnection.<GetPromptModels>d__37.MoveNext()
at Spotfire.Dxp.Data.Access.AddData.DataSourceAddDataFlow.ConfigureDataSourcesStep.<GetPromptRequests>d__7.MoveNext()
at Spotfire.Dxp.Data.Access.AddData.DataSourceAddDataFlow.ConfigureDataSourcesStep.<EnumerateModels>d__6.MoveNext()
 
ImportException at Spotfire.Dxp.Data:
The process reading data has terminated unexpectedly with exit code -1073740791 (HRESULT: 80131500)
 
Stack Trace:
at Spotfire.Dxp.Data.Import.OutOfProcessDataSourceConnection.TryExecute(Action f)
at Spotfire.Dxp.Data.Import.OutOfProcessDataSourceConnection.TryExecute[T](Func`1 f)
at Spotfire.Dxp.Data.Import.OutOfProcessDataSourceConnection.<GetPromptModelsCore>d__13.MoveNext()
at Spotfire.Dxp.Data.DataSourceConnection.<GetPromptModels>d__37.MoveNext()

This behavior is caused by the Microsoft driver on the local machine being 32 bit whereas Spotfire is looking for 64 bit driver. The driver Spotfire uses for reading binary Excel files is "Microsoft.ACE.OLEDB.12.0". To avoid this issue, please try one of the following:

Option 1) Install "Microsoft Access Database Engine 2016 Redistributable" (64 bit):

Results:

Loading a .dxp file with the linked .xlsb file works without issues.
Opening the .xlsb file into any new Spotfire analysis works without issues.

Note: This approach may interfere with the existing Office installations so ensure you validate with your IT team first.

Installation instructions:

Download the AccessDatabaseEngine_X64.exe from https://www.microsoft.com/en-us/download/details.aspx?id=54920
Open a PowerShell terminal with admin priviledges
Navigate to the folder where the AccessDatabaseEngine_X64.exe file is located
To install the OLEDB driver, run the command: .\AccessDatabaseEngine_X64.exe /quiet

Note: This is required in order to install this version of the 64-bit redistributable alongside a 32-bit installation of Office 2016

Option 2) Install "Microsoft Access Database Engine 2010 Redistributable" (64 bit):

Results:

Loading a .dxp file with the linked .xlsb file works without issues.
When opening the .xlsb file in a empty Spotfire analysis we got the "External table is not in the expected format." error message from the driver. However, this can be ignored this by closing the dialog and pressing OK in the summary view.

Installation instructions:

Download the AccessDatabaseEngine_X64.exe from https://www.microsoft.com/en-us/download/details.aspx?id=13255
Double click the AccessDatabaseEngine_X64.exe file to install the OLEDB driver.

Option 3) Importing data in-process
By default, Access, Excel, and SAS files are loaded out-of-process by Spotfire, which means that a separate sub-process is launched to handle loading of the file. You can check the following article for more information on the in-process/out-of-process concept:

KB: How to access 32-bit data files with 64-bit Spotfire on a 64-bit operating system.

If any issues are encountered using either the 2016 or 2010 version of the Microsoft Access Database Engine bundle, loading data in-process might solve the problem. To enable data in-process set the "DataImport_LoadOutOfProcess" setting to "False". You can follow this KB article to make this change: