Error while connectiong to Excel datasource.
Hi all, I would like to know how the OLE db connection string for Excel-97 works. I have Datasource ,created using the connection string => "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\myserver\Sharedfolder \ExcelTest_97.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";" The connection is created with credentials set to =>"Credentials supplied by the user running this report" The dataset uses this Datasource and fires a query => "select * from [Sheet1$]" Now, the Dataset gets executed when run in 'Query Designer'.But when i run the report itself it fails.I get an error => "For more information about this error navigate to the report server on the local server machine, or enable remote errors ---------------------------- Query execution failed for dataset 'DS_1'. (rsErrorExecutingCommand) ---------------------------- An error has occurred during report processing. (rsProcessingAborted) " help me out of this. Any help is appreciated. Dpka
May 15th, 2012 8:47am
I am not sure but seems a rights issue.Because when i run the same report on Server itself ,it works.But it fails when run from webclient thru my machine.
May 15th, 2012 8:49am
Hi dpkas, What is the number of your computer bits? Generally, the Microsoft OLE DB Provider for Jet driver are available in 32-bit versions only. If you use the Microsoft OLE DB Provider for Jet to connect to a data source in a 64-bit environment, you experience different problems:http://support.microsoft.com/kb/957570 One possible workaround here:http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/9e999fb4-5a39-41c4-8fd7-46193a223673/ For further assistance, please enable remote errors and provide the error message in details. Thanks, LolaPlease remember to mark the replies as answers if they help.
May 15th, 2012 11:08pm
Hi Lola, My machine is 32 bit. I donot have SSIS enabled on the reportserver and I cannot do it as i donot have rights. I donot understand why my report donot work.I suspect it to be rights issue.Does anyone know how rights are required for using Excel97 thru oledb? Is there some other way to use data from Excel and manipulate it using sql queries. thanks.
May 16th, 2012 7:28am