"Method Not Allowed" Api error when making multiple API Calls to entity with many records

Rachel Bender

I'm writing a script to get all of our records from the ContactActivity entity, which has 8,098,887 records in it. The first 10 calls, to pages 1-10, might work fine, then I start getting errors (see below). The error, "Method Not Allowed", seems incorrect, as this method does work with this entity on the first 10 calls. I'd appreciate any assistance with this. 2014-05-06 17:51:18: org: 132: REQUEST: redactedSELECT * FROM ContactActivity10020 2014-05-06 17:51:18: org: 132: name: ContactActivity; page: 20 SOAP Fault: (faultcode: HTTP, faultstring: Method Not Allowed)

Eric Pecoraro

Rachel Bender:
I'm writing a script to get all of our records from the ContactActivity entity, which has 8,098,887 records in it. The first 10 calls, to pages 1-10, might work fine, then I start getting errors (see below). The error, "Method Not Allowed", seems incorrect, as this method does work with this entity on the first 10 calls. I'd appreciate any assistance with this. 2014-05-06 17:51:18: org: 132: REQUEST: redactedSELECT * FROM ContactActivity10020 2014-05-06 17:51:18: org: 132: name: ContactActivity; page: 20 SOAP Fault: (faultcode: HTTP, faultstring: Method Not Allowed)

We've run into this problem many times on various calls. On all occasions without exception the API team had to fix the issue. I recommend submitting a support ticket.

Shannon Emery

Rachel Bender:
I'm writing a script to get all of our records from the ContactActivity entity, which has 8,098,887 records in it. The first 10 calls, to pages 1-10, might work fine, then I start getting errors (see below). The error, "Method Not Allowed", seems incorrect, as this method does work with this entity on the first 10 calls. I'd appreciate any assistance with this. 2014-05-06 17:51:18: org: 132: REQUEST: redactedSELECT * FROM ContactActivity10020 2014-05-06 17:51:18: org: 132: name: ContactActivity; page: 20 SOAP Fault: (faultcode: HTTP, faultstring: Method Not Allowed)

Hi Rachel,

I can create a Support ticket for you if you would like to have someone reach out to discuss this issue with you. If you could, you can email socialsupport@blackbaud.com with the Site ID for the organization you are working with and I can get that case started for you.

If you have any questions, please let me know.

Thank you,

Rachel Bender

Shannon Emery:

Hi Rachel,

I can create a Support ticket for you if you would like to have someone reach out to discuss this issue with you. If you could, you can email socialsupport@blackbaud.com with the Site ID for the organization you are working with and I can get that case started for you.

If you have any questions, please let me know.

Thank you,

I sent you an email about an hour ago - thanks!

Arturo Escobedo

Rachel Bender:
I sent you an email about an hour ago - thanks!

Hi, I am assuming that you were using the Query interface. Can you tell me what the query was? I was not able to reproduce your issue though you do say you have 8 million records so I may need to try a different example. thx -Arturo

Rachel Bender

Arturo Escobedo:
Hi, I am assuming that you were using the Query interface. Can you tell me what the query was? I was not able to reproduce your issue though you do say you have 8 million records so I may need to try a different example. thx -Arturo

My query is "Select * from ContactActivity" . This could apply to any other entity with over ~1 mil records ("select * from [any entity]"). I'm just trying to get all the data. I can get the first few pages, even though the call does take like 2 minutes to return. It was because it took so long that I started to process the pages asynchronously. I get the total number of records, divide it by 100 (the number allowed per call), and then try to get all the pages. It seems to overwhelm the system very quickly. So, I started to try batches of 5 every 5 minutes, which works for a while, then starts failing, so I'm trying to work around it. Let me know if I could provide any info that could help.

Arturo Escobedo

