SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSRS -> Excel connection, runs in Query Designer, not in Preview


SSRS -> Excel connection, runs in Query Designer, not in Preview

Author
Message
simon.johnson
simon.johnson
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 183
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
RossRoss
RossRoss
Old Hand
Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)

Group: General Forum Members
Points: 337 Visits: 763
Not sure if this will work or not but have you tried sharing the excel doc? (Review tab > Share Workbook)
simon.johnson
simon.johnson
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 183
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!
RossRoss
RossRoss
Old Hand
Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)

Group: General Forum Members
Points: 337 Visits: 763
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 Smile
simon.johnson
simon.johnson
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 183
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search