DSXchange

Hello,

I have a requirement to generate a unique Sequence number. We currently have a state file doing this but we need to be able to fetch it from a database instead. Our Database is MSSQL Server where there is no availability of a SEQUENCE object. I have therefore written a stored procedure to update a table with a single BIGINT column and return the value.
update dbo.ID
set id=id+1
OUTPUT INSERTED.*

When I execute this in the Management Studio as exec proc1, I get the desired results that is every time it is invoked the id is incremented with 1.
However when I try to call this procedure from Datastage either in a Stored Procedure stage or in an ODBC stage, the value fetched is not a unique value implying that the update was not executed. Any ideas as to what maybe going wrong?

Thank You

Can you be more specific as to what "the value fetched is not a unique value" means, please?

Also, SQL Server does in fact have SEQUENCE objects unless your version is older. You could possibly also use an IDENTITY column and save all this.

What I meant to say was that every call to the procedure is meant to generate a new number and return it whereas when I was invoking it from datastage the number being returned was a duplicate which is not possible unless the procedure was not invoked. Yes Im using an older version that does not have the Sequence object. This problem was however resolved by invoking the stored procedure from an odbc stage and setting the array size and record count values to 1.