1. Home
  2. Support
Article - CS393833

Error "SQLServerException: NEXT VALUE FOR function cannot be used if ROWCOUNT option has been set, or the query contains TOP or OFFSET." is returned when executing a service on SQLServer in ThingWorx

Modified: 06-Mar-2024   


Applies To

  • ThingWorx Platform 9.3

Description

  • Calling SQL sequence in ThingWorx selects last value but is not incrementing the number
  • Following errors are being returned when executing service
    • in Composer
    • Unable to Invoke Service execSP on SQLServerThing : Execute Query failed. Please check Database logs for more details.
    •  in DatabaseLog.log
    • [L: ERROR] [O: D.c.t.t.d.QueryHandler] [I: ] [U: Administrator] [S: ] [P: ] [T: https-openssl-nio-443-exec-9] Unable to Invoke Service execSP on SQLServerThing : 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.
  • A SQLServer database Thing is created with a service
  • The service calls a stored procedure using the NEXT VALUE clause, for example
create   PROCEDURE [dbo].[NextValSP]


AS
BEGIN
SET NOCOUNT ON

select NEXT VALUE FOR dbo.MySequence;

END
GO



 
This is a printer-friendly version of Article 393833 and may be out of date. For the latest version click CS393833