Hi,
Has anyone seen this?
In my app i need a unique number, for example an invoice number.
I have an AutoNumber file (filename: BAC5103), with field FNR:NR (decimal 7.0) holding these unique numbers.
In my program i use Access:File.Insert to get the next number.
This has been working for years in TPS files, never had a problem.
In MSSQL i had to add some code to get the number in the buffer, which i added in the DCT triggers After Insert.
COMPILE(‘SQL’,MSSQL)
CLEAR(FNR:RECORD,1)
SET(FNR:FNR_NR_KEY)
PREVIOUS(BAC5103)
!SQL
This is working okay!
Now i had already some users that told me the number add’s up 1000, for instance it goes from 200406 to 201406.
When it goes wrong it mostly adds 1000, but that’s not always.
It happens rarely but has anybody seen this behavior, or what could be the problem?
I am using mssql and do not have problems. Clarion version is slightly different though. Maybe you could post a clarion definition of the BAC5103. The only thing that I may think is to check the order directly in sql. Check if the clarion key is transformed in the correct sql index. I think there exists sql command WITH, to force the usage of a particular index, so you might want to check if the order in sql respect that index is what you think it is.
hth
The definition of BAC5103 is simple (here the SQL version):
BAC5103 FILE,DRIVER(‘MSSQL’,’/AUTOINC=SELECT SCOPE_IDENTITY() /MULTIPLEACTIVERE’ & |
‘SULTSETS=TRUE’),PRE(FNR),BINDABLE,CREATE,THREAD,NAME(‘dbo.BAC5103’),OWNER(GLO:dbOwner)
FNR_NR_KEY KEY(FNR:NR),NOCASE,PRIMARY,NAME(‘FNR_FNR_NR_KEY’) !!
Record RECORD,PRE()
NR DECIMAL(7),NAME(‘FNR_NR’)
TXT STRING(1),NAME(‘FNR_TXT’)
END
END
I use CapeSoft FM3 and MutliProject to have one DCT and one APP (multi-dll) for both TPS and SQL versions, that works great.
When i look directly in SQL the order is okay, it just jumps up 1000 at a certain point.
I have to go now but I can just paste fast what are my tables look like.
The driver option is this one
/AUTOINCUSESSCOPEIDENTITY=TRUE
The primary key has autonumber off
The column making the primary key, in your case NR, has these options
The above are not options on key but on the column
hth
The issue is not with your app, it is the result of a default configuration in MSSQL where the database server caches 1000 values at a time (for data type int) to speed up the assignment of identity values. The cache can be disabled but there will be a slight performance hit.
