locked
Trying to connect Oracle database to SharePoint 2010 Foundation RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello out there!

    My team and I are trying to connect an Oracle Database to SharePoint 2010 Foundation and display it.

    We have made the connection and can see the values populated in SP designer but when we try and the table to display on the SharePoint page it errors:

    Storing connection information in the web application configuration file (web.config) is not supported on Microsoft SharePoint Foundation sites. The connection will be stored in the data source control instead.

    Also I get this error when i tried to create a webpart of the connection in Designer

    "Unable to display this Web Part. To troubleshoot the problem,
    open this Web page in a Microsoft SharePoint Foundation-compatible HTML editor
    such as Microsoft SharePoint Designer. If the problem persists, contact your Web
    server administrator."

    Correlation
    ID:df5af711-2dfe-4461-9c19-b39e03471273

    Is this a problem with my version of SharePiont? am i using the wrong webpart?

    Any help or ideas on this would be greatly appreciated.

    thanks All!


    • Edited by ELB226 Thursday, October 11, 2012 8:51 PM
    Thursday, October 11, 2012 8:49 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi ELB,

    Unfortunately SharePoint Designer doesn't support connecting to Oracle database. So you may either manually create a BDC Model for Connecting to Oracle Databases

    http://msdn.microsoft.com/en-us/library/ff464424.aspx

    or use Visual Studio's BDC Model project and write the code for Oracle connection. Please look at this blog post http://www.lightningtools.com/blog/archive/2009/11/01/business-data-connectivity-model-ndash-finder-method.aspx

    please feel free to ask any questions

    reference :- 

    http://social.msdn.microsoft.com/Forums/is/sharepoint2010general/thread/53c9d776-6d68-4456-9e85-a8eeae618b77

    Best Regrads, Ahmed Madany MCTS @twitter http://twitter.com/ahmed_madany @Blog http://ahmedmadany.wordpress.com @LinkedIn http://eg.linkedin.com/pub/ahmed-madany/35/80/2b6

    Saturday, October 13, 2012 7:06 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Ahmed is right in that you'd need to use BCS.  Oracle doesn't have a SPD support, so you'll either need to create a new BDCM file or edit one that was created for a SQL table.  The following code (written by Scott Hillier) would be a good place to begin: -

    <?xml version="1.0" encoding="utf-16" standalone="yes"?>
