Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel as a SSRS 2005 data source

Posted on 2011-02-25
3
Medium Priority
?
1,234 Views
Last Modified: 2012-05-11
I have been asked to set up an automated report to be emailed to management at certain times during the week. I've decided to use SSRS 2005 to do this.

The data source has to be an Excel spreadsheet (i know, I know, not cool, but that's what they want). I set up an Excel system DNS ODBC link on the server that points to a development version of the spreadsheet using the full path name.

In Visual Studio I created a shared data source that doesn't use any credentials. I've written the report using a simple dataset and it works fine within VS2005. I can deploy both the data source and report to the local reporting server without errors.

When I go to run the report from report server I get the following errors:

An error has occurred during report processing.
Cannot create a connection to data source 'orderbook'.
ERROR [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. ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed ERROR [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.

No one has the spreadsheet open. I've even closed VS2005 to make sure. I tried giving the data source network admin credentials, but the report still doesn't run. I have even moved the spreadsheet to be stored locally on the server, but to no avail.

Any pointers would be greatly appreciated.
0
Comment
Question by:t250
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 17

Expert Comment

by:dbaSQL
ID: 34985341
I'm wondering if it is less about the file than it is the datasource itself, given your first error:
Cannot create a connection to data source 'orderbook'.

This user had a similar problem, and resolved it by storing the datasource credentials within the datasource on the report sever:
http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/4f3eafe3-748a-4129-8d94-ba4dc3eda2cb
0
 

Accepted Solution

by:
t250 earned 0 total points
ID: 34996051
Thanks dbaSQL,

I had been added credentials to the data source on the report server, including network admin rights, but it didn't run.

I solved this by using an admin log-in for the ODBC connector as well as storing credentials against the data source and ticked "Use as Windows credentials when connecting to the data source"
0
 

Author Closing Comment

by:t250
ID: 35042276
I can now run the report from the Excel data source
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question