locked
oracle connection from excel RRS feed

  • Question

  • 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 by Mike Walsh FIN Wednesday, November 4, 2009 10:13 AM Excel Services is a function of a SharePoint product and has nothing to do with Excel. (From:SharePoint - Excel Services)
    Wednesday, November 4, 2009 9:53 AM

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 by wskanaan Thursday, November 5, 2009 5:29 AM
    Thursday, November 5, 2009 5:28 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.
    Wednesday, November 4, 2009 10:17 AM
  • 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 by wskanaan Thursday, November 5, 2009 5:29 AM
    Thursday, November 5, 2009 5:28 AM