With more and more customers and partners upgrading their Business Portal environments to Dynamics GP 2010 and Dynamics GP 2013, we've seen a handful of cases with this smGetNextNoteIndex procedure listed in the BPSQL.log file with various errors such as:

~~Conversion failed when converting the varchar value 'T 03' to data type smallint.

~~Cannot insert the value NULL into column 'From_Date', table 'xxxxx.dbo.GL00200'; column does not allow nulls. INSERT fails.

 

What the installation/upgrade of Business Portal is running, before this error occurs, is the following script:

        SELECT Distinct BUDGETID FROM GL00201
        WHERE BUDGETID NOT IN (SELECT BUDGETID FROM GL00200)

 

There normally shouldn't be any records in the GL00201 table that are not in the GL00200 table, as well as we shouldn't have any records in the GL00201 table where the BUDGETID column value is blank.

It is because that we have records in the GL00201 that are not in the GL00200 table for the company database that is failing with this procedure, or GL00201 records that have a blank BUDGETID column value, that cause this error and the installation/upgrade to fail.

 

Before running an installation/upgrade to Business Portal 5.x for Dynamics GP 2010 or Business Portal 6.0 for Dynamics GP 2013, or after getting this smGetNextNoteIndex procedure error during the installation/upgrade of Business Portal, the next step would be to run the following scripts against the failing company database(s):

a. SELECT Distinct BUDGETID FROM GL00201 WHERE BUDGETID NOT IN (SELECT BUDGETID FROM GL00200)

b. SELECT * FROM GL00201 WHERE BUDGETID = ''

 

If any records are returned by script a, we would need to verify whether they are valid records or not and if not, remove them from the GL00201 table. If they are valid records, we need to find out why those record's BUDGETID is not in the GL00200 table.

If any records are returned by script b, we would want to either remove the invalid records or find out why the record doesn't have a BUDGETID value and resolve that before beginning or continuing with the installation or upgrade of Business Portal.

Once both scripts a and b return no records at all, we should be able to get past this smGetNextNoteIndex procedure error for Business Portal.

Hopefully this will help if anyone runs into this type of error message with Business Portal.

Thank you!