Strange Problem with Identity field 1

[mSportsman]

I am having a problem where @@IDENTITY is returning null after a record has been successfully inserted into a table that contains an IDENTITY Column.
The methods I have tried always work again a test database. The same methods sometimes work against the production data, but sometimes they do not.
The Database Schema is the same for the TEST data and the production data.

Were are running PERVASIVE Version 11.30 Running on a Virtual Server ( Windows Server 2008R Standard )

Here is the table Schema:

CREATE TABLE "BFCX_NCR"(
 "ID" IDENTITY DEFAULT '0',
 "JOB" CHAR(6),
 "SUFFIX" CHAR(3),
 "SEQUENCE" CHAR(6),
 "DATE_INCIDENT" DATE,
 "EMPLOYEE" CHAR(5),
 "EMPLOYEE_NAME" CHAR(30),
 "SHIFT" CHAR(1),
 "CAUSE" CHAR(30),
 "DESC_INCIDENT" CHAR(512),
 "DISPOSITION" CHAR(20),
 "DESC_DISPOSITION" CHAR(512),
 "SUBMITTED" CHAR(5),
 "SUBMITTED_NAME" CHAR(30),
 "SUBMITTED_SHIFT" CHAR(1),
 "DATE_LAST_CHANGE" DATE,
 PRIMARY KEY ("ID"),
 UNIQUE ("ID"));

Here is the SQL Statements and the results

INSERT INTO BFCX_NCR ( ID, JOB, SUFFIX, SEQUENCE, DATE_INCIDENT, EMPLOYEE, EMPLOYEE_NAME, SHIFT, CAUSE, DESC_INCIDENT, DISPOSITION, DESC_DISPOSITION, SUBMITTED, SUBMITTED_NAME, SUBMITTED_SHIFT, DATE_LAST_CHANGE )
VALUES ( 0, '011695', 'ENG', '997000', '2017-05-04', '00678', 'JOHN DOE', '1', 'Galv/Paint','TEST XXX','NOT Provived', 'THIS IS A TEST', '00712', 'JOE BLOW', '1', '2017-05-04' );

<<<<<<<<<<<<<<<<<<<<<<<<
INSERT INTO BFCX_NCR ( ID, JOB, SUFFIX, SEQUENCE, DATE_INCIDENT, EMPLOYEE, EMPLOYEE_NAME, SHIFT, CAUSE, DESC_INCIDENT, DISPOSITION, DESC_DISPOSITION, SUBMITTED, SUBMITTED_NAME, SUBMITTED_SHIFT, DATE_LAST_CHANGE )
VALUES ( 0, '011695', 'ENG', '997000', '2017-05-04', '00678', 'BRANDI WRIGHT', '1', 'Galv/Paint','TEST XXX','NOTProvived', 'THIS IS A TEST', '00712', 'BROOKE MAUZEY', '1', '2017-05-04' )
SQL statement(script) has executed successfully.
1 row was affected.
>>>>>>>>>>>>>>>>>>>>>>>>

Then execute this immediately after:

SELECT @@IDENTITY;
<<<<<<<<<<<<<<<<<<<<<<<<
     EXPR_1
===========
     (Null)

1 row was affected.

>>>>>>>>>>>>>>>>>>>>>>>>

I have also tried this with a stored procedure as follows:

CREATE PROCEDURE "spInsertNCR" 
	( :JOB CHAR(6), :SUFFIX CHAR(3), :SEQUENCE CHAR(6), :DATE_INCIDENT DATE, :EMPLOYEE CHAR(5), :EMPLOYEE_NAME CHAR(30), :SHIFT CHAR(1), :CAUSE CHAR(30), :DESC_INCIDENT CHAR(512), :DISPOSITION CHAR(20), :DESC_DISPOSITION CHAR(512), :SUBMITTED CHAR(5), :SUBMITTED_NAME CHAR(30), :SUBMITTED_SHIFT CHAR(1), :DATE_LAST_CHANGE DATE )
 	RETURNS  (NewID INTEGER);
BEGIN
	// DECLARE :NewID INTEGER;
	INSERT INTO BFCX_NCR ( ID, JOB, SUFFIX, SEQUENCE, DATE_INCIDENT, EMPLOYEE, EMPLOYEE_NAME, SHIFT, CAUSE, DESC_INCIDENT, DISPOSITION, DESC_DISPOSITION, SUBMITTED, SUBMITTED_NAME, SUBMITTED_SHIFT, DATE_LAST_CHANGE )
	VALUES ( 0, :JOB, :SUFFIX, :SEQUENCE, :DATE_INCIDENT, :EMPLOYEE, :EMPLOYEE_NAME, :SHIFT, :CAUSE, :DESC_INCIDENT, :DISPOSITION, :DESC_DISPOSITION, :SUBMITTED, :SUBMITTED_NAME, :SUBMITTED_SHIFT, :DATE_LAST_CHANGE );
	SELECT @@IDENTITY as NewID;
END

This also has the same behavior and return null most of the time.

Any help to a solution or advice on how to trouble shoot this problem will be deeply appreciated.
Thank you,
MS

 

[mirtheil]

If it always works against the test database but fails against the production database, I would say there might be something wrong with the production database. A few questions:
1. Are the production and test databases on the same server?
2. If they aren't, are the servers running exactly the same version of PSQL?
3. What happens if you create a new database, create the table in the new database, and run the insert statement?
4. What happens if you create a new table in the production database and insert the record? Does the @@IDENTITY work?
5. As a nuclear option, you could export the data from the current production table, drop the table, re-create it, and then import the data.

I've tried it here with my v11.30 server and @@IDENTITY worked every time I tried it.

Mirtheil

http://www.mirtheil.com

 

[mSportsman]

Answer to questions:
1. Both databases are running on the same server.
3. ( I did not try this, because I got results from question #4 )
4. I created a new table in the production database, and methods of inserting record seemed to work.
5. There is another table that has a foreign key constraint to this table, so to try to fix the problem I dropped both of the tables from the database. I then recreated the tables with the same names as before. Added the constraints as before.
This did not work.

Next I tried creating the tables with different names in the same production database.... This worked..... But only a few times before it started returning null again.

My next move is .... I have asked the IT people here if they could reboot that server. They said they will over lunch break.

I will let you know what I find out after the reboot.

Thanks for all of your help...
MS

 

[mSportsman]

The reboot of the server seemed to have fixed this issue.
Any ideas on how this database may have been affected in this way?

MS

 

[mirtheil]

In all the years I've supported and worked on the Pervasive engine (since 1995), I've never seen anything like it. The only guess I have would be that somehow a cache or memory corruption occurred in the SQL engine. Restarting cleared the memory and allowed everything to work again.
If it comes back, you might want to contact Actian to see if they can offer any insight.


Mirtheil

http://www.mirtheil.com