Overview
This document serves as a guide for all clients using the Eleos Document Poller to reprocess documents that are Emailed and/or File Exported. This can be done by running scripting within the database. Someone with permission to access and edit the database can run the SQL scripts to reprocess the documents. The following scripts allow users to first identify and review documents within a specific time window (from and to) and then with a single change, set those records for reprocessing.
There are two different SQL scripts shown in the sections below, the one within the “Query For Reprocessing Documents” section is most commonly used for reprocessing documents by running the process to save them to a file location again, email them again, or both and the one within the “Query For Advanced Reprocessing Of Documents” should be used more carefully but allows this same ability but using reprocessed (and potentially changed) prefilled values from the associated form and action the document scan was completed from. This advanced query is used for specific scenarios where documents need to be reprocessed but need to have updated prefilled data then what was token-replaced when originally submitted.
Because this “Query For Advanced Reprocessing Of Documents” can change prefilled info that was initially captured when the driver submitted their scan, it is advised to use the “Query For Reprocessing Documents” unless there is specific reason or need to reprocess with potentially updated prefilled data.
These scripts are specifically designed to work with documents which were initially processed following the Q42023 EleosCore enhancements deployment.
Script To Reprocess Documents
Before running the script to reprocess any missing documents, please see the following sections explaining the variables used in the scripts and step-by-step instructions on how to use it.
Variable Explanations
@ReviewRecords - Setting responsible for controlling if the records are output for review or reset to be processed again. When the value is 1, impacted records will be displayed. When the value is 0, the records will be updated within the database to be processed again by the document poller.
@ResendEmails - This value determines if emailed records should be reprocessed or not. If your document poller is not configured to email documents after processing, you will not need this setting. When the value is 1, emailed documents will reprocess. When the value is 0, the records will not be.
@ReExportFiles - This value determines if exported records should be reprocessed or not. If your document poller is not configured to export documents to a directory after processing, you will not need this setting. When the value is 1, emailed documents will reprocess. When the value is 0, the records will not be.
@UploadTo - This value represents the end of the reprocessing window. Documents within the database that were uploaded to Eleos before or at the time specified will be reprocessed. The time should be in a format of 'yyyy-mm-dd hh:mm:ss.0000000 +00:00'. If you are including an offset with the time, the '+00:00' would instead be preceeded by a '-'. Eastern Standard Time for instance would be ‘-05:00’.
@UploadFrom - This value represents the start of the reprocessing window. Documents within the database that were uploaded to Eleos after or at the time specified will be reprocessed. The time should be in a format of 'yyyy-mm-dd hh:mm:ss.0000000 +00:00'. If you are including an offset with the time, the '+00:00' would instead be preceeded by a '-'. Eastern Standard Time for instance would be ‘-05:00’.
Steps For Usage
- Ensure that @ReviewRecords is set to 1 on the initial execution. Before making updates to documents, you should review the results and make sure everything looks accurate beforehand.
- Update @UploadTo and @UploadFrom to create a date range of documents for review and reprocessing.
- Determine the type of processing you would like to perform. Do you need to resend emailed documents, documents exported to a directory, or both.
- Update the corresponding variable based on the processing you intend to perform. Set @ResentEmails to 1 for emailed documents. Set @ReExportFiles to 1 for exported documents. Processing methods not being used or reset should be maintained with a default of 0.
- Once the window has been defined, processing type has been determined and the selected records have been reviewed, update @ReviewRecords to 0 to disable review and execute the script again. This time, instead of outputting the records for review, the updates will be persisted into the database for reprocessing.
Query For Reprocessing Documents
/********************************************************************************************
CONFIGURE VARIABLES FOR REVIEW AND PROCESSING
********************************************************************************************/
;DECLARE /* Reprocessing Controls */
@ReviewRecords BIT = 1
/* Set @ResentEmails to 1 to resend emails. Set @ReExportFiles to 1 to rexport to directory. */
,@ResendEmails BIT = 0
,@ReExportFiles BIT = 0
/* To and From window require date, time and offset - Ex. Both Dates Below are the Same */
,@UploadTo DATETIMEOFFSET = '2026-04-22 18:55:08.6349000 +00:00'
,@UploadFrom DATETIMEOFFSET = '2026-04-21 16:55:08.6349000 +00:00'
/********************************************************************************************
WHEN @ReviewRecords IS 1, SELECT RESULTS BACK FOR REVIEW BEFORE REPROCESSING
********************************************************************************************/
;IF ( @ReviewRecords = 1 )
BEGIN
/* Select DocumentHistory records within the defined UploadTo and UploadFrom window */
/* Note the UploadDate times will be displayed in UTC time */
SELECT [DocumentId] AS 'Id'
,[UserName] AS 'ScannedBy'
,[NumberOfPages] AS 'NumberOfPages'
,[UploadedAt] AS 'UploadDate'
,[LoadNumber] AS 'LoadNumber'
,[BolNumber] AS 'BolNumber'
,[DocumentType] AS 'DocumentType'
FROM Eleos.DocumentHistory
WHERE [UploadedAt] <= @UploadTo
AND [UploadedAt] >= @UploadFrom
ORDER BY [RetryDt] ASC
END
/********************************************************************************************
WHEN REVIEW IS DISABLED, UPDATE RECORDS TO BE REPROCESSED
********************************************************************************************/
ELSE BEGIN
/* Declare a table variable to store DocumentIds as primary key for reprocessing */
;DECLARE @DocumentsToReprocess TABLE (
[DocumentId] VARCHAR(30)
)
/* Insert DocumentIds into the table variable existing within the To/From window */
;INSERT INTO @DocumentsToReprocess(
[DocumentId]
)
SELECT [DocumentId] = [DocumentId]
FROM Eleos.DocumentHistory
WHERE [UploadedAt] <= @UploadTo
AND [UploadedAt] >= @UploadFrom
/********************************************************************************************
UPDATE DocumentFields TO RESET SENT EMAIL FLAG TO FALSE
********************************************************************************************/
;IF ( @ResendEmails = 1 )
BEGIN
;UPDATE Eleos.DocumentFields
SET [FieldValue] = 'false'
WHERE [FieldChecksum] = Eleos.udf_Checksum('true')
AND [FieldName] = 'EmailSent'
AND [DocumentId] IN ( SELECT [DocumentId]
FROM @DocumentsToReprocess )
END
/********************************************************************************************
UPDATE DocumentFields TO RESET EXPORTED FILE FLAG TO FALSE
********************************************************************************************/
;IF ( @ReExportFiles = 1 )
BEGIN
;UPDATE Eleos.DocumentFields
SET [FieldValue] = 'false'
WHERE [FieldChecksum] = Eleos.udf_Checksum('true')
AND [FieldName] = 'FileSaved'
AND [DocumentId] IN ( SELECT [DocumentId]
FROM @DocumentsToReprocess )
END
/********************************************************************************************
UPDATE DOCUMENT HISTORY TO PENDING WHERE REPROCESSING WAS ENABLED
********************************************************************************************/
;IF ( @ReExportFiles = 1
OR @ResendEmails = 1 )
BEGIN
;UPDATE Eleos.DocumentHistory
SET [Pending] = 1
,[Retries] = 0
,[RetryDt] = SYSDATETIMEOFFSET()
,[SentToEmail] = IIF ( @ResendEmails = 1, NULL, [SentToEmail] )
WHERE [DocumentId] IN ( SELECT [DocumentId]
FROM @DocumentsToReprocess )
END
END
Query For Advanced Reprocessing Of Documents
For the query for more advanced reprocessing of documents, there are two versions that perform this same advanced reprocessing but are for whether a clients database has had our SQL contracts restructure implemented or not. You can confirm which to use by checking if your database has the ELEOS.udf_DocumentFields function. If this function does exist, you should utilize the first query below under the “Non-SQL Contracts Version” section. If this function does not exist, you should utilize the second query below under the “SQL Contracts Version” section.
Non-SQL Contracts Version
/********************************************************************************************
CONFIGURE VARIABLES FOR REVIEW AND PROCESSING
********************************************************************************************/
;DECLARE /* Reprocessing Controls */
@ReviewRecords BIT = 1
/* Set @ResentEmails to 1 to resend emails. Set @ReExportFiles to 1 to rexport to directory. */
,@ResendEmails BIT = 0
,@ReExportFiles BIT = 0
/* To and From window require date, time and offset - Ex. Both Dates Below are the Same */
,@UploadTo DATETIMEOFFSET = '2026-04-22 18:55:08.6349000 +00:00'
,@UploadFrom DATETIMEOFFSET = '2026-04-21 16:55:08.6349000 +00:00'
/********************************************************************************************
WHEN @ReviewRecords IS 1, SELECT RESULTS BACK FOR REVIEW BEFORE REPROCESSING
********************************************************************************************/
;IF ( @ReviewRecords = 1 )
BEGIN
/* Select DocumentHistory records within the defined UploadTo and UploadFrom window */
/* Note the UploadDate times will be displayed in UTC time */
SELECT [DocumentId] AS 'Id'
,[UserName] AS 'ScannedBy'
,[NumberOfPages] AS 'NumberOfPages'
,[UploadedAt] AS 'UploadDate'
,[LoadNumber] AS 'LoadNumber'
,[BolNumber] AS 'BolNumber'
,[DocumentType] AS 'DocumentType'
FROM Eleos.DocumentHistory
WHERE [UploadedAt] <= @UploadTo
AND [UploadedAt] >= @UploadFrom
ORDER BY [RetryDt] ASC
END
/********************************************************************************************
WHEN REVIEW IS DISABLED, UPDATE RECORDS TO BE REPROCESSED
********************************************************************************************/
ELSE BEGIN
/* Declare a table variable to store DocumentIds as primary key for reprocessing */
;DECLARE @DocumentsToReprocess TABLE (
[DocumentId] VARCHAR(30)
)
/* Insert DocumentIds into the table variable existing within the To/From window */
;INSERT INTO @DocumentsToReprocess(
[DocumentId]
)
SELECT [DocumentId] = [DocumentId]
FROM Eleos.DocumentHistory
WHERE [UploadedAt] <= @UploadTo
AND [UploadedAt] >= @UploadFrom
/********************************************************************************************
FOR DOCUMENTS TO REPROCESS FULLY DELETE THE FIELDS THEN REPOPULATE WITH UPDATED VALUES
********************************************************************************************/
/* For those Documents, Remove any Fields from udf_DocumentFields. */
;DELETE F
FROM ELEOS.DocumentFields F
WHERE EXISTS( SELECT NULL
FROM @DocumentsToReprocess R
OUTER APPLY ELEOS.udf_DocumentFields(R.[DocumentId]) D
WHERE F.[DocumentId] = R.[DocumentId]
AND F.[FieldName] = D.[FieldName] )
/* Re-insert Updated Values for Document Fields after Removal. */
;INSERT INTO ELEOS.DocumentFields (
[DocumentId]
,[FieldName]
,[FieldValue] )
SELECT [DocumentId] = R.[DocumentId]
,[FieldName] = D.[FieldName]
,[FieldValue] = D.[FieldValue]
FROM @DocumentsToReprocess R
OUTER APPLY ELEOS.udf_DocumentFields(R.[DocumentId]) D
/********************************************************************************************
UPDATE DocumentFields TO RESET SENT EMAIL FLAG TO FALSE
********************************************************************************************/
;IF ( @ResendEmails = 1 )
BEGIN
;UPDATE Eleos.DocumentFields
SET [FieldValue] = 'false'
WHERE [FieldChecksum] = Eleos.udf_Checksum('true')
AND [FieldName] = 'EmailSent'
AND [DocumentId] IN ( SELECT [DocumentId]
FROM @DocumentsToReprocess )
END
/********************************************************************************************
UPDATE DocumentFields TO RESET EXPORTED FILE FLAG TO FALSE
********************************************************************************************/
;IF ( @ReExportFiles = 1 )
BEGIN
;UPDATE Eleos.DocumentFields
SET [FieldValue] = 'false'
WHERE [FieldChecksum] = Eleos.udf_Checksum('true')
AND [FieldName] = 'FileSaved'
AND [DocumentId] IN ( SELECT [DocumentId]
FROM @DocumentsToReprocess )
END
/********************************************************************************************
UPDATE DOCUMENT HISTORY TO PENDING WHERE REPROCESSING WAS ENABLED
********************************************************************************************/
;IF ( @ReExportFiles = 1
OR @ResendEmails = 1 )
BEGIN
;UPDATE Eleos.DocumentHistory
SET [Pending] = 1
,[Retries] = 0
,[RetryDt] = SYSDATETIMEOFFSET()
,[SentToEmail] = IIF ( @ResendEmails = 1, NULL, [SentToEmail] )
WHERE [DocumentId] IN ( SELECT [DocumentId]
FROM @DocumentsToReprocess )
END
END
SQL Contracts Version
/********************************************************************************************
CONFIGURE VARIABLES FOR REVIEW AND PROCESSING
********************************************************************************************/
;DECLARE /* Reprocessing Controls */
@ReviewRecords BIT = 1
/* Set @ResentEmails to 1 to resend emails. Set @ReExportFiles to 1 to rexport to directory. */
,@ResendEmails BIT = 0
,@ReExportFiles BIT = 0
/* To and From window require date, time and offset - Ex. Both Dates Below are the Same */
,@UploadTo DATETIMEOFFSET = '2026-04-22 18:55:08.6349000 +00:00'
,@UploadFrom DATETIMEOFFSET = '2026-04-21 16:55:08.6349000 +00:00'
;CREATE TABLE #DocumentFields (
[DocumentId] NVARCHAR(100)
,[FieldName] VARCHAR(MAX)
,[FieldValue] VARCHAR(MAX)
)
/********************************************************************************************
WHEN @ReviewRecords IS 1, SELECT RESULTS BACK FOR REVIEW BEFORE REPROCESSING
********************************************************************************************/
;IF ( @ReviewRecords = 1 )
BEGIN
/* Select DocumentHistory records within the defined UploadTo and UploadFrom window */
/* Note the UploadDate times will be displayed in UTC time */
SELECT [DocumentId] AS 'Id'
,[UserName] AS 'ScannedBy'
,[NumberOfPages] AS 'NumberOfPages'
,[UploadedAt] AS 'UploadDate'
,[LoadNumber] AS 'LoadNumber'
,[BolNumber] AS 'BolNumber'
,[DocumentType] AS 'DocumentType'
FROM Eleos.DocumentHistory
WHERE [UploadedAt] <= @UploadTo
AND [UploadedAt] >= @UploadFrom
ORDER BY [RetryDt] ASC
END
/********************************************************************************************
WHEN REVIEW IS DISABLED, UPDATE RECORDS TO BE REPROCESSED
********************************************************************************************/
ELSE BEGIN
/* Declare a table variable to store DocumentIds as primary key for reprocessing */
;DECLARE @DocumentsToReprocess TABLE (
[Id] INT IDENTITY(1,1) PRIMARY KEY
,[DocumentId] VARCHAR(30)
)
/* Insert DocumentIds into the table variable existing within the To/From window */
;INSERT INTO @DocumentsToReprocess(
[DocumentId]
)
SELECT [DocumentId] = [DocumentId]
FROM Eleos.DocumentHistory
WHERE [UploadedAt] <= @UploadTo
AND [UploadedAt] >= @UploadFrom
/********************************************************************************************
FOR DOCUMENTS TO REPROCESS FULLY DELETE THE FIELDS THEN REPOPULATE WITH UPDATED VALUES
********************************************************************************************/
/* Delcare looping variables for processing. */
;DECLARE @Index INT = 1
,@Max INT = NULL
,@DocId NVARCHAR(100) = NULL
/* Retrieve and store the maximum index within the table. */
;SELECT @Max = MAX([Id])
FROM @DocumentsToReprocess
/* Loop over the collection, calling the document fields function and incrimenting the index. */
;WHILE ( @Index <= @Max )
BEGIN
/* Identify current document. */
;SELECT @DocId = [DocumentId]
FROM @DocumentsToReprocess
WHERE [Id] = @Index
/* Execute the procedure for the current document. */
;EXEC Eleos.usp_DocumentFields
@p_DocumentId = @DocId
/* Update DocumentId on fields before continuing. */
;UPDATE #DocumentFields
SET [DocumentId] = @DocId
WHERE [DocumentId] IS NULL
OR [DocumentId] = ''
/* Increase the index. */
;SELECT @Index = @Index + 1
END
/* For those Documents, Remove any Fields from Eleos.DocumentFields to avoid duplicates. */
;DELETE F
FROM ELEOS.DocumentFields F
WHERE EXISTS( SELECT NULL
FROM #DocumentFields TF
WHERE F.[DocumentId] = TF.[DocumentId]
AND F.[FieldName] = TF.[FieldName] )
/* Re-insert Updated Values for Document Fields after Removal. */
;INSERT INTO ELEOS.DocumentFields (
[DocumentId]
,[FieldName]
,[FieldValue] )
SELECT [DocumentId] = D.[DocumentId]
,[FieldName] = D.[FieldName]
,[FieldValue] = D.[FieldValue]
FROM #DocumentFields D
/********************************************************************************************
UPDATE DocumentFields TO RESET SENT EMAIL FLAG TO FALSE
********************************************************************************************/
;IF ( @ResendEmails = 1 )
BEGIN
;UPDATE Eleos.DocumentFields
SET [FieldValue] = 'false'
WHERE [FieldChecksum] = Eleos.udf_Checksum('true')
AND [FieldName] = 'EmailSent'
AND [DocumentId] IN ( SELECT [DocumentId]
FROM @DocumentsToReprocess )
END
/********************************************************************************************
UPDATE DocumentFields TO RESET EXPORTED FILE FLAG TO FALSE
********************************************************************************************/
;IF ( @ReExportFiles = 1 )
BEGIN
;UPDATE Eleos.DocumentFields
SET [FieldValue] = 'false'
WHERE [FieldChecksum] = Eleos.udf_Checksum('true')
AND [FieldName] = 'FileSaved'
AND [DocumentId] IN ( SELECT [DocumentId]
FROM @DocumentsToReprocess )
END
/********************************************************************************************
UPDATE DOCUMENT HISTORY TO PENDING WHERE REPROCESSING WAS ENABLED
********************************************************************************************/
;IF ( @ReExportFiles = 1
OR @ResendEmails = 1 )
BEGIN
;UPDATE Eleos.DocumentHistory
SET [Pending] = 1
,[Retries] = 0
,[RetryDt] = SYSDATETIMEOFFSET()
,[SentToEmail] = IIF ( @ResendEmails = 1, NULL, [SentToEmail] )
WHERE [DocumentId] IN ( SELECT [DocumentId]
FROM @DocumentsToReprocess )
END
END