‘Invalid length parameter passed to the RIGHT function’ error when selecting categories

Issue

When selecting categories from the right click menu, you receive the following error:

Image 1 invalid right function 1

Gateway encountered a problem while attempting to connect to this database. Invalid length parameter passed to the RIGHT function.

Cause

Invalid category data in the categories list saved in the database.

Solution

To fix the error, follow these steps below:

  1. Backup your database. Go to Tools, Database Utilities, then Backup Wizard.
    Image 2 BackWiz
  2. When complete, go to Tools, Database Utilities, then select Execute SQLImage 3 ExecuteSQL
  3. When prompted, enter the SQL Key obtained from Console Support:
    Image 4 RebuildKey
  4. There will be a confirmation window to continue, click Yes.
  5. In the Execute SQL window, copy and paste in this SQL command:
    UPDATE owners SET categories = '' WHERE id IN (SELECT id FROM owners WHERE len(CAST(categories AS nvarchar)) < 2)
    UPDATE tenants SET categories = '' WHERE id IN (SELECT id FROM tenants WHERE len(CAST(categories AS nvarchar)) < 2)
    UPDATE contacts SET categories = '' WHERE id IN (SELECT id FROM contacts WHERE len(CAST(categories AS nvarchar)) < 2)
    UPDATE sales SET categories = '' WHERE id IN (SELECT id FROM sales WHERE len(CAST(categories AS nvarchar)) < 2)
    UPDATE creditors SET categories = '' WHERE id IN (SELECT id FROM creditors WHERE len(CAST(categories AS nvarchar)) < 2)
    UPDATE banks SET categories = '' WHERE id IN (SELECT id FROM banks WHERE len(CAST(categories AS nvarchar)) < 2)
    UPDATE buildings SET categories = '' WHERE id IN (SELECT id FROM buildings WHERE len(CAST(categories AS nvarchar)) < 2)
    UPDATE actions SET categories = '' WHERE id IN (SELECT id FROM actions WHERE len(CAST(categories AS nvarchar)) < 2)
    UPDATE properties SET categories = '' WHERE id IN (SELECT id FROM properties WHERE len(CAST(categories AS nvarchar)) < 2)
  6. Click OK.

Was this article helpful?

Related Articles