MoveData - Common and Useful Queries

Sent/Not Confirmed – Select – This query will return all results for items that should have received a confirmation date from DMS, but have not. (Use Document, Trans_Head, or MemberIDImage for the appropriate database). 

select * from Document
where DateSend is not null
and DateConfirm is null
and DateSend <=getdate()-3

 

Sent/Not Confirmed – Re-send – This query will update the DateSend date so that the documents without confirmation dates can be re-sent.

update Document
set DateSend=null
where DateSend is not null
and DateConfirm is null
and DateSend <=getdate()-3

 

 

Reindex Required – If a user is experiencing an error stating a document has an invalid DMS DocID, you’ll need to “unlock” that document for a Credit Union user to be able to fix the DMS DocID, so that it can then move to DMS. Most times just using the Sent/Not Confirmed – Re-send command to unlock the documents works just fine, but you can use the below queries for more specific needs.

 

Find Specific Document The “DocumentID”  value can be pulled from the nightly error report.

select * from Document
where DateSend is not null
and DocumentID='XXXXX'

 

‘Unlock’ Specific Document

update Document
set DateSend=null
where DateSend is not null
and DocumentID='XXXXX'

 

Return All Documents with Short DMS DocIDs - This will return a list of all documents with DMS DocIDs shorter than 12 characters. This list can then be copied from into an excel file that can then be sent to a customer. Most clients should already receive a list of "Reindex Required" documents, but this can be used for any client where that isn't the case. 

select Document.DocumentDescription, Document.DateConfirm, Document.DateSend, DocField.FieldValue, DocField.IndexFieldID, DocumentMember.MemberNo
from Document
Inner Join DocField on Document.DocumentID=DocField.DocumentID
Inner Join DocumentMember on Document.DocumentID=DocumentMember.DocumentID
where IndexFieldID='3'
and LEN(FieldValue) < 12
and DateSend is not null
and DateConfirm is null
and DateSend<=getdate()-3
and FieldValue!=''

 

 

"No Row at Position 0" Error in MoveData Log - This refers to an entry in the Teller Database, specifically for ScanID. This essentially means the item that the error is referring to doesn't exist anymore. To resolve these errors, we have to manually delete those items from the Database. The ID value can be found in the error log. 

 

Find ScanID Entry

select * from MemberIDImage
where ID='XXXX'

 

Delete ScanID Entry

delete from MemberIDImage
where ID='XXXX'