May 14, 2012 at 8:26 am
Hi All,
I have created a System DSN which connects, and tests successfully to an excel workbook.
I then set up a new SSRS report in BIDS, and a new Shared Data Source (and corresponding Data Source), which connects successfully to the Excel workbook.
I then setup a new dataset, which simply says "SELECT * FROM [StaffStatistics$]", and this, again, when run in the Query Designer of the Dataset Properties, returns all of the required data.
However, when I then press "Preview" to return this data to a Tablix, it gives the error:
"An error occurred during local report processing.
An error during report processing.
Cannot create a connection to data source 'DATASOURCE NAME'.
ERROR [HY000] [Microsoft] [ODBC Excel Driver] The Microsoft Access database engine cannot open or write to the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view and write its data. ERRRO [IM006] [Microsoft] [ODBC Driver Manager] Driver's SQLSetConnectAttr failedERROR [HY000] [Microsoft] [ODBC Excel Driver] The Microsoft Office Access database engine cannot open or write to the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view and write its data.
So... In before: "Is the Excel workbook definitely not open, anywhere on the network"! No, I can confirm it is not. The Excel sheet has been saved as 97-2003 and Office 12, and both deliver data top the point of Query Designer in SSRS, but will not run when Previewed...
I am running on an x64 machine, so there is the BIDs x86 -> OS x64 to consider, maybe this only presents itself when previewing the report as the 'engine'(?) is different to that when just viewing the data, as opposed to running the report?
Any help or new google search paths would be great, I am on, say, page 20 of google searchs containing "Excel ODBC SSRS" etc!
Thanks again
May 14, 2012 at 10:19 am
Not sure if this will work or not but have you tried sharing the excel doc? (Review tab > Share Workbook)
May 14, 2012 at 10:29 am
The workbook is shared, yes. Thanks, though, that is in the right area!
I have put it on the local c:\ drive of the BIDS machine I'm developing on, and it works, and Preview shows the data in tablix. The drive that the excel workbook was on was a network drive, internally in work, but, obviously, with the network drive has priveleges that haven't been granted to my local SSRS preview engine account (??).
To clarify, it works in BIDS preview when the workbook is on a local drive of the machine with BIDS on, but won't when the ODBC points to a network drive that has additional security (this is why it connects fine in ODBC admin). When I wrote about "the engine" that drives Preview in BIDS, it looks like I was right that this is different to that in Query Designer, as it is running not under My Profile, but under a local SSRS account i.e. that hasn't been granted access to the network file share?
It should be fine as long as the service account that I deploy to has rights to access the file-share?
I'll report back!
May 14, 2012 at 10:36 am
Ahh ok, I've never connected to excel with SSRS but in a previous job we did have problems with the SSRS user not having access to network drives when it came to publishing reports to a file location, we set up a windows user id dedicated to SSRS which had network rights and it worked. Probably not the most secure method but if it was only a system in development at the time.
In saying that though, you say it returns data in the query designer, but not the preview, don't they both use the same connection string?
Post back with an update if it works or not 🙂
May 30, 2012 at 6:39 am
Hey. All of this, combined, worked to solve the problem.
1) Share the workbook.
2) Ensure that the workbook is in a place for which the security is right to allow access from Visual Studio and also the SSRS Service account that you deploy to.
I've just started another excel project and got caught out by not sharing the workbook!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply