Cannot insert the value NULL into column ‘SEQNUMBR’, table dbo.IV10400, column does not allow nulls. UPDATE fails.

 

Today, one of my customers reported the below error message whenever they try to post any kind of inventory transactions:

Microsoft SQL Native Client SQL Server Cannot insert the value NULL into column ‘SEQNUMBR’, table dbo.IV10400, column does not allow nulls. UPDATE fails.

Nothing modified at their database and the error start appearing suddenly, they are running the latest service pack for GP and running SQL Server 2005 with the latest service pack.

They followed all the known procedures trying to figure out what’s the reason behind this, but unfortunately non of the procedures helped. Worth to mention that the transaction posts properly and does not actually has any obvious issues.

I ran an SQL Server Profiler trace and tried to locate the actual reason behind the issue and noticed the problem in the Extended Pricing module.

The issue is that the customer has 50 concurrent users working on the application and they have big number of transactions daily, which lead to the fact that IV10400 table is no longer accepting new entries, the table contains a field called “SEQNUMBR” which holds the sequence number for transaction and increased by 16XXX each time a new record created.

The maximum number returned for SEQNUMBR returned was 22,000,000 which is too large to be handled by an integer data type, I had to modify the table structure for IV10400 to replace the integer data type with bigint and to update a stored procedure called “sopExtPriceBookSetup” to replace the declaration of integer variables into bigint’s, if you need help in replacing this, just let me know and I will send you the stored procedure I already modified.

Enjoy!

UPDATE: Before applying modifications on the database, I checked the linked table in dexterity and noticed that the Sequence Number is defined as long integer, that’s why I posted the article, but my customer informed me that this did not work as expected and they gets additional errors! I check the maximum number they achieved after 3 working years in the table and it was 2,700,000,000! It works as all sequence numbers in Dynamics GP by multiplying 16384 which allows only 131072 records in the table as the maximum allowed length for long integer is an integral number in the range [–2,147,483,648 to 2,147,483,647].

Therefore, I had to re-index the table to reduce the values by exporting table data to an excel sheet, renumber the SEQNUMBR field and return the data back to GP.

Regards,
--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
www.mohdaoud.com