Skip to main content
P
psskps7-Bedrock
7-Bedrock
July 13, 2020
Question

calling sql sequence in Thingworx

  • July 13, 2020
  • 2 replies
  • 3025 views

Hi,

 

I am trying to call a Sql sequence in Thingworx . i wrote a sql query service in a thing and tried executing it. But it shows the following error "Execute Query failed: com.microsoft.sqlserver.jdbc.SQLServerException: NEXT VALUE FOR function cannot be used if ROWCOUNT option has been set, or the query contains TOP or OFFSET." 

 

how to resolve this?

 

Thanks in Advance

Sadagopan P

2 replies

W
wposner-21-Visitor
1-Visitor
July 13, 2020

This is not a TWX issue.  I'd suggest reading up on proper SQL query implementation.

https://docs.microsoft.com/en-us/sql/t-sql/functions/next-value-for-transact-sql?view=sql-server-ver15

 

If you tried executing your query directly in SQL Manager, you'd get the same error.

P
psskps7-BedrockAuthor
7-Bedrock
July 16, 2020

I tried in the SQL manager, and actually am not getting that error which i get when i execute the same in thingworx

P
psskps7-BedrockAuthor
7-Bedrock
July 22, 2020

Hi,

 

Instead of executing the sequence as an query separately, i added ' next value for "sequence name" ' in the insert command values itself. like, insert into tablename (id) values (next value for sequenceName) . This solved kind of solved the aforementioned issue.

 

But, I have another similar issue here.

 

When i execute a stored procedure in thingworx as SQL query am getting that same old error "NEXT VALUE FOR function cannot be used if ROWCOUNT option has been set, or the query contains TOP or OFFSET." i tried executing this specific procedure in sql directly, it works fine there. But while executing it in Thingworx am getting this error. 

 

Can I get some help regarding this?

 

Thanks,

Sadagopan Ponmani.

 

 

T
Tudor14-Alexandrite
14-Alexandrite
February 22, 2021

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]

Terms & ConditionsAccessibility statement