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!

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