11 Replies Latest reply on Apr 14, 2016 4:32 PM by wposner-2 RSS
    luwenya Explorer

    stored procedure

    how do I run stored procedure in a mssql service?

      • stored procedure
        paic Collaborator

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

         

          • stored procedure
            luwenya Explorer

            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'



          • stored procedure
            adam Creator

            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



              • stored procedure
                luwenya Explorer

                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

                  • stored procedure
                    paic Collaborator

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

                     

                      • stored procedure
                        luwenya Explorer

                        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?



                        • stored procedure
                          luwenya Explorer

                          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.

                           

                        • stored procedure
                          andyb Explorer

                          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.



                            • stored procedure
                              luwenya Explorer

                              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!

                               

                        • Re: stored procedure
                          sdas Newbie

                          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

                            • Re: stored procedure
                              wposner-2 Apprentice

                              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