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.
- cron: a cron schedule; if no cron schedule is specified the call is executed as soon as possible, which is essentially a simple async operation
- start: a UTC start date
- stop: a UTC end date
- correlationId: a logical identifier allowing you to group background operations; for example you can delete all async operations that have a given correlationId value
- snapshotName: friendly name of an edge cache so that it is easier to query; specifying a snapshotName automatically creates a handler with the name provided
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'