locked
SharePoint Designer 2010 missing Oracle connection RRS feed

  • Question

  • Hi

    In SPD and within DataSources I'm missing Oracle provide.  We have 1 wfe, 1 App server in development

    I've tried adding the Oracle client (11204) software on my WFE however I still cant connect to the DB from within ODBC. Now I've removed it. To my knowledge my server does not contain tnsnames.ora

    Could someone explain if I need to check through ODBC or through another way to get SPD to work.

    Thanks
    Friday, July 1, 2016 12:04 PM

Answers

  • Hi orange juice jones,

    As I know, the database connection functionality in SharePoint Designer does not work with Oracle Databases.

    After research, there is a workaround here, and it involves multiple steps, you can have a try:
     
    Step 1: Install Oracle Client software on the SharePoint Server/Developer work station.
     
    Step 2: Create an ASP:SqlDataSource control and make sure it works against Oracle.
     
    Step 3: Integrate the ASP:SqlDataSource control into a placeholder Database Data Source in the fpdatasources folder.
     
    Step 1:
    The Oracle Client software must be installed on the SharePoint Servers and the desktop that SharePoint Designer is on. The .NET Framework Data Provider for Oracle is an add-on component to the .NET Framework that provides access to the Oracle database. This provider relies upon  Oracle Client Interfaces provided by the Oracle Client Software.

    Step 2:
    Before a new Database Data Source can be created, for use in a DataFormWebPart, an ASP:SqlDataSource control must be configured correctly. Once this is done it will be leveraged to create a valid Oracle Database Data Source for later use.

    Connect to a SharePoint site with SharePoint Designer 2007.
     
    Create a blank aspx page by pressing Control + N.
     
    Using the Toolbox Task Pane drag an SqlDataSource control onto the page.
     
    Use SqlDataSource control's smart tag, configure data source by clicking the Configure Data Source link.
     
    Click on "New Connection" to create a new connection to your Oracle database.
     
    Data Source - Choose "Oracle Database", then Data Provider would be .NET Framework Data Provider for Oracle, click OK.
     
    Connection Properties windows appears. Datasource is "Oracle Database(OracleClient)" as chosen in previous interface. 
     
    Fill the Server name.
     
    NOTE: The name here is the name of the database that is held in the tnsnames.ora file. This file is put into place after install the Oracle Client and a connection is created with the Oracle Client software on the server. The .NET Framework Data Provider for Oracle uses this to talk to Oracle. It is typically at C:\Oracle\product\10.1.0\Client_1\NETWORK\ADMIN. An example of a database entry in an ora file is has been included below:
     
    MyDatabase =
     
    (DESCRIPTION =
     
    (ADDRESS_LIST =
     
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521))
     
    )
     
    (CONNECT_DATA =
     
    (SID = MyDatabase)
     
    (SERVER = DEDICATED)
     
    )
     
    )
     
    After the reference to the database, fill database username and password and then simply use the SqlDataSource control as working with SQL Server. 

    Click on Test Connection button and it should display, "Test connection succeeded", if not, fix the problem until Test Connection succeed.
     
    Click "OK", prompt - "The selected authentication option saves the user name and password as clear text in the data connection. Other authors of this Web sit can access this information.", click "OK" and then "Next".
     
    Click "Next" at the "Save the Connection String to the Application Configuration File" without checking "Yes, save this connection as", click "Next".
     
    Now, "Configure the Select Statement" screen prompts, however, the automatically generated SQL statements won't work. User needs to choose "Specify a custom SQL statement or stored procedure".
     
    Use Oracle Enterprise Manager Console and SQL Plus tool to develop and test query first and then put as corresponding statement, for example, "select * from HR.TABLE101" is used for Select Statment.
     
    Click on "Test Query", it retrieved right data. Click OK, prompt "Storing connection information in the web application configuration file (web.config) is not supported on Windows SharePoint Services sites. The connection will be stored in the data source control instead.", click OK for this.
     
    The <asp:SqlDataSource> tag will be saved on the page.
     
    Next, from the Toolbox Task Pane drag a GridView control onto the page and bind it to the ASP:SqlDataSource Control for testing. If this works then move onto Step 3.
     
    Step 3:
    This step will put all the pieces together and produce a DataFormWebPart that displays Oracle database data.
     
    Create a dummy Database Connection to a SQL Server database in Data Source Library with SharePoint Designer following this article - http://office.microsoft.com/en-us/sharepoint-designer-help/add-a-database-as-a-data-source-HA010355745.aspx?CTT=1.
     
    After create a Database Connection to a SQL Server database successfully, user should be able to locate the datasouce XML file at _catalogs/fpdatasources folder.
     
    Open the datasource XML file, replace "ProviderName", "ConnectionString" and "SelectCommand" with the corresponding components from the asp:SqlDataSouce control generated in Step 2.
     
    Drop a Data View Web Part in the page, link it to the above modified Data Source connection.
     
    Locate the Database Connection generated in Step3 in Data  Source Library and click on it, choose "Show Data" in the dropdown menu.
     
    Select desired columns and click on "Insert Selected Fields as..." dropdown menu, choose from Single Item View, Multiple Item View, Single Item Form... Save the page after.
     
    Browse to the page, the data from Oracle Database is retrieved and displayed.
     
    Best Regards,

    CY


    TechNet Community Support
    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Monday, July 4, 2016 7:13 AM

