On Searching transaction error message "An error has been encountered during the search function"

On Searching transaction error message "An error has been encountered during the search function"

Version

6.6

Issue

When searching the transaction at POS an error message appears
"An error has been encountered during the search function"
Below is the screenshot of the error:

Root Cause Store was not upgraded correctly

Troubleshooting

As per POS log file of the problematic machine, below error message was logged
 <Message>Invalid object name 'vw_TrxTransaction'.</Message>
<ExceptionInfo>
<Type>System.Data.SqlClient.SqlException</Type>
<Message>Invalid object name 'vw_TrxTransaction'.</Message>
<Source>.Net SqlClient Data Provider</Source>
<TargetSite>Void OnError(System.Data.SqlClient.SqlException, Boolean, System.Action`1[System.Action])</TargetSite>

<StackTrace> at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction
It suggests that, the view named "vw_TrxTransaction" does not exist in the Store database hence causing the issue.

 Resolution

Store database must have the same schema as the Enterprise database. So for this specific instance, once the missing view named "vw_TrxTransaction" is available in the store database, issue should get fixed. In case the issue persists need to review the log file for fresh exception.

 You can use the command sp_helptext vw_TrxTransaction to check the view details (in the Enterprise Database or any other working store database) and then copy the View at the problematic store database.
 
Important: While the above instance logged the exception for a specific view "" only, It was observed, that store database was missing many other views as well; ( as compared to the schema of Enterprise database)

Cause

One of the Possible cause of such situation has been noticed an improper upgrade ( to 6.6) /initialization of store database.

 Recommendation

It is highly recommended to follow the pre- upgrade checklist if the iVend upgrade is performed by the Partner/Customer to avoid such issues.

 Key Check Point: If you have any custom views/ Stored procedure in your Enterprise database and you are re-initializing the store after that, make sure below are followed

-- Confirm the Enterprise has been upgraded successfully to 6.6
-- Before taking backup for store re-initialization, take backup of the custom stored procedures and views
-- Once done, drop them from the production database
-- Once done, take backup of store with desired setting (for store re-initialization)
-- Once backup is completed, use this to re-initialize the store
-- After store initialization, verify the schema (view/procedures) against Enterprise database
-- Once all set, then reload/recreate the existing custom views and stored procedures at HO* and store databases

 * You can restore the custom views/Stored procedure at HO just after completing the back up as well ( if required