<Model xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://schemas.microsoft.com/windows/2007/BusinessDataCatalog BDCMetadata.xsd" Name="Employee_Oracle" xmlns="http://schemas.microsoft.com/windows/2007/BusinessDataCatalog">
  <AccessControlList>
    <AccessControlEntry Principal="wingtip\administrator">
      <Right BdcRight="Edit" />
      <Right BdcRight="Execute" />
      <Right BdcRight="SetPermissions" />
      <Right BdcRight="SelectableInClients" />
    </AccessControlEntry>
    <AccessControlEntry Principal="wingtip\brianc">
      <Right BdcRight="Edit" />
      <Right BdcRight="Execute" />
      <Right BdcRight="SelectableInClients" />
    </AccessControlEntry>
    <AccessControlEntry Principal="wingtip\spworker">
      <Right BdcRight="Edit" />
      <Right BdcRight="Execute" />
    </AccessControlEntry>
  </AccessControlList>
  <LobSystems>
    <LobSystem Type="Database" Name="OracleHR" DefaultDisplayName="Oracle 2">
      <Properties>
        <Property Name="WildcardCharacter" Type="System.String">%</Property>
      </Properties>
      <AccessControlList>
        <AccessControlEntry Principal="wingtip\administrator">
          <Right BdcRight="Edit" />
          <Right BdcRight="Execute" />
          <Right BdcRight="SetPermissions" />
          <Right BdcRight="SelectableInClients" />
        </AccessControlEntry>
        <AccessControlEntry Principal="wingtip\brianc">
          <Right BdcRight="Edit" />
          <Right BdcRight="Execute" />
          <Right BdcRight="SelectableInClients" />
        </AccessControlEntry>
        <AccessControlEntry Principal="wingtip\spworker">
          <Right BdcRight="Edit" />
          <Right BdcRight="Execute" />
        </AccessControlEntry>
      </AccessControlList>
      <Proxy />
      <LobSystemInstances>
        <LobSystemInstance Name="Oracle HR Instance">
          <Properties>
            <Property Name="AuthenticationMode" Type="System.String">RdbCredentials
        </Property>
            <Property Name="DatabaseAccessProvider" Type="System.String">Oracle
        </Property>
            <Property Name="RdbConnection Data Source" Type="System.String">
          YOUR_ORACLE_NET_SERVICE_NAME_HERE</Property>
            <Property Name="SsoApplicationId" Type="System.String">
          SECURESTORE_ORACLE_APP_ID_HERE</Property>
            <Property Name="SsoProviderImplementation" Type="System.String">
          Microsoft.Office.BusinessData.Infrastructure.SecureStore.LocalSecureStoreProvider, 
          Microsoft.Office.BusinessData, Version=14.0.0.0, Culture=neutral, 
          PublicKeyToken=71e9bce111e9429c</Property>
          </Properties>
        </LobSystemInstance>
      </LobSystemInstances>
      <Entities>
        <Entity Namespace="HR.OracleModel" Version="1.0.0.0" EstimatedInstanceCount="10000" Name="Employee" DefaultDisplayName="Employee">
          <Properties>
            <Property Name="Title" Type="System.String">EName</Property>
          </Properties>
          <AccessControlList>
            <AccessControlEntry Principal="wingtip\administrator">
              <Right BdcRight="Edit" />
              <Right BdcRight="Execute" />
              <Right BdcRight="SetPermissions" />
              <Right BdcRight="SelectableInClients" />
            </AccessControlEntry>
            <AccessControlEntry Principal="wingtip\brianc">
              <Right BdcRight="Edit" />
              <Right BdcRight="Execute" />
              <Right BdcRight="SelectableInClients" />
            </AccessControlEntry>
            <AccessControlEntry Principal="wingtip\spworker">
              <Right BdcRight="Edit" />
              <Right BdcRight="Execute" />
            </AccessControlEntry>
          </AccessControlList>
          <Identifiers>
            <Identifier TypeName="System.String" Name="EmployeeName" />
          </Identifiers>
          <Methods>
            <Method Name="EmployeeFinder">
              <Properties>
                <Property Name="RdbCommandText" Type="System.String">
                SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO 
                FROM SCOTT.EMP 
                WHERE ENAME LIKE :Name 
                ORDER BY EMPNO</Property>
                <Property Name="RdbCommandType" Type="System.Data.CommandType, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">Text</Property>
              </Properties>
              <AccessControlList>
                <AccessControlEntry Principal="wingtip\administrator">
                  <Right BdcRight="Edit" />
                  <Right BdcRight="Execute" />
                  <Right BdcRight="SetPermissions" />
                  <Right BdcRight="SelectableInClients" />
                </AccessControlEntry>
                <AccessControlEntry Principal="wingtip\brianc">
                  <Right BdcRight="Edit" />
                  <Right BdcRight="Execute" />
                  <Right BdcRight="SelectableInClients" />
                </AccessControlEntry>
                <AccessControlEntry Principal="wingtip\spworker">
                  <Right BdcRight="Edit" />
                  <Right BdcRight="Execute" />
                </AccessControlEntry>
              </AccessControlList>
              <FilterDescriptors>
                <FilterDescriptor Type="Wildcard" Name="EmployeeName" />
              </FilterDescriptors>
              <Parameters>
                <Parameter Direction="In" Name=":Name">
                  <TypeDescriptor TypeName="System.String" IdentifierName="EmployeeName" AssociatedFilter="EmployeeName" Name="EmployeeName">
                    <DefaultValues>
                      <DefaultValue MethodInstanceName="EmployeeFinderInstance" Type="System.String">%</DefaultValue>
                      <DefaultValue MethodInstanceName="IdEnumeratorInstance" Type="System.String">%</DefaultValue>
                    </DefaultValues>
                  </TypeDescriptor>
                </Parameter>
                <Parameter Direction="Return" Name="Employees">
                  <TypeDescriptor TypeName="System.Data.IDataReader, System.Data,                    Version=1.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="Employees">
                    <TypeDescriptors>
                      <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data,                        Version=1.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="Employee">
                        <TypeDescriptors>
                          <TypeDescriptor TypeName="System.Decimal" Name="EMPNO" />
                          <TypeDescriptor TypeName="System.String" ReadOnly="true" IdentifierName="EmployeeName" Name="ENAME" />
                          <TypeDescriptor TypeName="System.String" Name="JOB" />
                          <TypeDescriptor TypeName="System.Decimal" Name="MGR" />
                          <TypeDescriptor TypeName="System.DateTime" Name="HIREDATE">
                            <Interpretation>
                              <NormalizeDateTime LobDateTimeMode="UTC" />
                            </Interpretation>
                          </TypeDescriptor>
                          <TypeDescriptor TypeName="System.Decimal" Name="SAL" />
                          <TypeDescriptor TypeName="System.Decimal" Name="COMM" />
                          <TypeDescriptor TypeName="System.Decimal" Name="DEPTNO" />
                        </TypeDescriptors>
                      </TypeDescriptor>
                    </TypeDescriptors>
                  </TypeDescriptor>
                </Parameter>
              </Parameters>
              <MethodInstances>
                <MethodInstance Type="Finder" ReturnParameterName="Employees" Default="true" Name="EmployeeFinderInstance">
                  <AccessControlList>
                    <AccessControlEntry Principal="wingtip\administrator">
                      <Right BdcRight="Edit" />
                      <Right BdcRight="Execute" />
                      <Right BdcRight="SetPermissions" />
                      <Right BdcRight="SelectableInClients" />
                    </AccessControlEntry>
                    <AccessControlEntry Principal="wingtip\brianc">
                      <Right BdcRight="Edit" />
                      <Right BdcRight="Execute" />
                      <Right BdcRight="SelectableInClients" />
                    </AccessControlEntry>
                    <AccessControlEntry Principal="wingtip\spworker">
                      <Right BdcRight="Edit" />
                      <Right BdcRight="Execute" />
                    </AccessControlEntry>
                  </AccessControlList>
                </MethodInstance>
                <MethodInstance Type="SpecificFinder" ReturnParameterName="Employees" ReturnTypeDescriptorPath="Employees[0]" Default="true" Name="EmployeeSpecificFinderInstance">
                  <AccessControlList>
                    <AccessControlEntry Principal="wingtip\administrator">
                      <Right BdcRight="Edit" />
                      <Right BdcRight="Execute" />
                      <Right BdcRight="SetPermissions" />
                      <Right BdcRight="SelectableInClients" />
                    </AccessControlEntry>
                    <AccessControlEntry Principal="wingtip\brianc">
                      <Right BdcRight="Edit" />
                      <Right BdcRight="Execute" />
                      <Right BdcRight="SelectableInClients" />
                    </AccessControlEntry>
                    <AccessControlEntry Principal="wingtip\spworker">
                      <Right BdcRight="Edit" />
                      <Right BdcRight="Execute" />
                    </AccessControlEntry>
                  </AccessControlList>
                </MethodInstance>
                <MethodInstance Type="IdEnumerator" ReturnParameterName="Employees" Default="true" Name="IdEnumeratorInstance">
                  <AccessControlList>
                    <AccessControlEntry Principal="wingtip\administrator">
                      <Right BdcRight="Edit" />
                      <Right BdcRight="Execute" />
                      <Right BdcRight="SetPermissions" />
                      <Right BdcRight="SelectableInClients" />
                    </AccessControlEntry>
                    <AccessControlEntry Principal="wingtip\brianc">
                      <Right BdcRight="Edit" />
                      <Right BdcRight="Execute" />
                      <Right BdcRight="SelectableInClients" />
                    </AccessControlEntry>
                    <AccessControlEntry Principal="wingtip\spworker">
                      <Right BdcRight="Edit" />
                      <Right BdcRight="Execute" />
                    </AccessControlEntry>
                  </AccessControlList>
                </MethodInstance>
              </MethodInstances>
            </Method>
            <Method Name="Update" DefaultDisplayName="EmployeeUpdater">
              <Properties>
                <Property Name="RdbCommandText" Type="System.String">
                UPDATE SCOTT.EMP 
                SET EMPNO=:EmpNo,JOB=:Job,MGR=:Mgr,HIREDATE=:HireDate,
                SAL=:Sal,COMM=:Comm,DEPTNO=:DeptNo 
                WHERE ENAME=:Name</Property>
                <Property Name="RdbCommandType" Type="System.Data.CommandType, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">Text</Property>
              </Properties>
              <AccessControlList>
                <AccessControlEntry Principal="wingtip\administrator">
                  <Right BdcRight="Edit" />
                  <Right BdcRight="Execute" />
                  <Right BdcRight="SetPermissions" />
                  <Right BdcRight="SelectableInClients" />
                </AccessControlEntry>
                <AccessControlEntry Principal="wingtip\brianc">
                  <Right BdcRight="Edit" />
                  <Right BdcRight="Execute" />
                  <Right BdcRight="SelectableInClients" />
                </AccessControlEntry>
                <AccessControlEntry Principal="wingtip\spworker">
                  <Right BdcRight="Edit" />
                  <Right BdcRight="Execute" />
                </AccessControlEntry>
              </AccessControlList>
              <Parameters>
                <Parameter Direction="In" Name=":EmpNo">
                  <TypeDescriptor TypeName="System.Decimal" UpdaterField="true" Name="EMPNO" />
                </Parameter>
                <Parameter Direction="In" Name=":Name">
                  <TypeDescriptor TypeName="System.String" IdentifierName="EmployeeName" Name="EmployeeName" />
                </Parameter>
                <Parameter Direction="In" Name=":Job">
                  <TypeDescriptor TypeName="System.String" UpdaterField="true" Name="JOB" />
                </Parameter>
                <Parameter Direction="In" Name=":Mgr">
                  <TypeDescriptor TypeName="System.Decimal" UpdaterField="true" Name="MGR" />
                </Parameter>
                <Parameter Direction="In" Name=":HireDate">
                  <TypeDescriptor TypeName="System.Nullable`1[[System.DateTime, mscorlib,                    Version=1.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]" UpdaterField="true" Name="HIREDATE">
                    <Interpretation>
                      <NormalizeDateTime LobDateTimeMode="UTC" />
                    </Interpretation>
                  </TypeDescriptor>
                </Parameter>
                <Parameter Direction="In" Name=":Sal">
                  <TypeDescriptor TypeName="System.Nullable`1[[System.Decimal, mscorlib,                    Version=1.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]" UpdaterField="true" Name="SAL" />
                </Parameter>
                <Parameter Direction="In" Name=":Comm">
                  <TypeDescriptor TypeName="System.Nullable`1[[System.Decimal, mscorlib,                    Version=1.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]" UpdaterField="true" Name="COMM">
                    <Properties>
                      <Property Name="Decimal Digits" Type="System.Int32">9</Property>
                    </Properties>
                  </TypeDescriptor>
                </Parameter>
                <Parameter Direction="In" Name=":Deptno">
                  <TypeDescriptor TypeName="System.Nullable`1[[System.Decimal, mscorlib,                    Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]" UpdaterField="true" Name="DEPTNO">
                    <Properties>
                      <Property Name="Decimal Digits" Type="System.Int32">9</Property>
                    </Properties>
                  </TypeDescriptor>
                </Parameter>
              </Parameters>
              <MethodInstances>
                <MethodInstance Type="Updater" Name="Update" DefaultDisplayName="SQLAllTypes Update">
                  <AccessControlList>
                    <AccessControlEntry Principal="wingtip\administrator">
                      <Right BdcRight="Edit" />
                      <Right BdcRight="Execute" />
                      <Right BdcRight="SetPermissions" />
                      <Right BdcRight="SelectableInClients" />
                    </AccessControlEntry>
                    <AccessControlEntry Principal="wingtip\brianc">
                      <Right BdcRight="Edit" />
                      <Right BdcRight="Execute" />
                      <Right BdcRight="SelectableInClients" />
                    </AccessControlEntry>
                    <AccessControlEntry Principal="wingtip\spworker">
                      <Right BdcRight="Edit" />
                      <Right BdcRight="Execute" />
                    </AccessControlEntry>
                  </AccessControlList>
                </MethodInstance>
              </MethodInstances>
            </Method>
            <Method Name="Delete" DefaultDisplayName="EmployeeDelete">
              <Properties>
                <Property Name="RdbCommandText" Type="System.String">
                DELETE FROM SCOTT.EMP WHERE ENAME = :Name</Property>
                <Property Name="RdbCommandType" Type="System.Data.CommandType, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">Text</Property>
              </Properties>
              <AccessControlList>
                <AccessControlEntry Principal="wingtip\administrator">
                  <Right BdcRight="Edit" />
                  <Right BdcRight="Execute" />
                  <Right BdcRight="SetPermissions" />
                  <Right BdcRight="SelectableInClients" />
                </AccessControlEntry>
                <AccessControlEntry Principal="wingtip\brianc">
                  <Right BdcRight="Edit" />
                  <Right BdcRight="Execute" />
                  <Right BdcRight="SelectableInClients" />
                </AccessControlEntry>
                <AccessControlEntry Principal="wingtip\spworker">
                  <Right BdcRight="Edit" />
                  <Right BdcRight="Execute" />
                </AccessControlEntry>
              </AccessControlList>
              <Parameters>
                <Parameter Direction="In" Name=":Name">
                  <TypeDescriptor TypeName="System.String" IdentifierName="EmployeeName" Name="EmployeeName" />
                </Parameter>
              </Parameters>
              <MethodInstances>
                <MethodInstance Type="Deleter" Name="Delete" DefaultDisplayName="Employee                  Delete">
                  <AccessControlList>
                    <AccessControlEntry Principal="wingtip\administrator">
                      <Right BdcRight="Edit" />
                      <Right BdcRight="Execute" />
                      <Right BdcRight="SetPermissions" />
                      <Right BdcRight="SelectableInClients" />
                    </AccessControlEntry>
                    <AccessControlEntry Principal="wingtip\brianc">
                      <Right BdcRight="Edit" />
                      <Right BdcRight="Execute" />
                      <Right BdcRight="SelectableInClients" />
                    </AccessControlEntry>
                    <AccessControlEntry Principal="wingtip\spworker">
                      <Right BdcRight="Edit" />
                      <Right BdcRight="Execute" />
                    </AccessControlEntry>
                  </AccessControlList>
                </MethodInstance>
              </MethodInstances>
            </Method>
          </Methods>
        </Entity>
      </Entities>
    </LobSystem>
  </LobSystems>
</Model>
    Lightning Tools has a great tool called BDC Metaman which would be a great use here too.

    Steven Andrews | SharePoint Professional | http://www.twitter.com/backpackerd00d | https://baron72.wordpress.com/


    Monday, October 15, 2012 4:26 PM
    Answerer
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thanks for the Reply, Ahmed.

    I have the BDC Model created and have imported it to Central Admin.

    I think there is a problem with the webpart i am trying to use. What are the steps after I import the model to CA?

    thanks again!

    Monday, October 15, 2012 7:09 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    So it has been almost a year since I was trying to do this.

    I found this article: http://office.microsoft.com/en-us/sharepoint-designer-help/add-a-database-as-a-data-source-HA010355745.aspx#_Toc268508962

    Which seems to contradict the idea that I cannot connect to the Oracle Database through Designer.
    Any ideas?

    thanks all,

    Wednesday, September 25, 2013 8:39 PM