Skip to main content
L
luwenya1-Visitor
1-Visitor
February 21, 2014
Question

stored procedure

  • February 21, 2014
  • 3 replies
  • 4549 views

how do I run stored procedure in a mssql service?

      3 replies

      P
      paic1-Visitor
      1-Visitor
      February 21, 2014

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

      L
      luwenya1-VisitorAuthor
      1-Visitor
      February 21, 2014

      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'



      A
      adamtrainer5-Regular Member
      5-Regular Member
      February 21, 2014

      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



      L
      luwenya1-VisitorAuthor
      1-Visitor
      February 21, 2014

      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

      P
      paic1-Visitor
      1-Visitor
      February 21, 2014

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

      S
      sdas11-Visitor
      1-Visitor
      April 6, 2016

      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

        W
        wposner-21-Visitor
        1-Visitor
        April 14, 2016

        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

        Terms & ConditionsAccessibility statement