Change Data Capture
Logins & ACL
Enzo Server offers a built-in scheduler that can be used to execute commands on a regular interval. The scheduler is an extension of the async call feature of Enzo with the addition of a cron definition. See the async feature for more information on how to start a background request. This section explains how to define the scheduling information.
View Scheduled Operations
To view currently scheduled operations against an adapter you need to use SQL Server Management Studio, connect to Enzo Server, and issue a query against the _asyncScheduled handler.
The SQL command returns all available scheduled operations, their cron schedule, and next execution time.
select HandlerName, _reqUid, _reqCorrelationId, _reqStartAtUtc, _reqEndAtUtc, _reqInterval, LastExecuted, NextFireTime from SharePoint._asyncScheduled
Other interesting columns include LastError, which returns the last error message if any. The Login and Config columns returns which login/configuration is being used when executing the async operation.
Schedule New Operation
To schedule a new async operation with Enzo Server, you need to add the __asyncAt extension to a valid SQL command and
specify a cron schedule with additional options. The general format for the __asyncAt extension is as follows, where the parameters
list are those supported by the handler:
Scheduled Async Call
EXEC [adapter].[handler]__asyncAt 'options' [,parameter1...]
The __asyncAt options are as follows:
All the values in the option list are optional.
Read Operation Output
Once the call of a background operation has completed at least once, you can access the output generated in two ways: either using the
snapshotName if one was provided, or using the asyncResult handler.
Using a snapshot name
The simplest way to access data from a scheduled operation is to specify a snapshot name during the creation of the async call, and to use the snapshot name using a SELECT operation:
SELECT * FROM adapter.[snapshotName]
Using the async job _reqUid
You can also access data from a scheduled operation using its _reqUid, which is a unique identifier assigned when the operation is created:
SELECT * FROM adapter._asyncResult WHERE _reqUid='918fb98f-cbe9-4260-88ff-357d1592e13e'
List available result sets
Call the _asyncResultsList handler to obtain the list of available results.
SELECT * FROM adapter._asyncResultsList
This handler takes a few optional parameters to help you filter the information. For example you can list all available outputs given the correlationId:
SELECT * FROM adapter._asyncResultsList WHERE _correlationId='mygroup'
Rerun an async operation
You can execute an async operation manually as many times as you want, even if it is scheduled for operation at a later time.
To do so, call the _asyncRefresh handler with the operation _reqUid or the correlationId (using a correlationId reruns all the
operations that have the same correlation value):
--Rerun a single operation by _reqUid EXEC adapter._asyncRefresh '918fb98f-cbe9-4260-88ff-357d1592e13e'
--Rerun all operations that belong to the specified collectionId EXEC adapter._asyncRefresh null, 'mygroup'
Cancel/delete async operation
To remove a scheduled operation, and delete the associated cached data set, call the _asyncCancel handler:
--Cancel/delete a single operation by _reqUid EXEC adapter._asyncCancel '918fb98f-cbe9-4260-88ff-357d1592e13e'
--Cancel/delete all operations that belong to the specified collectionId EXEC adapter._asyncCancel null, 'mygroup'
--Other way to cancel/delete all operations that belong to the specified collectionId DELETE FROM adapter._asyncRequest WHERE _reqCorrelationId='mygroup'
Run a background operation every 5 minutes
The following code creates a scheduled operation every 5 minutes, retrieves the cached data, and deletes it.
The code sample provided below runs directly against an Enzo Server. However you will need to replace the _reqUid with the one assigned to you by the __asyncAt handler.
-- This is the SQL command that returns data from SharePoint in real-time that we want to schedule EXEC sharepoint.getlistitemsex 'US States', 'ID,Title,State Code,Created' -- Let's run this call asynchronously every 5 minutes by adding __asyncAt EXEC sharepoint.getlistitemsex__asyncAt '0 0/5 * * * ?', 'US States', 'ID,Title,State Code,Created' -- The previous method returned a _reqUid: efd55d0f-17b9-4e06-86cf-eca6df5e3c7e -- We can see the scheduled operation here: SELECT * FROM sharepoint._asyncScheduled -- Let's force execution of the background operation now: EXEC sharepoint._asyncRefresh 'efd55d0f-17b9-4e06-86cf-eca6df5e3c7e' -- wait a few seconds, and access the cached data set: SELECT * FROM SharePoint._asyncResult WHERE _reqUid = 'efd55d0f-17b9-4e06-86cf-eca6df5e3c7e' -- delete the cached data set and cancel any future scheduled operation: exec SharePoint._asyncCancel 'efd55d0f-17b9-4e06-86cf-eca6df5e3c7e'