Rachel Bender:
My query is "Select * from ContactActivity" . This could apply to any other entity with over ~1 mil records ("select * from [any entity]"). I'm just trying to get all the data. I can get the first few pages, even though the call does take like 2 minutes to return. It was because it took so long that I started to process the pages asynchronously. I get the total number of records, divide it by 100 (the number allowed per call), and then try to get all the pages. It seems to overwhelm the system very quickly. So, I started to try batches of 5 every 5 minutes, which works for a while, then starts failing, so I'm trying to work around it. Let me know if I could provide any info that could help.

In that case I will be trying something like this in a loop and see what behavior I see. try { // Run a query repeatedly to get all the result records, 50 records each time QueryRequest request = new QueryRequest(); request.QueryText = "SELECT * FROM ContactActivity WHERE ContactID> 100113842 ORDER BY CreateDate"; request.PageSize = 100; // Set page size, system max is 100 request.PageNumber = 1; // Set page # while (true) { QueryResult result = kinteraConnect.Query(request); if (result.Total > 0) { // Console.WriteLine("Total: " + result.Total); // Console.WriteLine("Page #: " + request.PageNumber); // Print record details foreach (BusinessEntity record in result.Records) { ContactActivity contactActivity = (ContactActivity)record; Console.WriteLine("ContactActivityID: " + contactActivity.ContactActivityID); Console.WriteLine("Subject: " + contactActivity.Subject); Console.WriteLine("Description: " + contactActivity.Description); Console.WriteLine("OwnerID: " + contactActivity.OwnerID); Console.WriteLine("Owner: " + contactActivity.Owner); Console.WriteLine("ActivityID: " + contactActivity.ActivityID); Console.WriteLine("ContactID: " + contactActivity.ContactID); Console.WriteLine("EventID: " + contactActivity.EventID); Console.WriteLine("SourceCode: " + contactActivity.SourceCode); Console.WriteLine("CreateDate: " + contactActivity.CreateDate); Console.WriteLine(); } if (request.PageNumber * request.PageSize >= result.Total) { break; // All records have been retrieved, exit the loop } else { request.PageNumber++; // Increase page #, get next batch } } else { break; // No records found, exit loop } } } catch (Exception ex) { Console.WriteLine(" ex.Message ); }

Arturo Escobedo

Arturo Escobedo:
In that case I will be trying something like this in a loop and see what behavior I see. try { // Run a query repeatedly to get all the result records, 50 records each time QueryRequest request = new QueryRequest(); request.QueryText = "SELECT * FROM ContactActivity WHERE ContactID> 100113842 ORDER BY CreateDate"; request.PageSize = 100; // Set page size, system max is 100 request.PageNumber = 1; // Set page # while (true) { QueryResult result = kinteraConnect.Query(request); if (result.Total > 0) { // Console.WriteLine("Total: " + result.Total); // Console.WriteLine("Page #: " + request.PageNumber); // Print record details foreach (BusinessEntity record in result.Records) { ContactActivity contactActivity = (ContactActivity)record; Console.WriteLine("ContactActivityID: " + contactActivity.ContactActivityID); Console.WriteLine("Subject: " + contactActivity.Subject); Console.WriteLine("Description: " + contactActivity.Description); Console.WriteLine("OwnerID: " + contactActivity.OwnerID); Console.WriteLine("Owner: " + contactActivity.Owner); Console.WriteLine("ActivityID: " + contactActivity.ActivityID); Console.WriteLine("ContactID: " + contactActivity.ContactID); Console.WriteLine("EventID: " + contactActivity.EventID); Console.WriteLine("SourceCode: " + contactActivity.SourceCode); Console.WriteLine("CreateDate: " + contactActivity.CreateDate); Console.WriteLine(); } if (request.PageNumber * request.PageSize >= result.Total) { break; // All records have been retrieved, exit the loop } else { request.PageNumber++; // Increase page #, get next batch } } else { break; // No records found, exit loop } } } catch (Exception ex) { Console.WriteLine(" ex.Message ); }

// Let me try this again. Having issues posting with Chrome   

  try

            {

            // Run a query repeatedly to get all the result records, 50 records each time

            QueryRequest request = new QueryRequest();

            request.QueryText = "SELECT * FROM ContactActivity WHERE ContactID> 100113842 ORDER BY CreateDate";

            request.PageSize = 50;  // Set page size, system max is 100

            request.PageNumber = 1; // Set page #

            while (true)

            {

                QueryResult result = kinteraConnect.Query(request);

                if (result.Total > 0)

                {

                //    Console.WriteLine("Total: " + result.Total);

                //    Console.WriteLine("Page #: " + request.PageNumber);

                    // Print record details

                    foreach (BusinessEntity record in result.Records)

                    {

                        ContactActivity contactActivity = (ContactActivity)record;

                        Console.WriteLine("ContactActivityID: " + contactActivity.ContactActivityID);

                        Console.WriteLine("Subject: " + contactActivity.Subject);

                        Console.WriteLine("Description: " + contactActivity.Description);

                        Console.WriteLine("OwnerID: " + contactActivity.OwnerID);

                        Console.WriteLine("Owner: " + contactActivity.Owner);

                        Console.WriteLine("ActivityID: " + contactActivity.ActivityID);

                        Console.WriteLine("ContactID: " + contactActivity.ContactID);

                        Console.WriteLine("EventID: " + contactActivity.EventID);

                        Console.WriteLine("SourceCode: " + contactActivity.SourceCode);

                        Console.WriteLine("CreateDate: " + contactActivity.CreateDate);

                        Console.WriteLine();

                    }

                    if (request.PageNumber * request.PageSize >= result.Total)

                    {

                        break;  // All records have been retrieved, exit the loop

                    }

                    else

                    {

                        request.PageNumber++; // Increase page #, get next batch

                    }

                }

                else

                {

                    break;  // No records found, exit loop

                }

            }

            }

            catch (Exception ex)

            {

                string message = ex.Message ;

            }

Eric Pecoraro

Arturo Escobedo:

// Let me try this again. Having issues posting with Chrome   

  try

            {

            // Run a query repeatedly to get all the result records, 50 records each time

            QueryRequest request = new QueryRequest();

            request.QueryText = "SELECT * FROM ContactActivity WHERE ContactID> 100113842 ORDER BY CreateDate";

            request.PageSize = 50;  // Set page size, system max is 100

            request.PageNumber = 1; // Set page #

            while (true)

            {

                QueryResult result = kinteraConnect.Query(request);

                if (result.Total > 0)

                {

                //    Console.WriteLine("Total: " + result.Total);

                //    Console.WriteLine("Page #: " + request.PageNumber);

                    // Print record details

                    foreach (BusinessEntity record in result.Records)

                    {

                        ContactActivity contactActivity = (ContactActivity)record;

                        Console.WriteLine("ContactActivityID: " + contactActivity.ContactActivityID);

                        Console.WriteLine("Subject: " + contactActivity.Subject);

                        Console.WriteLine("Description: " + contactActivity.Description);

                        Console.WriteLine("OwnerID: " + contactActivity.OwnerID);

                        Console.WriteLine("Owner: " + contactActivity.Owner);

                        Console.WriteLine("ActivityID: " + contactActivity.ActivityID);

                        Console.WriteLine("ContactID: " + contactActivity.ContactID);

                        Console.WriteLine("EventID: " + contactActivity.EventID);

                        Console.WriteLine("SourceCode: " + contactActivity.SourceCode);

                        Console.WriteLine("CreateDate: " + contactActivity.CreateDate);

                        Console.WriteLine();

                    }

                    if (request.PageNumber * request.PageSize >= result.Total)

                    {

                        break;  // All records have been retrieved, exit the loop

                    }

                    else

                    {

                        request.PageNumber++; // Increase page #, get next batch

                    }

                }

                else

                {

                    break;  // No records found, exit loop

                }

            }

            }

            catch (Exception ex)

            {

                string message = ex.Message ;

            }

This is not a valid query for ContactActivity:



SELECT * FROM ContactActivity WHERE ContactID> 100113842 ORDER BY CreateDate



CreateDate is NOT searchable



Only the following are searchable:



ContactActivityID, OwnerID, ContactID



This would be valid:



SELECT * FROM ContactActivity WHERE ContactID > 100113842





HOWEVER, the issue here is a database performance problem, so this should be escalated to engineering/development.



ContactActivity seems to be a poorly optimized table at the source database. I have had problems querying on it with ANY account that has several million records.



Based on my experience, and based on the fact that the API doesn't even have a dedicated development support ticketing mechanism, or support forum (it used to), I doubt Blackbaud is going to fix/improve/optimize this entity table.



So, you probably right by querying against ContactID because that would limit the result set from several million records, to likely several thousand max (probably much fewer).



So, as a workaround, one could iterate through every single contact (ContactID) in the database, then query for each one's activity records. And/or, do it in batches as follows: SELECT * FROM ContactActivity WHERE ContactID IN (281219283,281220230,[...etc])



This is a hack, and it will inevitably cause the query limit to be exceeded with large data sets, but it may be the only way to query against all the ContactActivity records at one time on a large data set.

Eric Pecoraro

Eric Pecoraro:
This is not a valid query for ContactActivity:



SELECT * FROM ContactActivity WHERE ContactID> 100113842 ORDER BY CreateDate



CreateDate is NOT searchable



Only the following are searchable:



ContactActivityID, OwnerID, ContactID



This would be valid:



SELECT * FROM ContactActivity WHERE ContactID > 100113842





HOWEVER, the issue here is a database performance problem, so this should be escalated to engineering/development.



ContactActivity seems to be a poorly optimized table at the source database. I have had problems querying on it with ANY account that has several million records.



Based on my experience, and based on the fact that the API doesn't even have a dedicated development support ticketing mechanism, or support forum (it used to), I doubt Blackbaud is going to fix/improve/optimize this entity table.



So, you probably right by querying against ContactID because that would limit the result set from several million records, to likely several thousand max (probably much fewer).



So, as a workaround, one could iterate through every single contact (ContactID) in the database, then query for each one's activity records. And/or, do it in batches as follows: SELECT * FROM ContactActivity WHERE ContactID IN (281219283,281220230,[...etc])



This is a hack, and it will inevitably cause the query limit to be exceeded with large data sets, but it may be the only way to query against all the ContactActivity records at one time on a large data set.

...

Eric Pecoraro

Rachel Bender:
I'm writing a script to get all of our records from the ContactActivity entity, which has 8,098,887 records in it. The first 10 calls, to pages 1-10, might work fine, then I start getting errors (see below). The error, "Method Not Allowed", seems incorrect, as this method does work with this entity on the first 10 calls. I'd appreciate any assistance with this. 2014-05-06 17:51:18: org: 132: REQUEST: redactedSELECT * FROM ContactActivity10020 2014-05-06 17:51:18: org: 132: name: ContactActivity; page: 20 SOAP Fault: (faultcode: HTTP, faultstring: Method Not Allowed)

...editor is mangling my responses...

Eric Pecoraro

Rachel Bender:
I'm writing a script to get all of our records from the ContactActivity entity, which has 8,098,887 records in it. The first 10 calls, to pages 1-10, might work fine, then I start getting errors (see below). The error, "Method Not Allowed", seems incorrect, as this method does work with this entity on the first 10 calls. I'd appreciate any assistance with this. 2014-05-06 17:51:18: org: 132: REQUEST: redactedSELECT * FROM ContactActivity10020 2014-05-06 17:51:18: org: 132: name: ContactActivity; page: 20 SOAP Fault: (faultcode: HTTP, faultstring: Method Not Allowed)

There are also alternatives to reduce the overall result set, which may help:





There is a away to query by time using the GetUpdated call:



http://www.kintera.com/doc/connect/default.htm#ContactActivity.htm



I believe this returns up to 5,000 records at a time, so you would have to apply logic to iterate through time periods if more than 5K is returned, which can be dicey...especially if activity is being logged for a huge email campaign would could record several thousand records.





Also, alternatively, to reduce result sets, you can query against only the ContactActivity types (via ContactActivityID assignments) that you specify. It's undocumented, but you can get the IDs for specific ContactActivity types from the Sphere Admin (like bulk email) and exclude them from your query, like so:



SELECT * FROM ContactActivity WHERE ContactActivityID NOT IN (XX,XX,XX,XX)



You can find all the ContactActivityID types by clicking the "Activity Type Association" of a contact for (which takes you to https://www.kintera.com/kintera_sphere/events/asp/activityTypeAssociation.asp?var_functionid=development) then looking at the HTML from that page, which will will show the ContactActivityID beside the name as follows:



<input type="Checkbox" name="activityTypeID" value="2" id="Checkbox1">>Registration



So, the ContactActivityID for Registrations is 2. Therefore if you just wanted to isolate registrations, you could query like:



SELECT * FROM ContactActivity WHERE ContactActivityID = 2



etc, etc.

Rachel Bender

Eric Pecoraro:
There are also alternatives to reduce the overall result set, which may help:





There is a away to query by time using the GetUpdated call:



http://www.kintera.com/doc/connect/default.htm#ContactActivity.htm



I believe this returns up to 5,000 records at a time, so you would have to apply logic to iterate through time periods if more than 5K is returned, which can be dicey...especially if activity is being logged for a huge email campaign would could record several thousand records.





Also, alternatively, to reduce result sets, you can query against only the ContactActivity types (via ContactActivityID assignments) that you specify. It's undocumented, but you can get the IDs for specific ContactActivity types from the Sphere Admin (like bulk email) and exclude them from your query, like so:



SELECT * FROM ContactActivity WHERE ContactActivityID NOT IN (XX,XX,XX,XX)



You can find all the ContactActivityID types by clicking the "Activity Type Association" of a contact for (which takes you to https://www.kintera.com/kintera_sphere/events/asp/activityTypeAssociation.asp?var_functionid=development) then looking at the HTML from that page, which will will show the ContactActivityID beside the name as follows:



<input type="Checkbox" name="activityTypeID" value="2" id="Checkbox1">>Registration



So, the ContactActivityID for Registrations is 2. Therefore if you just wanted to isolate registrations, you could query like:



SELECT * FROM ContactActivity WHERE ContactActivityID = 2



etc, etc.

Thanks for your suggestions / ideas, I really appreciate it. Before I start getting creative .. Arturo, any updates on this issue?

Eric Pecoraro

Rachel Bender:
Thanks for your suggestions / ideas, I really appreciate it. Before I start getting creative .. Arturo, any updates on this issue?

No problem, however... I jumped the gun on "ContactActivityID". What I said about that holds true for "ActivityID". BUT, unfortunately, ActivityID is NOT searchable via the API.



I mis-spoke above. We use ActivityID to filter results on one of our offline/SQL sync'd databases AFTER we have pulled the records from the Sphere Connect API.



So, unfortunately, you'll have to work with the other options/workarounds suggested. I highly doubt BB will fix/optimize this table, or make ActivityID searchable (which I requested eons ago), so I would recommend that you start getting creative sooner than later... .

Eric Pecoraro

Eric Pecoraro:
No problem, however... I jumped the gun on "ContactActivityID". What I said about that holds true for "ActivityID". BUT, unfortunately, ActivityID is NOT searchable via the API.



I mis-spoke above. We use ActivityID to filter results on one of our offline/SQL sync'd databases AFTER we have pulled the records from the Sphere Connect API.



So, unfortunately, you'll have to work with the other options/workarounds suggested. I highly doubt BB will fix/optimize this table, or make ActivityID searchable (which I requested eons ago), so I would recommend that you start getting creative sooner than later... .

For the record, in case anyone at Blackbaud dev would care to be proactive on this issue of ContactActivity inaccessibility :





1. Enhance the current DescribeSetup->ActivityID

(Return system generated ActivityIDs in addition to the user generated ActivityIDs currently returned)



2. Make ContactActivity.ActivityID searchable/indexed





These enhancements would take a huge load off of the API resources, as well as taking the burden off of consumers attempting to shoehorn data out of ContactActivity.

Arturo Escobedo

Eric Pecoraro:
For the record, in case anyone at Blackbaud dev would care to be proactive on this issue of ContactActivity inaccessibility :





1. Enhance the current DescribeSetup->ActivityID

(Return system generated ActivityIDs in addition to the user generated ActivityIDs currently returned)



2. Make ContactActivity.ActivityID searchable/indexed





These enhancements would take a huge load off of the API resources, as well as taking the burden off of consumers attempting to shoehorn data out of ContactActivity.

Sorry I am getting sidetracked and our resources are somewhat limited here. Short term I think that you are dealing with too many records are a time. I think you mentioned you are making multiple calls so that would be multiple sets of 8 million each which have to be looked at and record My short term suggestion before looking at it is to use the contact id as the limiting factor. for example SELECT * FROM ContactActivity WHERE ContactID> 100113842 and ContactID < 100213842 then process all the records you get next time add maybe 100,000 or 200000 to your possible set SELECT * FROM ContactActivity WHERE ContactID> 100213842 and ContactID < 100313842 Regardless of wether you know the actual ContactID or not you will only be retrieving records that you were approved for but at the most you are querying for 100000 at a time Later on I can try creating a new project and running the example again I don't know if I grabbed the wrong wsdl but I made this call SELECT * FROM ContactActivity WHERE ContactID> 100113842 ORDER BY CreateDate"; and Eric was saying that CreateDate was invalid so I may have to track down a valid wsdl. I was going to use CreateDatea to track down the largest number.

Arturo Escobedo

Arturo Escobedo:
Sorry I am getting sidetracked and our resources are somewhat limited here. Short term I think that you are dealing with too many records are a time. I think you mentioned you are making multiple calls so that would be multiple sets of 8 million each which have to be looked at and record My short term suggestion before looking at it is to use the contact id as the limiting factor. for example SELECT * FROM ContactActivity WHERE ContactID> 100113842 and ContactID < 100213842 then process all the records you get next time add maybe 100,000 or 200000 to your possible set SELECT * FROM ContactActivity WHERE ContactID> 100213842 and ContactID < 100313842 Regardless of wether you know the actual ContactID or not you will only be retrieving records that you were approved for but at the most you are querying for 100000 at a time Later on I can try creating a new project and running the example again I don't know if I grabbed the wrong wsdl but I made this call SELECT * FROM ContactActivity WHERE ContactID> 100113842 ORDER BY CreateDate"; and Eric was saying that CreateDate was invalid so I may have to track down a valid wsdl. I was going to use CreateDatea to track down the largest number.

 Hmm. Google is removing all the formatting.

Sorry I am getting sidetracked and our resources are somewhat limited here.

Short term I think that you are dealing with too many records are a time. I think you mentioned you are making multiple calls so that would be multiple sets of 8 million each which have to be looked at and record

My short term suggestion before looking at it is to use the contact id as the limiting factor. for example

SELECT * FROM ContactActivity WHERE ContactID> 100113842 and ContactID < 100213842

then process all the records you get

next time add maybe 100,000 or 200000 to your possible set

SELECT * FROM ContactActivity WHERE ContactID> 100213842 and ContactID < 100313842

Regardless of wether you know the actual ContactID or not you will only be retrieving records that you were approved for but at the most you are querying for 100000 at a time

Later on I can try creating a new project and running the example again I don't know if I grabbed the wrong wsdl but I made this call

SELECT * FROM ContactActivity WHERE ContactID> 100113842 ORDER BY CreateDate"; and Eric was saying that CreateDate was invalid so I may have to track down a valid wsdl. I was going to use CreateDatea to track down the largest number.

Rachel Bender

Arturo Escobedo:

 Hmm. Google is removing all the formatting.

Sorry I am getting sidetracked and our resources are somewhat limited here.

Short term I think that you are dealing with too many records are a time. I think you mentioned you are making multiple calls so that would be multiple sets of 8 million each which have to be looked at and record

My short term suggestion before looking at it is to use the contact id as the limiting factor. for example

SELECT * FROM ContactActivity WHERE ContactID> 100113842 and ContactID < 100213842

then process all the records you get

next time add maybe 100,000 or 200000 to your possible set

SELECT * FROM ContactActivity WHERE ContactID> 100213842 and ContactID < 100313842

Regardless of wether you know the actual ContactID or not you will only be retrieving records that you were approved for but at the most you are querying for 100000 at a time

Later on I can try creating a new project and running the example again I don't know if I grabbed the wrong wsdl but I made this call

SELECT * FROM ContactActivity WHERE ContactID> 100113842 ORDER BY CreateDate"; and Eric was saying that CreateDate was invalid so I may have to track down a valid wsdl. I was going to use CreateDatea to track down the largest number.

Thanks, I'll try that and I'll let you know how it goes.

Rachel Bender

Rachel Bender:
Thanks, I'll try that and I'll let you know how it goes.

So, this method is working when I can query by a primary key, it seems. For ContactActivity, I make a call, with a min and max ContactActivityId in my query, and then base my next call on the largest id I get back. I make the min the largest id I get back, and I set the max to min+100,000. This means I have to process the calls sequentially, as opposed to asynchronously, which is slower, but I get back good data, so it works, and the calls come back in 1-2 seconds. However, now I'm running into problems with entities that do not allow queries using a primary key, like ContactMembership, which only allows queries by DateUpdated. I thought I would be able to create one solution, but looks like I'm going to need several. I don't suppose there's some undocumented way of querying by ID, is there?

Arturo Escobedo

Rachel Bender:
So, this method is working when I can query by a primary key, it seems. For ContactActivity, I make a call, with a min and max ContactActivityId in my query, and then base my next call on the largest id I get back. I make the min the largest id I get back, and I set the max to min+100,000. This means I have to process the calls sequentially, as opposed to asynchronously, which is slower, but I get back good data, so it works, and the calls come back in 1-2 seconds. However, now I'm running into problems with entities that do not allow queries using a primary key, like ContactMembership, which only allows queries by DateUpdated. I thought I would be able to create one solution, but looks like I'm going to need several. I don't suppose there's some undocumented way of querying by ID, is there?

Sorry, in the case of ContactMembership you are not allowed to use a query, you would probably need to call using RetrieveMultiple using the contact id's retrieved. That would be the type of thing that you leave running overnight I suppose. In your case maybe longer since you are dealing with a few millions. Not sure what you are trying to get exactly but some of this may be more efficient from the batch export data interface in Sphere , unless you are trying to get the data in a custom format etc...

Rachel Bender

Arturo Escobedo:
Sorry, in the case of ContactMembership you are not allowed to use a query, you would probably need to call using RetrieveMultiple using the contact id's retrieved. That would be the type of thing that you leave running overnight I suppose. In your case maybe longer since you are dealing with a few millions. Not sure what you are trying to get exactly but some of this may be more efficient from the batch export data interface in Sphere , unless you are trying to get the data in a custom format etc...

Arturo, thanks for your help so far, I'm almost there. I'm now dealing with entities for which I must use CreatedDate, or some other date field, because they can't be queried by ID. It seems like this works: SELECT * FROM Event where CreateDate >=01-14-2007 but this does not work: SELECT * FROM Event where CreateDate >=01-14-2007 and CreateDate <=01-14-2014 So, I can query records that are greater than a date, but not less than. Any ideas why this is?

Eric Pecoraro

Rachel Bender:
Arturo, thanks for your help so far, I'm almost there. I'm now dealing with entities for which I must use CreatedDate, or some other date field, because they can't be queried by ID. It seems like this works: SELECT * FROM Event where CreateDate >=01-14-2007 but this does not work: SELECT * FROM Event where CreateDate >=01-14-2007 and CreateDate <=01-14-2014 So, I can query records that are greater than a date, but not less than. Any ideas why this is?

Have you tried?:



SELECT * FROM Event WHERE CreateDate BETWEEN '2007-01-14' AND '2014-01-14'

Rachel Bender

Eric Pecoraro:
Have you tried?:



SELECT * FROM Event WHERE CreateDate BETWEEN '2007-01-14' AND '2014-01-14'

Actually, yes, that does work. Is this the same for the ContactPledge entity? In the docs, it's written "MM/DD/YYYY, date that Pledge is entered". My query for the ContactPledge entity is not working, however. It takes about a minute, then I see this: PHP Error[2]: SoapClient::__doRequest(): SSL: Connection reset by peer in file /var/www/sphere/protected/components/SphereApi.php at line 229 My queries do seem to work with the Events entity. Here's my query: "SELECT * FROM ContactPledge where Date between '2013-06-09' and '2013-07-09' order by Date ASC". I then tried a smaller date range: "SELECT * FROM ContactPledge where Date between '2013-06-09' and '2013-06-11' order by Date ASC" But this still takes a minute and then errors out. Can you look into this?

Eric Pecoraro

Rachel Bender:
Actually, yes, that does work. Is this the same for the ContactPledge entity? In the docs, it's written "MM/DD/YYYY, date that Pledge is entered". My query for the ContactPledge entity is not working, however. It takes about a minute, then I see this: PHP Error[2]: SoapClient::__doRequest(): SSL: Connection reset by peer in file /var/www/sphere/protected/components/SphereApi.php at line 229 My queries do seem to work with the Events entity. Here's my query: "SELECT * FROM ContactPledge where Date between '2013-06-09' and '2013-07-09' order by Date ASC". I then tried a smaller date range: "SELECT * FROM ContactPledge where Date between '2013-06-09' and '2013-06-11' order by Date ASC" But this still takes a minute and then errors out. Can you look into this?

If I recall correctly, ContactPayment has been deprecated for several years now. Not sure about ContactPledge, but I would assume the same.



That's likely the reason for the poor performance. Not sure if BB engineering will provide performance support on those because they have been deprecated for so long...

Rachel Bender

Eric Pecoraro:
If I recall correctly, ContactPayment has been deprecated for several years now. Not sure about ContactPledge, but I would assume the same.



That's likely the reason for the poor performance. Not sure if BB engineering will provide performance support on those because they have been deprecated for so long...

Weird, it's still in the docs, https://www.blackbaud.com/files/support/helpfiles/connect/default.htm . Thanks, Eric, I'm not sure what we would do without you. What is the new version of ContactPayment? Is it ContactActivity? To BB, is there anyway I could find out what entities (at the above link) are deprecated? Anything would be appreciated.

Eric Pecoraro

Rachel Bender:
Weird, it's still in the docs, https://www.blackbaud.com/files/support/helpfiles/connect/default.htm . Thanks, Eric, I'm not sure what we would do without you. What is the new version of ContactPayment? Is it ContactActivity? To BB, is there anyway I could find out what entities (at the above link) are deprecated? Anything would be appreciated.

I think ContactPayment was deprecated in 2007/8.



Here are some links to resources we built that may be helpful regarding schema relationships:



http://shepardclient.s3.amazonaws.com/sphere/2012-06-24-160637-Connect-Describe-All-Entities.xlsx



http://shepardclient.s3.amazonaws.com/sphere/Shepard.com-Sphere-ERD.pdf

Arturo Escobedo

Eric Pecoraro:
I think ContactPayment was deprecated in 2007/8.



Here are some links to resources we built that may be helpful regarding schema relationships:



http://shepardclient.s3.amazonaws.com/sphere/2012-06-24-160637-Connect-Describe-All-Entities.xlsx



http://shepardclient.s3.amazonaws.com/sphere/Shepard.com-Sphere-ERD.pdf

Sorry I can't reproduce your issue with ContactPledge, Can you try removing the Order by Date part with the same query. The queries are ultimately going off of views joined with other viewsl. (i.e. ContactPledge is not a table). You can try sending me a private message with your account id or account name, would need to try this against different data probably to see your issue. thanks -Arturo