All replies

  • Hi orange juice jones,

    As I know, the database connection functionality in SharePoint Designer does not work with Oracle Databases.

    After research, there is a workaround here, and it involves multiple steps, you can have a try:
     
    Step 1: Install Oracle Client software on the SharePoint Server/Developer work station.
     
    Step 2: Create an ASP:SqlDataSource control and make sure it works against Oracle.
     
    Step 3: Integrate the ASP:SqlDataSource control into a placeholder Database Data Source in the fpdatasources folder.
     
    Step 1:
    The Oracle Client software must be installed on the SharePoint Servers and the desktop that SharePoint Designer is on. The .NET Framework Data Provider for Oracle is an add-on component to the .NET Framework that provides access to the Oracle database. This provider relies upon  Oracle Client Interfaces provided by the Oracle Client Software.

    Step 2:
    Before a new Database Data Source can be created, for use in a DataFormWebPart, an ASP:SqlDataSource control must be configured correctly. Once this is done it will be leveraged to create a valid Oracle Database Data Source for later use.

    Connect to a SharePoint site with SharePoint Designer 2007.
     
    Create a blank aspx page by pressing Control + N.
     
    Using the Toolbox Task Pane drag an SqlDataSource control onto the page.
     
    Use SqlDataSource control's smart tag, configure data source by clicking the Configure Data Source link.
     
    Click on "New Connection" to create a new connection to your Oracle database.
     
    Data Source - Choose "Oracle Database", then Data Provider would be .NET Framework Data Provider for Oracle, click OK.
     
    Connection Properties windows appears. Datasource is "Oracle Database(OracleClient)" as chosen in previous interface. 
     
    Fill the Server name.
     
    NOTE: The name here is the name of the database that is held in the tnsnames.ora file. This file is put into place after install the Oracle Client and a connection is created with the Oracle Client software on the server. The .NET Framework Data Provider for Oracle uses this to talk to Oracle. It is typically at C:\Oracle\product\10.1.0\Client_1\NETWORK\ADMIN. An example of a database entry in an ora file is has been included below:
     
    MyDatabase =
     
    (DESCRIPTION =
     
    (ADDRESS_LIST =
     
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521))
     
    )
     
    (CONNECT_DATA =
     
    (SID = MyDatabase)
     
    (SERVER = DEDICATED)
     
    )
     
    )
     
    After the reference to the database, fill database username and password and then simply use the SqlDataSource control as working with SQL Server. 

    Click on Test Connection button and it should display, "Test connection succeeded", if not, fix the problem until Test Connection succeed.
     
    Click "OK", prompt - "The selected authentication option saves the user name and password as clear text in the data connection. Other authors of this Web sit can access this information.", click "OK" and then "Next".
     
    Click "Next" at the "Save the Connection String to the Application Configuration File" without checking "Yes, save this connection as", click "Next".
     
    Now, "Configure the Select Statement" screen prompts, however, the automatically generated SQL statements won't work. User needs to choose "Specify a custom SQL statement or stored procedure".
     
    Use Oracle Enterprise Manager Console and SQL Plus tool to develop and test query first and then put as corresponding statement, for example, "select * from HR.TABLE101" is used for Select Statment.
     
    Click on "Test Query", it retrieved right data. Click OK, prompt "Storing connection information in the web application configuration file (web.config) is not supported on Windows SharePoint Services sites. The connection will be stored in the data source control instead.", click OK for this.
     
    The <asp:SqlDataSource> tag will be saved on the page.
     
    Next, from the Toolbox Task Pane drag a GridView control onto the page and bind it to the ASP:SqlDataSource Control for testing. If this works then move onto Step 3.
     
    Step 3:
    This step will put all the pieces together and produce a DataFormWebPart that displays Oracle database data.
     
    Create a dummy Database Connection to a SQL Server database in Data Source Library with SharePoint Designer following this article - http://office.microsoft.com/en-us/sharepoint-designer-help/add-a-database-as-a-data-source-HA010355745.aspx?CTT=1.
     
    After create a Database Connection to a SQL Server database successfully, user should be able to locate the datasouce XML file at _catalogs/fpdatasources folder.
     
    Open the datasource XML file, replace "ProviderName", "ConnectionString" and "SelectCommand" with the corresponding components from the asp:SqlDataSouce control generated in Step 2.
     
    Drop a Data View Web Part in the page, link it to the above modified Data Source connection.
     
    Locate the Database Connection generated in Step3 in Data  Source Library and click on it, choose "Show Data" in the dropdown menu.
     
    Select desired columns and click on "Insert Selected Fields as..." dropdown menu, choose from Single Item View, Multiple Item View, Single Item Form... Save the page after.
     
    Browse to the page, the data from Oracle Database is retrieved and displayed.
     
    Best Regards,

    CY


    TechNet Community Support
    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Monday, July 4, 2016 7:13 AM
  • Hi

    Thanks for the concise steps. On the server (WFE) the client has been installed and we can TNSPING. We have also added a System DNS in ODBC.

    On the desktop that's running SPD2010 I haven't installed Oracle Client. Is this essential? 
    When I try to add a SqlDatSource I don't get a option to set properties other that the Tag Properties. So edited the Tag Properties as below:

    Add new connection

    selected Oracle DB and .NET Framework Data Provider for Oracle

    Added Server Name  - myTNS, User name and Password,

    Test Connection and receive the following error:

    ORA-12154: TNS:could not resolve the correct identifier specified

    Any ideas on what could be wrong. Does any new drivers need to be registered with SharePoint?

    Thanks

    Monday, July 4, 2016 3:35 PM
  • Hi

    I now can connect to Oracle via SPD albeit in a read only basis. Is editing possible?

    I didn't need to install Oracle Client on the machine running SPD but only on the server.

    Thanks

    Thursday, July 7, 2016 8:31 AM