Insert into SY_MenuItemReport error on upgrade to a later Version 7 release
Problem
Upgrading a version 7 database to a later release can result in errors being reported attempting into insert records into SY_MenuItemReport, usually as a result of replacing or renaming Jiwa standard report menu items. Example of an error:
You can examine the SQL script which caused the error by running the following SQL query once the error has occurred:
SELECT * FROM SY_UpgradeScripts WHERE Status = 2
In the Script column returned, you should see something like this:
INSERT INTO SY_MenuItemReport (RecID, SY_MenuItem_RecID, SY_Report_RecID, LastSavedDateTime) SELECT '9aa2d864-b096-48d7-a4f0-f062bbb8b1c1', '9aa2d864-b096-48d7-a4f0-f062bbb8b1c1', (SELECT TOP 1 RecID FROM SY_Report WHERE FileName = 'DBMGT050 - Debtors Sales By Staff Member.rpt'), '2016-11-03 16:33:44'
This is the script which is failing.
Cause
There are 2 possible causes - which is the cause depends on the error message being reported.
If the error message was "Cannot insert the value NULL Into column SY_Report_RecID":
The cause is the filename of a standard report has been changed, or the report delete. As of 07.00.159.00 you are no longer able to change or delete standard reports in an effort to prevent this issue.
- DEV-3284Getting issue details... STATUS
As of 07.00.179.00 reslience has been added to the upgrade process to cater for deleted or renamed reports. - DEV-5816Getting issue details... STATUS
Solution
To correct the issue, follow these steps:
- Restore the database from backup
- Locate the report causing the issue in the Report Maintenance form and rename it back to be the standard report name - or if it was deleted, import the report from the local file system (%ProgramData%\Jiwa Financials\Jiwa 7\ReportCache).
- Backup the database
- Re-start the upgrade
OR
Leaving the database in the errored state, run the following query AFTER Replacing the text <INSERT FILENAME HERE> with the filename of the report which has been deleted or renamed - We showed in the problem description earlier how to determine the filename. After running the script, resume the upgrade
INSERT INTO SY_Report(RecID, FileName, Title, Description, Author, SY_ReportSection_RecID, ReportType, IsEnabled, FileBinary, FileHash, Comment, ItemNo, LastSavedDateTime, IsReadOnly) SELECT NewID(), '<INSERT FILENAME HERE>', 'Dummy Title', '', 'Jiwa Financials', (SELECT TOP 1 RecID FROM SY_ReportSection), 1, 1, dbo.udf_JIWA_Base64ToBinary(''), '', 'Dummy Comment', (SELECT COALESCE(MAX(ItemNo), 0) + 1 FROM SY_Report), GETDATE(), 1
If the error message was "Cannot insert duplicate key in object 'dbo.SY_Report':
This happens if the Jiwa 7 Default Menu has been altered in the Jiwa 7 version before:
Solution
- Copy the Jiwa default menu
- Alter each staff user group to use the copied menu
- After each upgrade, if desired, merge the Jiwa default menu with their copied menu to make new menu items appear on their custom menu.
- To fix the upgrade error you need to identify the duplicate keys in the table SY_Reports and then delete the duplicate key entries
Related articles