Our SQL statements sent to the database are wrapped in an sp_prepexec transaction. From the context of this sp_prepexec transaction the DB won't allow us to invoke any nextval lookups. Thus, we're unable to get the value of the next sequence using our DB connector inside ThingWorx.
As a workaround, we can create a stored procedure on the DB to get the current value. Then we can invoke this stored procedure from ThingWorx to return the current value of the sequence. We'll need to increment this appropriately if we need to use this in an insert statement, but it's probably best if we let the DB auto-increment inserts as needed:
--Stored Procedure on the DB:
CREATE PROCEDURE [current_seq_val]
AS
BEGIN
SET NOCOUNT ON;
SELECT CAST(current_value AS int) FROM sys.sequences WHERE name = '<sequenceName>';
END
GO
Used in a ThingWorx service (this gives the current value, not the next value and does not increment the sequence):
EXEC [current_seq_val]
