We used Enterprise WSDL to integrate external system to Salesforce. When I try to update the records of a particular field in opportunity, it throws an error that I cannot submit records more than 200. Similarly in getting the records. Is it really the number of records you can retrieve, update, insert to an object is 200? If it is, can I use an offset?
[SalesForce] only update 200 records at once with SOAP API? Enterprise WSDL Limitation
Related Solutions
SF asked that the solution not be disclosed as it is part of their security regime to keep our orgs safe from malefactors. Customers with similar issues should contact SF Support
(I feel weird even writing this)
The underlying technology has put a limit on number of rows, and SSJS is not directly able to get all the records, it would seem.
In order to do what you want, you should actually be using a Query Activity and utilize the _subscribers
system data view.
Here is the link to the query activity documentation
Use the following steps to create a query activity:
Click the Interactions tab on the navigation bar.
Click Activities.
Click Query.
The Queries workspace appears.
Click Create from the toolbar.
Complete the information in the Properties section:
Name - The name of the activity. You use this name to identify the activity in the application. Subscribers cannot see the name.
Key - A value you choose that uniquely identifies the activity. You use this value to identify the activity when using the API.
Description - The description of the activity. You use this description to further help identify the activity within the application. Subscribers cannot see the description.
Query - The SQL that makes up the query. You can click the Check Syntax button to check your SQL.
- Complete the information in the Target section:
Select data extension to populate - The data extension to contain the result of the query. Update Type - Determines how the system updates the data extension with new data. Value values include:
. Overwrite - The system deletes the existing records in the data extension and adds the results of the query.
. Update - The system updates the existing records in the data extension with information that results from the query and appends non-matching records.
. Append - The system adds the results of the query to the data extension after the existing records.
- Click Save.
Queries are basic SQL to access your data extensions, and list data.
Your query might look something like this edited for real world solution
SELECT SubscriberKey AS 'Subscriber Key',
Cast (
CASE
WHEN Status = 'Unsubscribed'
THEN 'false'
ELSE 'true'
END
as varchar(15)) As 'isSubscribed',
Cast(
CASE
WHEN Status = 'Unsubscribed'
THEN 'Perspectives'
END as varchar(15))
As "Publication"
FROM _subscribers
WHERE Status = 'Unsubscribed'
In the query activity - you would select your target data extension. You can perform pretty much any normal SQL function, like joins, nested selects, etc.
The SQL support for the Query Activity is based on SQL Server 2005 capabilities.
- Only SELECT statements to data extension or data views in an account or in the parent account
- Nested Queries
- UNION
- JOIN
- GROUP BY
- Conditional Statements
- IF Constructs
- CASE Statements
- Functions
- Most functions (i.e. MIN, MAX, etc) are supported
- CAST and CONVERT
- Unsupported elements
- Variables
- Cursors
- User Defined Functions
- Transaction and Locking
- GOTO
- Any sp_* stored procedure
- EXEC
- Temporary Tables and Common Table Expressions
- TEXT and IMAGE Functions
Best Answer
Updating the database using SOAP API
You can only send 200 records at once to the DML operations
This is in the documentation under Reference > Core Calls for each of the DML calls. eg from the entry for
upsert
:Fetching records using the SOAP API
You can retrieve up to 2000 records at once. From the same documentation, listed under the
query
call:Bulk API
If you are going to be writing and retrieving huge numbers of records and 200 upload, 2000 download is too small for the number of records you're expecting to be writing, consider using the Bulk API.