Could not find stored procedure 'fnGetAutoID2' / Database error obtaining sequence number

This error occurs when the Yellowfin DB is missing a required Stored Procedure/Function that is used to create it's auto incrementing ID's for the DB tables.

In most cases this, occurs when a DB has been moved from one server to another, and the backup did not include the stored proc/function, or the DB user permissions have changed.

In order to resolve the issue, there are 2 things you need to do;

1. Create the stored proc/function if missing
2. Ensure the Yellowfin DB users (specified in the web.xml) has full access to execute the stored proc/function.

Before continuing with this, please ensure YF has been stopped.

For MySQL , you can run the queries below:

1. DELIMITER // CREATE FUNCTION fnGetAutoID2 (sTableName varchar(40), increment int) RETURNS int BEGIN DECLARE seqNum INT; IF sTableName IS NULL THEN RETURN NULL; END IF; IF increment IS NULL THEN RETURN NULL; END IF; UPDATE KeyGenerator SET LastSequenceNumber = LastSequenceNumber + increment WHERE TableName = sTableName; SELECT LastSequenceNumber INTO seqNum FROM KeyGenerator WHERE TableName = sTableName LIMIT 1; RETURN seqNum; END// DELIMITER ;//

1. GRANT SELECT ON MySQL.Proc to <your YF user>

For Microsoft Sql Server you can run this query;

1. CREATE PROCEDURE [dbo].[fnGetAutoID2] ( @sTableName VARCHAR(40), @increment int )
2. AS BEGIN TRAN DECLARE @seqNum INT IF @sTableName IS NULL RETURN NULL IF @increment IS NULL RETURN NULL UPDATE KeyGenerator WHERE TableName = UPPER(@sTableName) IF @@error <> 0 RETURN NULL COMMIT TRAN RETURN @seqNum
3. SET @seqNum=LastSequenceNumber=LastSequenceNumber+@increment

In Oracle and Postgres, this is stored in the SEQUENCE section:

If missing you can run the following query to generate it:

CREATE SEQUENCE nexteventsequence INCREMENT BY 1 START WITH <yourLastValueInDB> MAXVALUE 999999999999999999999999999 MINVALUE 1 NOCYCLE CACHE 20 NOORDER;