There are multiple scenarios in BDC, where we can see this error message “An error occurred while retrieving data from <InstanceName>. Administrators, see the server log for more information.” Let me talk about one of the instance that can happen when we use BDC Application Definition File (ADF) that uses Oracle database in SharePoint web part.
This is the LobSystemInstance that we generally use in ADF when connecting to Oracle database.
<Properties>
<Property Name="DatabaseAccessProvider" Type="Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.Db.DbAccessProvider">Oracle</Property>
<Property Name="AuthenticationMode" Type="Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.Db.DbAuthenticationMode">RevertToSelf</Property>
<Property Name="rdbconnection Data Source" Type="System.String">datasource</Property>
<Property Name="rdbconnection Integrated Security" Type="System.Boolean">false</Property>
<Property Name="rdbconnection User ID" Type="System.String">uid</Property>
<Property Name="rdbconnection Password" Type="System.String">pwd</Property>
<Property Name="rdbconnection Enlist" Type="System.Boolean">true</Property>
<Property Name="rdbconnection Pooling" Type="System.Boolean">true</Property>
<Property Name="rdbconnection Min Pool Size" Type="System.Int32">0</Property>
<Property Name="rdbconnection Max Pool Size" Type="System.Int32">100</Property>
<Property Name="rdbconnection Unicode" Type="System.Boolean">false</Property>
<Property Name="rdbconnection Load Balance Timeout" Type="System.Int32">0</Property>
<Property Name="rdbconnection Omit Oracle Connection Name" Type="System.Boolean">false</Property>
</Properties>
This xml file works fine in BDC tool. But, when you use in SharePoint BDC web part, the data may not come. One of the reason could be when Oracle database server and MOSS 2007 server are on a different box. To overcome this issue, we must install Oracle Client Tools, and Oracle Data Provider .NET support on all SharePoint Servers.
