Execute a SQL Store Procedure in Script

Danny_21371
Danny_21371 Altair Community Member
edited March 18 in Community Q&A
Hi, 
I am having such a hard time trying to figure out how to connect to a SQL Server and execute a stored procedure.  
I am using Monarch Automator 15.3.  I added a Script to the Visual process but there isn't any good tutorials that I can find to do this. 

Any help is much appreciated. 

Thanks,

------------------------------
Danny Valenzuela
Senior Database Administrator

------------------------------
Tagged:

Answers

  • Mahmoud
    Mahmoud
    Altair Employee
    edited June 2019

    Hi Danny,
    Please see attached document.
    Regards
    Mo

    ------------------------------
    Mahmoud Abdolrahim
    Senior Implementation & Integration Engineer
    Datawatch Corporation
    MA
    (978) 935-3840
    ------------------------------
    -------------------------------------------
    Original Message:
    Sent: 06-01-2019 05:32 PM
    From: Danny Valenzuela
    Subject: Execute a SQL Store Procedure in Script

    Hi,
    I am having such a hard time trying to figure out how to connect to a SQL Server and execute a stored procedure.
    I am using Monarch Automator 15.3.  I added a Script to the Visual process but there isn't any good tutorials that I can find to do this.

    Any help is much appreciated.

    Thanks,

    ------------------------------
    Danny Valenzuela
    Senior Database Administrator

    ------------------------------
    "
  • Leonid
    Leonid New Altair Community Member
    edited June 2019
    Mahmoud said:

    Hi Danny,
    Please see attached document.
    Regards
    Mo

    ------------------------------
    Mahmoud Abdolrahim
    Senior Implementation & Integration Engineer
    Datawatch Corporation
    MA
    (978) 935-3840
    ------------------------------
    -------------------------------------------
    Original Message:
    Sent: 06-01-2019 05:32 PM
    From: Danny Valenzuela
    Subject: Execute a SQL Store Procedure in Script

    Hi,
    I am having such a hard time trying to figure out how to connect to a SQL Server and execute a stored procedure.
    I am using Monarch Automator 15.3.  I added a Script to the Visual process but there isn't any good tutorials that I can find to do this.

    Any help is much appreciated.

    Thanks,

    ------------------------------
    Danny Valenzuela
    Senior Database Administrator

    ------------------------------
    "
    Hi Mo,

    This is a very useful script that we were looking for. I have one question - instead of calling a stored procedure that has no output, can we call a SQL function (or direct Select from the table) that will return exactly one value - "select dbo.f_getBusinessDate()" and stored it in the Automator global variable. Then we can use this variable throughout all processes that need to insert data into different tables using business date, rather then calendar date.

    Thank you,
    Leonid

    ------------------------------
    Leonid Shteyngardt
    App Dev Manager
    Canaccord Genuity Inc.
    New York NY
    (212) 389-8176
    ------------------------------
    -------------------------------------------
    Original Message:
    Sent: 06-02-2019 09:23 PM
    From: Mahmoud Abdolrahim
    Subject: Execute a SQL Store Procedure in Script

    Hi Danny,
    Please see attached document.
    Regards
    Mo

    ------------------------------
    Mahmoud Abdolrahim
    Senior Implementation & Integration Engineer
    Datawatch Corporation
    MA
    (978) 935-3840
    ------------------------------

    Original Message:
    Sent: 06-01-2019 05:32 PM
    From: Danny Valenzuela
    Subject: Execute a SQL Store Procedure in Script

    Hi,
    I am having such a hard time trying to figure out how to connect to a SQL Server and execute a stored procedure.
    I am using Monarch Automator 15.3.  I added a Script to the Visual process but there isn't any good tutorials that I can find to do this.

    Any help is much appreciated.

    Thanks,

    ------------------------------
    Danny Valenzuela
    Senior Database Administrator

    ------------------------------
    "
  • Mahmoud
    Mahmoud
    Altair Employee
    edited June 2019
    Leonid said:

    Hi Mo,

    This is a very useful script that we were looking for. I have one question - instead of calling a stored procedure that has no output, can we call a SQL function (or direct Select from the table) that will return exactly one value - "select dbo.f_getBusinessDate()" and stored it in the Automator global variable. Then we can use this variable throughout all processes that need to insert data into different tables using business date, rather then calendar date.

    Thank you,
    Leonid

    ------------------------------
    Leonid Shteyngardt
    App Dev Manager
    Canaccord Genuity Inc.
    New York NY
    (212) 389-8176
    ------------------------------
    -------------------------------------------
    Original Message:
    Sent: 06-02-2019 09:23 PM
    From: Mahmoud Abdolrahim
    Subject: Execute a SQL Store Procedure in Script

    Hi Danny,
    Please see attached document.
    Regards
    Mo

    ------------------------------
    Mahmoud Abdolrahim
    Senior Implementation & Integration Engineer
    Datawatch Corporation
    MA
    (978) 935-3840
    ------------------------------

    Original Message:
    Sent: 06-01-2019 05:32 PM
    From: Danny Valenzuela
    Subject: Execute a SQL Store Procedure in Script

    Hi,
    I am having such a hard time trying to figure out how to connect to a SQL Server and execute a stored procedure.
    I am using Monarch Automator 15.3.  I added a Script to the Visual process but there isn't any good tutorials that I can find to do this.

    Any help is much appreciated.

    Thanks,

    ------------------------------
    Danny Valenzuela
    Senior Database Administrator

    ------------------------------
    "
    Hi Leonid,
    Attached document provides an example of executing a TSQL function. 
    In this example I used one of the Monarch Server Automator User Defined functions, which returns the current Automator version number, and assign it to a global variable.

    Regards
    Mo

    ------------------------------
    Mahmoud Abdolrahim
    Senior Implementation & Integration Engineer
    Datawatch Corporation
    MA
    (978) 935-3840
    ------------------------------
    -------------------------------------------
    Original Message:
    Sent: 06-06-2019 07:34 AM
    From: Leonid Shteyngardt
    Subject: Execute a SQL Store Procedure in Script

    Hi Mo,

    This is a very useful script that we were looking for. I have one question - instead of calling a stored procedure that has no output, can we call a SQL function (or direct Select from the table) that will return exactly one value - "select dbo.f_getBusinessDate()" and stored it in the Automator global variable. Then we can use this variable throughout all processes that need to insert data into different tables using business date, rather then calendar date.

    Thank you,
    Leonid

    ------------------------------
    Leonid Shteyngardt
    App Dev Manager
    Canaccord Genuity Inc.
    New York NY
    (212) 389-8176
    ------------------------------

    Original Message:
    Sent: 06-02-2019 09:23 PM
    From: Mahmoud Abdolrahim
    Subject: Execute a SQL Store Procedure in Script

    Hi Danny,
    Please see attached document.
    Regards
    Mo

    ------------------------------
    Mahmoud Abdolrahim
    Senior Implementation & Integration Engineer
    Datawatch Corporation
    MA
    (978) 935-3840

    Original Message:
    Sent: 06-01-2019 05:32 PM
    From: Danny Valenzuela
    Subject: Execute a SQL Store Procedure in Script

    Hi,
    I am having such a hard time trying to figure out how to connect to a SQL Server and execute a stored procedure.
    I am using Monarch Automator 15.3.  I added a Script to the Visual process but there isn't any good tutorials that I can find to do this.

    Any help is much appreciated.

    Thanks,

    ------------------------------
    Danny Valenzuela
    Senior Database Administrator

    ------------------------------
    "
  • Leonid
    Leonid New Altair Community Member
    edited June 2019
    Mahmoud said:

    Hi Leonid,
    Attached document provides an example of executing a TSQL function. 
    In this example I used one of the Monarch Server Automator User Defined functions, which returns the current Automator version number, and assign it to a global variable.

    Regards
    Mo

    ------------------------------
    Mahmoud Abdolrahim
    Senior Implementation & Integration Engineer
    Datawatch Corporation
    MA
    (978) 935-3840
    ------------------------------
    -------------------------------------------
    Original Message:
    Sent: 06-06-2019 07:34 AM
    From: Leonid Shteyngardt
    Subject: Execute a SQL Store Procedure in Script

    Hi Mo,

    This is a very useful script that we were looking for. I have one question - instead of calling a stored procedure that has no output, can we call a SQL function (or direct Select from the table) that will return exactly one value - "select dbo.f_getBusinessDate()" and stored it in the Automator global variable. Then we can use this variable throughout all processes that need to insert data into different tables using business date, rather then calendar date.

    Thank you,
    Leonid

    ------------------------------
    Leonid Shteyngardt
    App Dev Manager
    Canaccord Genuity Inc.
    New York NY
    (212) 389-8176
    ------------------------------

    Original Message:
    Sent: 06-02-2019 09:23 PM
    From: Mahmoud Abdolrahim
    Subject: Execute a SQL Store Procedure in Script

    Hi Danny,
    Please see attached document.
    Regards
    Mo

    ------------------------------
    Mahmoud Abdolrahim
    Senior Implementation & Integration Engineer
    Datawatch Corporation
    MA
    (978) 935-3840

    Original Message:
    Sent: 06-01-2019 05:32 PM
    From: Danny Valenzuela
    Subject: Execute a SQL Store Procedure in Script

    Hi,
    I am having such a hard time trying to figure out how to connect to a SQL Server and execute a stored procedure.
    I am using Monarch Automator 15.3.  I added a Script to the Visual process but there isn't any good tutorials that I can find to do this.

    Any help is much appreciated.

    Thanks,

    ------------------------------
    Danny Valenzuela
    Senior Database Administrator

    ------------------------------
    "
    Hi Mo,

    This is perfect!

    Thank you so much for sharing these scripts!

    Leonid

    ------------------------------
    Leonid Shteyngardt
    App Dev Manager
    Canaccord Genuity Inc.
    New York NY
    (212) 389-8176
    ------------------------------
    -------------------------------------------
    Original Message:
    Sent: 06-07-2019 11:29 AM
    From: Mahmoud Abdolrahim
    Subject: Execute a SQL Store Procedure in Script

    Hi Leonid,
    Attached document provides an example of executing a TSQL function.
    In this example I used one of the Monarch Server Automator User Defined functions, which returns the current Automator version number, and assign it to a global variable.

    Regards
    Mo

    ------------------------------
    Mahmoud Abdolrahim
    Senior Implementation & Integration Engineer
    Datawatch Corporation
    MA
    (978) 935-3840
    ------------------------------

    Original Message:
    Sent: 06-06-2019 07:34 AM
    From: Leonid Shteyngardt
    Subject: Execute a SQL Store Procedure in Script

    Hi Mo,

    This is a very useful script that we were looking for. I have one question - instead of calling a stored procedure that has no output, can we call a SQL function (or direct Select from the table) that will return exactly one value - "select dbo.f_getBusinessDate()" and stored it in the Automator global variable. Then we can use this variable throughout all processes that need to insert data into different tables using business date, rather then calendar date.

    Thank you,
    Leonid

    ------------------------------
    Leonid Shteyngardt
    App Dev Manager
    Canaccord Genuity Inc.
    New York NY
    (212) 389-8176

    Original Message:
    Sent: 06-02-2019 09:23 PM
    From: Mahmoud Abdolrahim
    Subject: Execute a SQL Store Procedure in Script

    Hi Danny,
    Please see attached document.
    Regards
    Mo

    ------------------------------
    Mahmoud Abdolrahim
    Senior Implementation & Integration Engineer
    Datawatch Corporation
    MA
    (978) 935-3840

    Original Message:
    Sent: 06-01-2019 05:32 PM
    From: Danny Valenzuela
    Subject: Execute a SQL Store Procedure in Script

    Hi,
    I am having such a hard time trying to figure out how to connect to a SQL Server and execute a stored procedure.
    I am using Monarch Automator 15.3.  I added a Script to the Visual process but there isn't any good tutorials that I can find to do this.

    Any help is much appreciated.

    Thanks,

    ------------------------------
    Danny Valenzuela
    Senior Database Administrator

    ------------------------------
    "
  • MRyba
    MRyba Altair Community Member
    edited March 13

    How do I download or view the file How to run a Stored procedure in Automator Visual Process.docx?

  • Mahmoud
    Mahmoud
    Altair Employee
    edited March 18

    Hi Matt,

    Please see attached file.

    Mo

  • Mahmoud
    Mahmoud
    Altair Employee
    edited March 18

    If you click on the attached file it will be downloaded to the default download folder.

    Mo

Categories