[Solved] MSSQL confused with BOTH System.IDGEN-and- Sequence

Jaymer

On a MSSQL Install, do I have to do something to make it use the new SEQUENCE for ID#s?

On Bruce's install, he doesn't have a System.BAS_IDGEN table. Doesn't exist. He does however have dbo.BAS_IDGEN_SEQ.

On a new server install, I have found myself with BOTH files.
Even though I have the Sequence (its still at 1 if I examine Properties) its not being used.
I first noticed it after I converted a lot of legacy data in (using their unique ID #s, which started at 1) and tried to add a record.

It was a simple "Duplicate Key" error msg saying ID # nnn already existed.
So I went and changed the Sequence to 1,000,000. Got the same error.
Not until I edited IDGEN did it start working.

I tried to delete IDGEN and thought Aware would use the sequence...
But I got this error: Internal error. Unable to generate id for the new object due to the following exception Invalid object name 'BAS_IDGEN'.

ideas?

Jaymer

bump - still stuck

bssxfire8

Jaymer did you test the BSV clean without any data from the old instance?

Jaymer

sorry - I don't understand this: "did you test the BSV clean without any data from the old instance?"

bssxfire8

You said you noticed the issue "after I converted a lot of legacy data in"
My question was did you have any erroneous behavior when there was no data at all?

Jaymer

What I meant was that I had a bunch of data from an old database. I converted it over using SQL inserts.
And used their unique row ID numbers.
I wasn’t needing aware to generate any ID GEN numbers.
And then I made maintenance screens and browse grids etc.
And then at some point in there I got a collision, because it was using BAS ID Gen, which was still really low.
And it collided with these lower ID numbers from the conversion.
This was my first install of 8.3 that used sequences.
And that’s my question, I don’t know how I’m supposed to activate the sequences, instead of IDGEN.

bssxfire8

In my experience with the sequences, there wasn't anything that needed to be done to activate them. If your sql server hadn't been used for Aware at all before your 8.3 install then I'm not sure why the ID Gen table is there at all.

I had collision issues the first two times I upgraded legacy apps to 8.3.
The first time the data was unimportant so I just started over.
The second time I had to write a few stored procedures importing all my data into temporary tables and then importing them all into their correct tables using, "NEXT VALUE FOR dbo.BAS_IDGEN_SEQ" to insert the record with new ID's using the sequence. I had to deal with the changes in all the reference fields I had within those SP's too.

One way to test the sequence before you deal with the legacy data would be to create a new record through Aware, get into your sql server and create another new record using this as a template:
INSERT INTO xxxxxxxx (id, ........
VALUE (NEXT VALUE FOR dbo.BAS_IDGEN_SEQ, ........
if it comes up with the next id you are expecting then your sequence is working and you'll probably have to recreate all the ID's for the records.
Just FYI, I'm setup with different databases per BSV so if you're just using the default database there may be different results.

Jaymer

BSS
I just did an initial install on a new Upcloud server on Sept 7. So that was 8.3 from the start.
Support requested this version info today in an attempt to find out why Sequences are not being used.
From SQL, I have used the NEXT VALUE FROM sequence and it works great.

This was from doing a SELECT @@VERSION on my MS SQL box:

Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)   Aug 22 2017 17:04:49   Copyright (C) 2017 Microsoft Corporation  Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

Just did a simple test from a Maintenance program on a table.
Added a new rec and Aware definitely got a new value from AIM.BAS_IDGEN.
SELECT [MAX_ID] FROM [AIM].[dbo].[BAS_IDGEN]
returns 2003028, and is the ID of the newly created record.

From my Production database, this is the sequence which was NOT used: (u have to scroll down to see current value)

Jaymer

OK, here's the solution.
thanks to Support for getting this resolved.

I'm on a Upcloud Server, using MSSQL.
When you install Aware, there are several BASSERVER.props files available.
If you look at the available choices, there's only 1 that says SQL, that could work for Microsoft MS SQL (at least the way I view the choices).
As I see it, I'm not Derby, Oracle, MySQL, or Maria.
I don't know what MSDE is, so I chose AzureSQL, cause Azure is Microsoft and since you can't put punctuation in the names, I figured that was for
"Azure/MSSQL"

WRONG.
MSDE is what you use for MSSQL

So I had been using the wrong connector inside the Props file - one which seemed to work fine 99.9% of the time.
DatabaseComponent=com.bas.basserver.persistence.dbplugins.MSSQLServerInterface
is what is supposed to be used for MSSQL (makes sense!) - this is a line you can see inside your BASSERVER.props file.

So, a simple change and new records were created using the Sequence.
All is good!