Prev
Next
Forums Integration / Customization IPA/ProcessFlow

Running Stored Procedure From IPA

 5 Replies
 0 Subscribed to this topic
 52 Subscribed to this forum
Sort:
Author
Messages
Rkapoor
Basic Member
Posts: 10
Basic Member
2/12/2019 2:44 PM

    I am trying to run the stored procedure using sql query node and selecting run sql procedure.

    When I click on select button, it connects me to the sql server, all I see is user ids defined in that database, I don't see the list of  Stored procedures. any suggestions why I can't see the stored procedures...? I don't have access to sql server.

    I can run the sql query on this sql server, no issue there.

    I can run the stored procedure if I select type sql query,build, other(no SELECT) type procedure name and click execute, it runs the SP on sql server.

     

    Thanks

     

     

    FireGeek21
    Veteran Member
    Posts: 84
    Veteran Member
    2/12/2019 2:52 PM
      We have a few running. In the SQL Update Node, SQL Update box put this:

      EXEC configurationVariableWhereJDBCdatabaseConnectionIsSet.[StoredProcedureName] 'InsideSingleQuotesAddVariableIfYouNeedToPassOne'

       

      Sorry, the last posts didn't work with the code block.

      JimY
      Veteran Member
      Posts: 510
      Veteran Member
      2/12/2019 3:19 PM
        I run it in a Sql Transaction Node this way.

        exec dbo.proc_Delete_POBID_Concordance

        The stored procedure is named proc_Delete_POBID_Concordance
        Rkapoor
        Basic Member
        Posts: 10
        Basic Member
        2/12/2019 4:04 PM

          ok great....Thanks for the quick replies...:-)

          I have tried it sql transaction node - I got success in log file - just waiting confirmation from the DBA.

          will let you know....thanks again for your valuable input....

          JimY
          Veteran Member
          Posts: 510
          Veteran Member
          2/12/2019 5:10 PM

            I forgot to mention that you could also use a SysCmd Node if you use BCP to run a stored procedure that does a query and you want the output to go to a file. Below is what we have used. Parameters are with in the single quotes.  For some reason I cannot get the code to paste in correctly

             

             

             

             

             

             

             

             

            <!--S3SQLServerPass-->

             

            <!--S3SQLServerPass-->

            Rkapoor
            Basic Member
            Posts: 10
            Basic Member
            2/13/2019 4:06 PM
              YES....It worked... DBA has confirmed that SP ran on sql server successfully.
              Thanks a lot Jim and FireGeek21...:-)

              I will check out with DBA if I can test BCP from Syscmd node, I do use syscmd node very commonly in lots of PFs to run shell scripts, and large sql queries from shell scripts to dump the data in files.
              This is first time ever I used had to use sql nodes...