Forum Discussion

vmanojrc30's avatar
vmanojrc30
Contributor
2 years ago

Calling DB Stored Procedures from OS

I want to execute a list of DB Stored procedures after executing a DM step in OS. Is it possible to call a DB Stored Procedure from OS?After the Stored Procedures are run, I want to execute a list of DM Jobs for data imports to cube.

Appreciate any pointers how I can implement this.

 

 

Data Integration
  • Steven's avatar
    Steven
    2 years ago

    Yes, you can call a stored procedure from OS:

    Do something like this in a business rule:

     

    Using dbConnApp = BRApi.Database.CreateApplicationDbConnInfo(si)
    BRApi.Database.ExecuteActionQuery(dbConnApp, "EXEC [StoredProc]", True, True)
    End Using

  • Steven's avatar
    Steven
    Contributor II
    2 years ago

    Yes, you can call a stored procedure from OS:

    Do something like this in a business rule:

     

    Using dbConnApp = BRApi.Database.CreateApplicationDbConnInfo(si)
    BRApi.Database.ExecuteActionQuery(dbConnApp, "EXEC [StoredProc]", True, True)
    End Using

    • vmanojrc30's avatar
      vmanojrc30
      Contributor
      2 years ago

      Thanks Steve! Is the [StoredProc] is a string that contains the Stored Procedure (line of codes) and need to be within the BR?

  • Steven's avatar
    Steven
    Contributor II
    2 years ago

    [StoredProc] is the name of the stored procedure you are running.

    I have a stored procedure named TruncateMyTable

    I would be: Exec TruncateMyTable

     

  • vmanojrc30's avatar
    vmanojrc30
    Contributor
    2 years ago

    I am able to execute the stored procedure. Now as a next step I need to execute a Data Management job to import the data from DB to OS Cube.

    Prior to that I need to check whether the stored procedure executed successfully without errors. How can I check for errors within the BR? The brapi.Database.ExecuteActionQuery command has a Boolean parameter to log errors. But I am not sure how I can access those error messages.