Insert into SY_MenuItemReport error on upgrade to a later Version 7 release

Owned by Beth Blok (Unlicensed)
Last updated: Nov 21, 2022 by Mike SheenVersion comment
3 min read

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-3284 - Getting 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-5816 - Getting 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:

 

You shouldn't really be altering the Jiwa default menu - every upgrade we remove all entries from the default menu and re-create it - we have to do this so that new menu options will always appear on the default menu when they are introduced with a new release.
We didn't foresee any problems with customers modifying the default menu - at worst they would lose any customisations each upgrade - but the foreign key constraint issue was not foreseen.

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