|Connected Services Framework 3.0 Developers Guide|
|SQL Server Jobs Used By Profile Integrator|
The Profile Integrator uses a SQL Server stored procedure, named ps_x_ResetState, to timeout or expire any IncomingChangeRequests and OutgoingChangeRequests that have been in operation for a set period of time. A SQL Server job executes this stored procedure every 30 minutes by default.
You can edit this SQL Server job to set the reset and failed elapsed times that the procedure uses to change the request state. The job and stored procedure are located in the Profile Integrator database and performs three distinct tasks:
- It resets change states for any request with its last changed time larger than a configurable parameter. This enables the Profile Integrator to try to process the change request another time. You can edit the @time_in_seconds_to_reset parameter in the SQL Server job to set how long the stored procedure should wait before it fails the state of a request. The default value for the @time_in_seconds_to_reset parameter is 1800 seconds (30 minutes).
- It sets the change state for a request to failed for those requests with its last changed time larger than a configurable parameter. The Profile Integrator does not try to process the change request again after this duration expires. You can edit the @time_in_seconds_to_fail parameter in the SQL Server job to set how long the stored procedure should wait before it fails the state of the request. The default value for the @time_in_seconds_to_reset parameter is 172800 seconds (48 hours).
- It moves any request record that the system will no longer process into a history table in the Profile Integrator database.
The SQL Server job checks the list of all IncomingOrderRequests and OutgoingOrderRequests that have a current state of Processing, CreatingSession, or Rollingback. The job checks the timestamp value for the request. If the difference between the current date and time and the timestamp is greater than 30 minutes, the job automatically assigns a new change state to the request depending on its current state. The job reassigns the following states:
- The Processing state to the Unprocessed state.
- The Rollingback state to the Success state.
- The CreatingSession state to the DataGathered state.
The SQL Server job also expires both IncomingChangeRequests and OutgoingChangeRequests that have been in Processing, Rollingback, or CreatingSession state for more than two days. Under this condition, the SQL Job reassigns the following states:
- The Processing state to the Failed state.
- The Rollingback state to the RollbackFailed state.
- The CreatingSession state to the Failed state.
All OutgoingChangeRequests associated with an IncomingChangeRequest must succeed for the IncomingChangeRequest to succeed. If one outgoing request fails for any reason, the entire IncomingChangeRequest fails. For example, after the SQL Server job sets an OutgoingChangeRequest to a failed state, it automatically sets the state of the associated IncomingChangeRequest to the OutgoingFailed state.
Additionally, the SQL Server job monitors whether the IncomingChangeRequest associated with a failed OutgoingChangeRequest contains any other outgoing requests. If the Profile Integrator has processed all other outgoing requests, it sets the state to Deleting Session instead of failed. This enables the Profile Integrator to end the Session rather than let the session continue with no pending requests.
For more information, see Change Request States.