State Error

When ingesting multiple media, if some medias show red instead of green, they are in state error.

To recover from these state errors you can follow these steps:

1. Resolve the storage issue that caused the initial state error.
2. Run the below script to remove the red highlighting from the media.
3. Rerun the "reprocessing the exception files" process on the same media to ensure all records that failed during the initial reprocessing attempts are included.

To remove the state error and to ensure ability to search, run the following script and then run test searches to make sure all media are available.

/*
Script to remove state error
Updated 20230509
Changelog: 
20230509: To allow the state error script to find 
'Ingestion For File Replacement' occurrences, change 
the SQL Insert section from: 
`where Name = 'Ingestion' and [Status] = 2`
to:
`where Name Like '%Ingestion%' and [Status] = 2 `
*/

DECLARE @JobIds TABLE
(
  JobId BIGINT PRIMARY KEY,
  HasStateError BIT
)

INSERT INTO @JobIds
SELECT JobId, OtherSettings.value('(OtherSettings/HasStateError/text())[1]','bit') as HasStateError
from tbl_ds_ProjectJobInfo with (nolock)
where Name Like '%Ingestion%' and [Status] = 2

update tbl_st_StateErrorInfo with (rowlock)
set ResolveType = 'MANUAL',ResolvingUserId = 1, ResolvedDate = GETDATE()
where JobId in (select JobId from @JobIds where HasStateError = 1)
and IsResolved = 0

UPDATE tbl_ds_ProjectJobInfo WITH (ROWLOCK)
Set OtherSettings.modify('replace value of (OtherSettings/HasStateError/text())[1] with "0"')
WHERE JobId in (select JobId from @JobIds where HasStateError = 1) and OtherSettings.exist('/OtherSettings/HasStateError') = 1