Maximum of 42,000 Account records returned by query

Question

We are writing an integration service between one of our systems and SFDC. The problem we are running into with the syncing of the data is that we're only able to sync 42,000 Account records from Salesforce, and have no idea why we're not getting all 42,400 records back.

Our query is pulling all Account objects and not limiting them at all.

Any ideas on how to get the remaining records?

Edit:

We're using .NET SOAP calls and using the load more technique to grab all records. This method seems to be topping out at 42,000 on the account object.

Using the SFDC data loader, I'm able to grab all 42,400 records with no problem, and the user has permissions to all records.

Any further help would be greatly appreciated.

Edit 2:

Here is the code - I stripped a bit out to try to at least keep it a decent size.

     private static void PollDataForObject( SF_DataPolling_Object obj )
    {
        try
        {

            using( DataClassesDataContext dc = new DataClassesDataContext( Utility.GetConnectionString() ) )
            {

                dc.ObjectTrackingEnabled = false;

                string fields = string.Join( "," , obj.SF_DataPolling_Object_Fields.Select( x => x.FieldName ) );

                string SOQL = "SELECT " + fields + " FROM " + obj.ObjectName;

                if( obj.LastIntervalUpdate != null )
                {
                    Case c = new Case();

                    SOQL += " WHERE CreatedDate >= " + ( ( DateTime ) obj.LastIntervalUpdate ).ToString( "yyyy'-'MM'-'dd'T'HH':'mm':'sszzzzz" ) + " OR  LastModifiedDate >= " + ( ( DateTime ) obj.LastIntervalUpdate ).ToString( "yyyy'-'MM'-'dd'T'HH':'mm':'sszzzzz" );
                }

                QueryResult queryResult = _sfDCBinding.query( SOQL );

                Type type = Type.GetType( "SF.DataPolling.Business.SFWebRef." + obj.ObjectName );

                PropertyInfo[] propertiesInfo = type.GetProperties();
                List<Tuple<string , object>> list = new List<Tuple<string , object>>();

                string checkVal = string.Empty;

                if( queryResult.size > 0 )
                {

                    string values = string.Empty;

                    while( queryResult.done == false )
                    {
                        for( int i = 0 ; i < queryResult.records.Count() ; i++ )
                        {
                            // Insert the records since last interval update.
                            list = new List<Tuple<string , object>>();

                            string propertyName = string.Empty;

                            for( int k = 0 ; k < propertiesInfo.Count() ; k++ )
                            {
                                propertyName = propertiesInfo[ k ].Name;

                                if( ( from x in obj.Fields where x.FieldName == propertyName select x ).Count() > 0 )
                                {
                                    object value = ( object ) propertiesInfo[ k ].GetValue( queryResult.records[ i ] , null );

                                    list.Add( new Tuple<string , object>( propertyName , value ) );

                                }
                            }

                            int count = dc.spSF_CHECKIFIDEXISTS( queryResult.records[ i ].Id , config.TableDataPrefix + obj.ObjectName );

                            // Check if the ID exists and do update.
                            // If not ADD.
                            if( count < 1 || obj.LastIntervalUpdate == null )
                            {
                                // It's new -- insert
                            }
                            else
                            {
                                // It's an update.
                                //update tbl                                    
                            }
                        }

                        queryResult = _sfDCBinding.queryMore( queryResult.queryLocator );
                    }


                }

            }

        }
        catch( Exception ex )
        {
            throw new Exception( ex.ToString() );
        }

    }

Answer 1

I'm not sure if you can / how you would do a do-while loop in C#, but you can force your loop to loop one last time using this (kind of clunky) technique. (I'd go with a do-while if possible)

Inside your while loop, make it dependent on two variables - one of them the new lastLoop boolean:

Boolean lastLoop = false;
while (queryResult.done == false || lastLoop == true) {

Then at the bottom of the while loop, if queryResult.done == true, set lastLoop to true. Just above this in the code, break if lastLoop is true. It's not super pretty, but it should work.

if (lastLoop == true){
    break;
}
queryResult = _sfDCBinding.queryMore(queryResult.queryLocator);
if (queryResult.done == true){
    lastLoop = true;
}

Answer 2

Reason #1

Since you indicated you are pulling from SFDC, but not seeing all of the Accounts, a likely reason for this is that the SFDC query has decided to return the values in chunks

If using the REST API, you'll see in the beginning of the JSON payload:

{"totalSize":42400,
    "done": false,
    "nextRecordsUrl" : "/services/data/v30.0/query/01g8000001J2eXvAAJ-2000",
   "records" : [ ... ] 
}

SOAP has something equivalent (check the doc)

Your external system needs to check the value of done and then issue the query again using the nextRecordsUrl until done becomes true

The point at which SFDC decides to chunk the SOQL response appears to be driven by the amount of bytes to be transmitted (more fields per record, fewer records delivered) but I haven't investigated this any further

Reason #2

The user used for the SOQl doesn't have read permission to all of the Accounts - check your sharing settings (this would imply a private Account OWD sharing model)

Answer 3

QueryResult qr = binding.query("Select Id,Name From Account");


Int32 CurrentRecord = 0;

for (int x = 0; x < qr.size; x++)
{
    if (CurrentRecord == qr.records.Length)
    {
        qr = binding.queryMore(qr.queryLocator);
        CurrentRecord = 0;
    }

    ProcessAccount((Account)qr.records[CurrentRecord]);

    CurrentRecord++;
}