calling sql sequence in Thingworx

[CS_9939070]

I have a stored procedure that contains an insert statement using "NEXT VALUE FOR" to use a sequence. The stored procedure works fine in SSMS, but fails to execute when executed from a DB Thing. My DB Service is a SQL Query and uses "EXECUTE". If I remove "NEXT VALUE FOR" from my stored procedure and just hard code a value it all works.

Is there some trick to get my stored procedure to be able to use the "NEXT VALUE FOR"?

Re:  post

[Tudor · Accepted solution]

Hello, thank you for the question.  Our JDBC statements are prepared and sent to the DB using a syntax that prevents 'next value for' from being utilized due to restrictions on where this function can appear -- we unfortunately cannot adjust or bypass this mechanism.

One possible workaround is to SELECT the current sequence value (and increment appropriately):

SELECT current_value FROM sys.sequences WHERE name = '<sequence name>';

There are other approaches as well such as writing the value in a table to be read later, or relying on auto sequence increments to determine the next row in the sequence.

Please let us know if the information above gives you a path forward.