Cannot insert the value NULL into column 'ACTINDX', table 'tembdb.dbo.##XXXXXXX', column does not allow nulls. INSERT fails.

(0) Share

Report

Posted on by Kimberley Stevens

720

Does anyone know which tables the Account Transactions List on the Financial Navigation Pane pulls from? When clicking on this report, we are receiving the error, "Cannot insert the value NULL into column 'ACTINDX', table 'tembdb.dbo.##XXXXXXX', column does not allow nulls. INSERT fails."

In addition to eyeballing all segments, accounts, etc from both the front end and the back end, I've also tried the following queries. None of them result in any data:

SELECT * FROM GL10001 WHERE ACTINDX NOT IN (SELECT ACTINDX FROM GL00100)

SELECT * FROM GL20000 WHERE ACTINDX NOT IN (SELECT ACTINDX FROM GL00100)

SELECT * FROM GL30000 WHERE ACTINDX NOT IN (SELECT ACTINDX FROM GL00100)

SELECT * FROM GL10001 WHERE ACTINDX = ''

SELECT * FROM GL20000 WHERE ACTINDX = ''

SELECT * FROM GL30000 WHERE ACTINDX = ''

SELECT * FROM GL10001 WHERE ACTINDX = '0'

SELECT * FROM GL20000 WHERE ACTINDX = '0'

SELECT * FROM GL30000 WHERE ACTINDX = '0'

SELECT * FROM GL10001 WHERE ACTINDX NOT IN (SELECT ACTINDX FROM GL00105)

SELECT * FROM GL20000 WHERE ACTINDX NOT IN (SELECT ACTINDX FROM GL00105)

SELECT * FROM GL30000 WHERE ACTINDX NOT IN (SELECT ACTINDX FROM GL00105)

I can't figure out where else to look!

I've also run Check Links and it doesn't result in any errors.

Thanks!

Kimberley

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (3)

Verified answer

Community Member on at

Like (0)

Report

Hi,

if you can run profiler trace, you will be able to find the query which is causing the error, so it would be easy to figure it out.

to run trace, you need to open SSMS

1. go to tools menu and open SQL profiler

2.in Trace properties window, select "errors and warnings", "Stored procedures" and "TSQL" and deselect all the other options,

3. click on Run.

then verify the error.

Regards,

SantoshG

Was this reply helpful? Yes No
Kimberley Stevens 720 on at

Like (0)

Report

Thank you very much for your help! It still required a fair amount of backtracking, but I was eventually able to find the problem records this way. Both are quick journal entries. One had a record in GL10100, but nothing in GL10101 and the other was the opposite scenario.

I was able to find and delete one from the front end, although it took two tries. After I deleted it the first time and re-ran Check Links, it then noticed the problem and added the missing header record. Then I still had to go back and delete it a second time in order to accurately remove it from both tables.

The second record is not being as cooperative. This is a record that has been posted and voided, so I cannot do anything from the front end. The entry exists in GL20000 already, but the header record still exists in GL10100. This is resulting in one line showing up in the glWorkTransactions view with an ACTINDX of NULL and a SEQNUMBR of NULL. I can't figure out what was wrong with the entry in the first place, or why I still have a record in GL10100. The record in GL10100 shows an ERRSTATE value of 20048. Can anyone provide insight on this?

Was this reply helpful? Yes No
Verified answer

Heather Roggeveen 9,146 on at

Like (0)

Report

You may have had an interrupted posting that has created the orphan record. If everything is where is should be in the 20000 range tables, then you may just have to delete that line from the GL10100 table. Do a backup of the full database or just that table first.

Was this reply helpful? Yes No
Verified answer

soma 24,410 on at

Like (0)

Report

Heather is absolutely correct. The records exist in the table GL10100 had an interrupted posting records. So, check if all the entries are correct in your GL open table GL20000. If everything is correct then delete the records from GL10100 table. Because, after posted and closed the quick journal entry window, the posted transactions will move from GL10100 table to GL20000 table.

Note: Before deleting records from GL10100 table, make sure you have a latest backup for company database.

Hope this helps!!!

Was this reply helpful? Yes No
Kimberley Stevens 720 on at

Like (0)

Report

Deleted the GL10100 record with no ill effects. Check Links runs okay, and so does the Trial Balance in the Navigation Pane.

This user is having lots of issues with connectivity, and we're not sure why yet.

Thanks for your assistance!

Was this reply helpful? Yes No