-
Title
When loading data from a linked file in one of the InTrust reports, error: "Query execution faile -
Description
When loading data from a linked file in one of the InTrust reports, error: "Query execution failed for dataset 'MainDataSet'."
An error has occurred during report processing.
Query execution failed for dataset 'MainDataSet'.
For more information about this error navigate to the report server on the local server machine, or enable remote errors -
Cause
JET is not available in Windows x64 systems anymore, as of 2007+, see: "http://en.wikipedia.org/wiki/Microsoft_Jet_Database_Engine". MS has replaced JET this with ACE, the same idea, but different syntax in the report RDL files will have to be updated to use a call to ACE rather than JET.
-
Resolution
We have logged Defect ID: ST80378 against our reports to possibly have this updated to use ACE rather than JET going forward; however, we do have a workaround:
1) Download and install ACE (64-bit version) from "http://www.microsoft.com/download/en/details.aspx?id=13255"
2) In the SQL Management Studio go to "Server Objects -> Linked Servers -> Providers -> Microsoft.ACE.OLEDB.12.0 -> Properties" and check the ‘Allow inprocess’ option.
3) Download the report RDL file (by going to Report Properties and then Edit -- you should be prompted to save the RDL file) and find and replace all Microsoft.Jet.OLEDB.4.0' with 'Microsoft.ACE.OLEDB.12.0' (use notepad to edit the file).
4) Upload the changed RDL file to QKP (Do not replace the original RDL file until make sure the new one is working correctly after the change).
-
Defect ID
ST80378 -
Additional Information
An additional step may be required if the above steps still result in an error, if so, run the following SQL Query from SQL Server Management Studio an then re-run the modified report(s).
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE
GO