sorry, I wasn't clear on my question. I meant how to run it in thingworx service using the MsSQL service.
Here is my sql to run my stored procedure:
DECLARE @return_value int,
@SiteName = N'StoredProcedure run 2',
@Identity = @Identity OUTPUT
SELECT @Identity as N'@Identity'
You are trying to execute this from ThingWorx? If so, you should include all of those statements in a stored procedure and execute it from ThingWorx. Since ThingWorx cannot guarantee the order in which commands will be executed, you should only execute one stored procedure per Service.
I just want to run my stored procedure which is already defined and running on my mssql server. I am looking for help on how to run it in ThingWorx. I tried using sql query or sql command, but it does not recognize exec command. I'd appreciate very much if you can provide some instructions or code snippets which shows how to run a stored procedure with passing parameters.Thanks,-LuWen
LuWen, let's setup a web session to review what your service looks like and the results you are getting. I think you might be over-complicating the exec statement and could try this in your SQL Query ThingWorx service with an output type of InfoTable :
SET NOCOUNT ON
@SiteName = N'StoredProcedure run 2'
Please let us know when you have time to meet.
Could you please elaborate what is the solution for this problem.
I am also getting the same error The statement did not return a result set when I am executing the Stored Procedure from the Thingworx service but that Stored Procedure is working fine from SQL management studio.
Yes...there seems to be a bad habit on this board of not following through with a completed solution. I need to do the exact same thing of returning my output ID only my problem is further complicated by the fact that I am trying to call my stored procedure with values from an infotable.
If I execute my command as such:
@username = [[values.username]],
@sabaOrderNumber = [[values.sabaOrderNumber]],
@skuOrder = [[values.skuOrder]]
where values is the name of my infotable, I get the following error:
The identifier that starts with '[values.username] @sabaOrderNumber = [[values.sabaOrderNumber], @skuOrder = [[values.skuOrder] @registrationID=2 DECLARE @retu' is too long. Maximum length is 128.
You can see that the handling of the double brackets is not consistent throughout. And if I try with single brackets. then "values.username" is exactly the value that gets set in the database. I've tried various combinations of trying to access the various properties on the values object with no success.
Hopefully someone here can shed some light on this and we can have a good referenceable answer for the community.