stored procedure

[luwenya]

how do I run stored procedure in a mssql service?

[paic]

I believe in SQL Server you use: Execute [name of the Procedure]

[luwenya]

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,

@Identity int

EXEC

@return_value = .[CreateOpportunity]

@SiteName = N'StoredProcedure run 2',

@Identity = @Identity OUTPUT

SELECT @Identity as N'@Identity'

[adam11]

Hi Luwen,

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.

Thanks,

Adam

[luwenya]

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

[paic]

did you try execute vs. exec? and then the name of the procedure?

[luwenya]

I tried the execute, it gives me:
Unable to Invoke Service runCreate on MsSQLServices : The statement did not return a result set.

it should result one column and one row. I tested within the ms studio. it works. can you suggest anything I can try?

[luwenya]

just to note: I just checked the database, event though it says failed, but records are created in my database. all I need is to get the returned data back.

[abalousek]

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

EXEC [dbo].[CreateOpportunity]

@SiteName = N'StoredProcedure run 2'

Please let us know when you have time to meet.

[luwenya]

is it possible to have a quick meeting this afternoon? I am available any time. sorry to push it, I have a demo on Monday morning at 8am. I am trying to get through this since it drive how UI behaves. Thank you!

[sdas1]

Hello,

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.

Thanks,

Shashank

[wposner-2]

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:

EXEC [dbo].[insertRegistration_SP]

@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.

Thanks!

Wayen