none
BDC An error occurred while retrieving data

    Question

  • Hi Everyone,

    I am getting the following error when I try to retreive data using BDC.

    An error occurred while retrieving data from rsupdeskInstance. Administrators, see the server log for more information.

    I traced this to the sharepoint logs to find the specific error as :

    57:41.0 w3wp.exe (0x0E54)                        0x1570 SharePoint Portal Server       Business Data                  79fm High     Exception handed to HandleRuntimeException.HandleException Microsoft.Office.Server.ApplicationRegistry.Runtime.RuntimeException: "The field named 'ID' and described in the TypeDescriptor for the Return parameter was not returned by the backend system". ---> System.IndexOutOfRangeException: ID     at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName)     at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name)     at System.Data.SqlClient.SqlDataReader.get_Item(String name)     at Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.Db.DbEntityInstanceEnumerator.get_Current()     --- End of inner exception stack trace ---     at Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.Db.DbEntityInstanceEnumerator.get_Current()     at Microsoft.Office.Server... 

    12/04/2008 14:57:41.03* w3wp.exe (0x0E54)                        0x1570 SharePoint Portal Server       Business Data                  79fm High     ....ApplicationRegistry.SystemSpecific.Db.DbEntityInstanceEnumerator.System.Collections.Generic.IEnumerator<Microsoft.Office.Server.ApplicationRegistry.Runtime.IEntityInstance>.get_Current()     at Microsoft.Office.Server.ApplicationRegistry.Runtime.EntityDataTableAdapter.Populate(UInt32 offsetFromStart, Int32 rowCount)     at Microsoft.Office.Server.ApplicationRegistry.Runtime.EntityDataTableAdapter.Initialize(IEntityInstanceEnumerator dataSource, UInt32 offsetFromStart, Int32 rowCount)     at Microsoft.Office.Server.ApplicationRegistry.Runtime.EntityDataTableAdapter..ctor(IEntityInstanceEnumerator dataSource, Boolean createSerializedIdColumn)     at Microsoft.SharePoint.Portal.WebControls.BdcDataSourceView.GetDataView(IEntityInstanceEnumerator entityInstanceDataReader)     at Microsoft.Shar... 

    12/04/2008 14:57:41.03* w3wp.exe (0x0E54)                        0x1570 SharePoint Portal Server       Business Data                  79fm High     ...ePoint.Portal.WebControls.BdcDataSourceView.GetList(Entity entity)     at Microsoft.SharePoint.Portal.WebControls.BdcDataSourceView.GetEntityInstances(Entity entity)     at Microsoft.SharePoint.Portal.WebControls.BdcDataSourceView.ExecuteSelect(DataSourceSelectArguments args)     at System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback)     at Microsoft.SharePoint.WebControls.SingleDataSource.GetXPathNavigatorInternal()     at Microsoft.SharePoint.WebControls.SingleDataSource.GetXPathNavigator()     at Microsoft.SharePoint.WebControls.SingleDataSource.GetXPathNavigator(IDataSource datasource, Boolean originalData)     at Microsoft.SharePoint.WebPartPages.DataFormWebPart.GetXPathNavigator(String viewPath)     at Microsoft.SharePoint.W... 

    12/04/2008 14:57:41.03* w3wp.exe (0x0E54)                        0x1570 SharePoint Portal Server       Business Data                  79fm High     ...ebPartPages.DataFormWebPart.PrepareAndPerformTransform() 
    57:41.0 w3wp.exe (0x0E54)                        0x1570 SharePoint Portal Server       Business Data                  8u7q Exception  Microsoft.Office.Server.ApplicationRegistry.Runtime.RuntimeException: "The field named 'ID' and described in the TypeDescriptor for the Return parameter was not returned by the backend system". ---> System.IndexOutOfRangeException: ID     at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName)     at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name)     at System.Data.SqlClient.SqlDataReader.get_Item(String name)     at Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.Db.DbEntityInstanceEnumerator.get_Current()     --- End of inner exception stack trace ---     at Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.Db.DbEntityInstanceEnumerator.get_Current()     at Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.Db.DbEntityInstanceEnu... 

    12/04/2008 14:57:41.03* w3wp.exe (0x0E54)                        0x1570 SharePoint Portal Server       Business Data                  8u7q Exception ...merator.System.Collections.Generic.IEnumerator<Microsoft.Office.Server.ApplicationRegistry.Runtime.IEntityInstance>.get_Current()     at Microsoft.Office.Server.ApplicationRegistry.Runtime.EntityDataTableAdapter.Populate(UI
     


    My XML file is as follows:

    <?xml version="1.0"?>
    <LobSystem xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:schemaLocation="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog BDCMetadata.XSD" Type="Database" Version="1.0.0.0" Name="rsupdeskLOBSystem" xmlns="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog">
      <Properties>
        <Property Name="WildcardCharacter" Type="System.String">%</Property>
      </Properties>
      <LobSystemInstances>
        <LobSystemInstance Name="rsupdeskInstance">
          <Properties>
            <Property Name="DatabaseAccessProvider" Type="System.String">SqlServer</Property>
            <Property Name="AuthenticationMode" Type="System.String">RevertToSelf</Property>
            <Property Name="RdbConnection Data Source" Type="System.String">ServerName</Property>
            <Property Name="RdbConnection Initial Catalog" Type="System.String">rsupdesk</Property>
            <Property Name="RdbConnection Integrated Security" Type="System.String">SSPI</Property>
            <Property Name="RdbConnection Pooling" Type="System.String">false</Property>
          </Properties>
        </LobSystemInstance>
      </LobSystemInstances>
      <Entities>
        <Entity EstimatedInstanceCount="0" Name="dbo.RequestLog">
          <Identifiers>
            <Identifier TypeName="System.Int32" Name="[ID]" />
          </Identifiers>
          <Methods>
            <Method Name="Getdbo.[RequestLog]">
              <Properties>
                <Property Name="RdbCommandText" Type="System.String">SELECT RequestLog.Status,Count(RequestLog.Status) FROM RequestLog WHERE (((RequestLog.DateTime) BETWEEN DATEADD(Day, -1, GETDATE()) AND GETDATE())) GROUP BY RequestLog.Status</Property>
                <Property Name="RdbCommandType" Type="System.Data.CommandType">Text</Property>
              </Properties>
              <Parameters>
                <Parameter Direction="Return" Name="dbo.[RequestLog]">
                  <TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="dbo.[RequestLog]DataReader">
                    <TypeDescriptors>
                      <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="dbo.[RequestLog]DataRecord">
                        <TypeDescriptors>
                          <TypeDescriptor TypeName="System.Int32" Name="ID" IdentifierName="[ID]" />
                          <TypeDescriptor TypeName="System.Int32" Name="RequestID" />
                          <TypeDescriptor TypeName="System.String" Name="DateTime" />
                          <TypeDescriptor TypeName="System.String" Name="UserID" />
                          <TypeDescriptor TypeName="System.String" Name="Cause" />
                          <TypeDescriptor TypeName="System.String" Name="Status" />
                          <TypeDescriptor TypeName="System.String" Name="Priority" />
                          <TypeDescriptor TypeName="System.Boolean" Name="ActiveStatus" />
                        </TypeDescriptors>
                      </TypeDescriptor>
                    </TypeDescriptors>
                  </TypeDescriptor>
                </Parameter>
              </Parameters>
              <MethodInstances>
                <MethodInstance Name="dbo.[RequestLog]Finder" Type="Finder" ReturnParameterName="dbo.[RequestLog]" ReturnTypeDescriptorName="dbo.[RequestLog]DataReader" ReturnTypeDescriptorLevel="0" />
              </MethodInstances>
            </Method>
            <Method Name="dbo.[RequestLog]SpecificFinder">
              <Properties>
                <Property Name="RdbCommandText" Type="System.String">Select [ID],[RequestID],[DateTime],[UserID],[Cause],[Status],[Priority],[ActiveStatus] From dbo.[RequestLog] Where ([ID]=@ID)</Property>
                <Property Name="RdbCommandType" Type="System.Data.CommandType">Text</Property>
              </Properties>
              <Parameters>
                <Parameter Direction="In" Name="@ID">
                  <TypeDescriptor TypeName="System.Int32" Name="[ID]" IdentifierName="[ID]" />
                </Parameter>
                <Parameter Direction="Return" Name="dbo.[RequestLog]">
                  <TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="dbo.[RequestLog]DataReader">
                    <TypeDescriptors>
                      <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="dbo.[RequestLog]DataRecord">
                        <TypeDescriptors>
                          <TypeDescriptor TypeName="System.Int32" Name="ID" IdentifierName="[ID]" />
                          <TypeDescriptor TypeName="System.Int32" Name="RequestID" />
                          <TypeDescriptor TypeName="System.String" Name="DateTime" />
                          <TypeDescriptor TypeName="System.String" Name="UserID" />
                          <TypeDescriptor TypeName="System.String" Name="Cause" />
                          <TypeDescriptor TypeName="System.String" Name="Status" />
                          <TypeDescriptor TypeName="System.String" Name="Priority" />
                          <TypeDescriptor TypeName="System.Boolean" Name="ActiveStatus" />
                        </TypeDescriptors>
                      </TypeDescriptor>
                    </TypeDescriptors>
                  </TypeDescriptor>
                </Parameter>
              </Parameters>
              <MethodInstances>
                <MethodInstance Name="dbo.[RequestLog]SpecificFinder" Type="SpecificFinder" ReturnParameterName="dbo.[RequestLog]" ReturnTypeDescriptorName="dbo.[RequestLog]DataReader" ReturnTypeDescriptorLevel="0" />
              </MethodInstances>
            </Method>
            <Method Name="dbo.[RequestLog]IDEnumerator">
              <Properties>
                <Property Name="RdbCommandType" Type="System.String">Text</Property>
                <Property Name="RdbCommandText" Type="System.String">Select [ID] from dbo.[RequestLog]</Property>
              </Properties>
              <Parameters>
                <Parameter Direction="Return" Name="dbo.[RequestLog]IDs">
                  <TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="dbo.[RequestLog]IDs">
                    <TypeDescriptors>
                      <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="dbo.[RequestLog]">
                        <TypeDescriptors>
                          <TypeDescriptor TypeName="System.Int32" Name="ID" IdentifierName="[ID]" />
                        </TypeDescriptors>
                      </TypeDescriptor>
                    </TypeDescriptors>
                  </TypeDescriptor>
                </Parameter>
              </Parameters>
              <MethodInstances>
                <MethodInstance Name="dbo.[RequestLog]EnumeratorInstance" Type="IdEnumerator" ReturnParameterName="dbo.[RequestLog]IDs" />
              </MethodInstances>
            </Method>
          </Methods>
        </Entity>
      </Entities>
    </LobSystem>

    Any Help would be appreciated.


    SQL 2005 with Sharepoint
    Thursday, December 04, 2008 4:36 PM

All replies

  • If you look at your application definition file, you'll see that the SQL getting executed is this:

    SELECT RequestLog.Status,Count(RequestLog.Status) FROM RequestLog WHERE (((RequestLog.DateTime) BETWEEN DATEADD(Day, -1, GETDATE()) AND GETDATE())) GROUP BY RequestLog.Status

    But this is going to return different columns to what you have described in the application definition (i.e. ID, Status, Priority, etc).

    should this line not read something like:

    SELECT ID, [DateTime], UserID, Cause, Status, Priority, ActiveStatus FROM RequestLog ...

    .NET Developer, Brisbane, Australia, http://httpcode.com
    Friday, December 05, 2008 11:11 PM
  • I am getting the same error <An error occurred while retrieving data from SearchPlusLogsInstance4. Administrators, see the server log for more information> .


    my xml looks like this. I m not able to figure out what should I change here..


    <?xml version="1.0"?>
    <LobSystem xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:schemaLocation="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog BDCMetadata.XSD" Type="Database" Version="1.0.0.0" Name="SearchLOBSystem5" xmlns="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog">
      <Properties>
        <Property Name="WildcardCharacter" Type="System.String">%</Property>
      </Properties>
      <LobSystemInstances>
        <LobSystemInstance Name="SearchInstance5">
          <Properties>
            <Property Name="DatabaseAccessProvider" Type="System.String">SqlServer</Property>
            <Property Name="AuthenticationMode" Type="System.String">RevertToSelf</Property>
            <Property Name="RdbConnection Data Source" Type="System.String"><server name></Property>
            <Property Name="RdbConnection Initial Catalog" Type="System.String"><DB name></Property>
            <Property Name="RdbConnection Integrated Security" Type="System.String">SSPI</Property>
            <Property Name="RdbConnection Pooling" Type="System.String">False</Property>
            <Property Name="RdbConnection User ID" Type="System.String"><username></Property>
            <Property Name="RdbConnection Password" Type="System.String"><password></Property>
          </Properties>
        </LobSystemInstance>
      </LobSystemInstances>
      <Entities>
        <Entity EstimatedInstanceCount="0" Name="dbo.ActivityLog">
          <Identifiers>
            <Identifier TypeName="System.Int32" Name="[intSerialNo]" />
          </Identifiers>
          <Methods>
            <Method Name="Getdbo.[ActivityLog]">
              <Properties>
                <Property Name="RdbCommandText" Type="System.String"> Select [txtSearchKeyword] From dbo.[ActivityLog] Order by [intSerialNo] esc;</Property>
                <Property Name="RdbCommandType" Type="System.Data.CommandType">Text</Property>
              </Properties>
              <Parameters>
                <Parameter Direction="Return" Name="dbo.[ActivityLog]">
                  <TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="dbo.[ActivityLog]DataReader">
                    <TypeDescriptors>
                      <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="dbo.[ActivityLog]DataRecord">
                        <TypeDescriptors>
                    
                          <TypeDescriptor TypeName="System.String" Name="txtSearchKeyword" />
                     
                        </TypeDescriptors>
                      </TypeDescriptor>
                    </TypeDescriptors>
                  </TypeDescriptor>
                </Parameter>
              </Parameters>
              <MethodInstances>
                <MethodInstance Name="dbo.[ActivityLog]Finder" Type="Finder" ReturnParameterName="dbo.[ActivityLog]" ReturnTypeDescriptorName="dbo.[ActivityLog]DataReader" ReturnTypeDescriptorLevel="0" />
              </MethodInstances>
            </Method>
            <Method Name="dbo.[ActivityLog]SpecificFinder">
              <Properties>
                <Property Name="RdbCommandText" Type="System.String">Select [intSerialNo],[txtActivity],[intMemberId],[intObjectId],[txtClientIP],[txtSearchKeyword],[dtDateTime],[intNoOfResults],[fltTimeTaken],[txtClientBrowser],[intNoOfClusters],[intNoOfManagedSenses],[intNoOfDiscoveredSenses],[intSearchId] From dbo.[ActivityLog] Where (intSerialNo=@intSerialNo)</Property>
                <Property Name="RdbCommandType" Type="System.Data.CommandType">Text</Property>
              </Properties>
              <Parameters>
                <Parameter Direction="In" Name="@intSerialNo">
                  <TypeDescriptor TypeName="System.Int32" Name="[intSerialNo]" IdentifierName="[intSerialNo]" />
                </Parameter>
                <Parameter Direction="Return" Name="dbo.[ActivityLog]">
                  <TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="dbo.[ActivityLog]DataReader">
                    <TypeDescriptors>
                      <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="dbo.[ActivityLog]DataRecord">
                        <TypeDescriptors>
                          <TypeDescriptor TypeName="System.Int32" Name="intSerialNo" IdentifierName="[intSerialNo]" />
                          <TypeDescriptor TypeName="System.String" Name="txtActivity" />
                          <TypeDescriptor TypeName="System.Int32" Name="intMemberId" />
                          <TypeDescriptor TypeName="System.Int32" Name="intObjectId" />
                          <TypeDescriptor TypeName="System.String" Name="txtClientIP" />
                          <TypeDescriptor TypeName="System.String" Name="txtSearchKeyword" />
                          <TypeDescriptor TypeName="System.String" Name="dtDateTime" />
                          <TypeDescriptor TypeName="System.Int32" Name="intNoOfResults" />
                          <TypeDescriptor TypeName="System.Double" Name="fltTimeTaken" />
                          <TypeDescriptor TypeName="System.String" Name="txtClientBrowser" />
                          <TypeDescriptor TypeName="System.Int32" Name="intNoOfClusters" />
                          <TypeDescriptor TypeName="System.Int32" Name="intNoOfManagedSenses" />
                          <TypeDescriptor TypeName="System.Int32" Name="intNoOfDiscoveredSenses" />
                          <TypeDescriptor TypeName="System.Int32" Name="intSearchId" />
                        </TypeDescriptors>
                      </TypeDescriptor>
                    </TypeDescriptors>
                  </TypeDescriptor>
                </Parameter>
              </Parameters>
              <MethodInstances>
                <MethodInstance Name="dbo.[ActivityLog]SpecificFinder" Type="SpecificFinder" ReturnParameterName="dbo.[ActivityLog]" ReturnTypeDescriptorName="dbo.[ActivityLog]DataReader" ReturnTypeDescriptorLevel="0" />
              </MethodInstances>
            </Method>
            <Method Name="dbo.[ActivityLog]IDEnumerator">
              <Properties>
                <Property Name="RdbCommandText" Type="System.String">Select [intSerialNo] From dbo.[ActivityLog]</Property>
                <Property Name="RdbCommandType" Type="System.Data.CommandType">Text</Property>
              </Properties>
              <Parameters>
                <Parameter Direction="Return" Name="dbo.[ActivityLog]IDs">
                  <TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="dbo.[ActivityLog]IDs">
                    <TypeDescriptors>
                      <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="dbo.[ActivityLog]">
                        <TypeDescriptors>
                          <TypeDescriptor TypeName="System.Int32" Name="intSerialNo" IdentifierName="[intSerialNo]" />
                        </TypeDescriptors>
                      </TypeDescriptor>
                    </TypeDescriptors>
                  </TypeDescriptor>
                </Parameter>
              </Parameters>
              <MethodInstances>
                <MethodInstance Name="dbo.[ActivityLog]EnumeratorInstance" Type="IdEnumerator" ReturnParameterName="dbo.[ActivityLog]IDs" />
              </MethodInstances>
            </Method>
          </Methods>
        </Entity>
       
      </Entities>
    </LobSystem>
    Monday, September 07, 2009 4:56 AM
  • Your finder method has invalid SQL statement

    Select [txtSearchKeyword] From dbo.[ActivityLog] Order by [intSerialNo] esc;

    it should be

    Select [txtSearchKeyword] From dbo.[ActivityLog] Order by [intSerialNo] desc;

    Monday, September 14, 2009 8:00 PM