Resources for IT Professionals >
Forums Home
>
Using Forums Forums
>
Off-Topic Posts (Do Not Post Here)
>
oracle connection from excel
oracle connection from excel
- Hi all. I am sure this has been asked before but I cannot find a way to solve it anywhere.
The issue is as follows:
I have an excel sheet with data coming from an oracle database. When I try to refresh the data I get the dreaded "Data Refresh Failed" error.
The same settings work for sql server data so it is not a general error. The report refreshes in sso and none authentication modes. However, when with oracle the error shows up.
I have also tried to use system DSN without success. I created an ODBC connection in the system DSN but this is not visible in Excel. When I create one in Excel it is a user dsn level connection rather than a system dsn connection.
Is there a way to make Excel see the system dsn... or is there another way to connect to Oracle?
Regards- Moved byMike Walsh MVPMVP, ModeratorWednesday, November 04, 2009 10:13 AMExcel Services is a function of a SharePoint product and has nothing to do with Excel. (From:SharePoint - Excel Services)
Answers
- OK... for those in the same problem... this is the work around I solved this problem with...
I have used the following tutorial to create a linked server in sql server express:
http://it.toolbox.com/blogs/daniel-at-work/linking-sql-server-2005-to-oracle-26791
I did not follow it to the letter - I used the management studio and selected "MS OleDb provider for oracle" and used the remote server address for the datasource as follows:
IP:Port/tnsname
eg. 192.168.55.12:1521/oratnsname
The rest was as in the tutorial
From Excel I connected to SQL server to a specific database and executed the sql in the tutorial. Worked fine in sharepoint.
Regards- Marked As Answer bywskanaan Thursday, November 05, 2009 5:29 AM
All Replies
- Hi Mike...
Actually... I think I should have explained that this error comes when using Excel reports in SharePoint. The connection works fine with Oracle when outside sharepoint... but once the sheet is published the error appears. - OK... for those in the same problem... this is the work around I solved this problem with...
I have used the following tutorial to create a linked server in sql server express:
http://it.toolbox.com/blogs/daniel-at-work/linking-sql-server-2005-to-oracle-26791
I did not follow it to the letter - I used the management studio and selected "MS OleDb provider for oracle" and used the remote server address for the datasource as follows:
IP:Port/tnsname
eg. 192.168.55.12:1521/oratnsname
The rest was as in the tutorial
From Excel I connected to SQL server to a specific database and executed the sql in the tutorial. Worked fine in sharepoint.
Regards- Marked As Answer bywskanaan Thursday, November 05, 2009 5:29 AM
