VERIFIED SOLUTION i

How to delete leftover bundle in the AWS bundle list

In occasion of power failure, immediate power off of OFMA Server could cause leftover bundle in the AWS bundle list.

Following are the steps to identify the leftover bundle and to delete/remove it from the AWS bundle list.

Run following sql query to identify the leftover bundle:
use PIOfficeMail;

declare 
@fromdate datetime,
@todate datetime,
@bundlename varchar(100);

set @fromdate ='2017-02-14 17:42:00'
set @todate ='2017-02-14 17:43:00'
set @bundlename= '%Colour_Simplex_DL%'

declare @bundleid uniqueidentifier;

SELECT     JobBundleID, JobBundleName, JobBundleStatusID, JobBundleDisplayName, IsOnHold, ManuallyReleasedToProduction, SubmittedToVIPDate, 
                      BundleLogged, JobCount, TotalPages, IsFutureBundle
FROM         JobBundle
WHERE     (JobBundleName LIKE @bundlename) AND (SubmittedToVIPDate > @fromdate) AND (SubmittedToVIPDate < @todate)

SELECT     top 1 @bundleid= JobBundleID
FROM         JobBundle
WHERE     (JobBundleName LIKE @bundlename) AND (SubmittedToVIPDate > @fromdate) AND (SubmittedToVIPDate < @todate)

select COUNT(*) as countOfJobsRelated from Job where job.JobBundleID = @bundleid;


Descriptions:
This query is to check identify the leftover bundle.
In set statements, you have to select values for date and bundlename (remember underscore instead of space, and percent char as prefix and suffix, that says it is a part of the name).
Script should give you only one bundle – if more is selected means that you have to change filtering condition to get only one.
It also gives you the number related jobs to this bundle row; as shown at the AWS bundle list.

Once identify the "set statements" that will filter and resulting in one bundle i.e. the leftover bundle, use the "set statements" for following sql query:
use PIOfficeMail;

declare 
@fromdate datetime,
@todate datetime,
@bundlename varchar(100);

set @fromdate ='2017-02-14 17:42:00'
set @todate ='2017-02-14 17:43:00'
set @bundlename= '%Colour_Simplex_Doctors%'

declare @bundleid uniqueidentifier;

SELECT     JobBundleID, JobBundleName, JobBundleStatusID, JobBundleDisplayName, IsOnHold, ManuallyReleasedToProduction, SubmittedToVIPDate, 
                      BundleLogged, JobCount, TotalPages, IsFutureBundle
FROM         JobBundle
WHERE     (JobBundleName LIKE @bundlename) AND (SubmittedToVIPDate > @fromdate) AND (SubmittedToVIPDate < @todate)

SELECT     top 1 @bundleid= JobBundleID
FROM         JobBundle
WHERE     (JobBundleName LIKE @bundlename) AND (SubmittedToVIPDate > @fromdate) AND (SubmittedToVIPDate < @todate)

delete from JobBundle where JobBundle.JobBundleID=@bundleid


Descriptions:
Since this use the same "set statements" as per the first sql query, it will delete/remove whatever identified as per the first sql query.
So you need to be careful and make sure you are deleting the appropriate bundle.

Note: please make sure you backup the "PIOfficeMail" before you run the second sql query (deletion script)

Please verify and contact us at software.support@pb.com if you have any query.
Environment Details

 
UPDATED:  May 1, 2017