Introduction

This article describes how to use stored procedures from different database schemas; specifically procedures that are named the same but residing in different schemas.

Current Behaviour in the Call Stored Procedure (OLEDB) Tool

When building a Call Stored Procedure (OLEDB)Closed The Call Stored Procedure (OLEDB) tool executes database stored procedures and functions to provide read, write, and automated data processing. task step, you must specify the database holding the stored procedure. Typically, this is enough for the task to use stored procedures from different schemas but there may be instances where a stored procedure in two separate schemas has the same name. At runtime, you may find that parameters from the "other" stored procedure are also called, resulting in a failed step.

Resolution

As well as the procedure detailed below, it is also recommended you upgrade your BPA Platform installation to version 2020 Update 1 or higher due to improvements made in that release for the Call Stored Procedure (OLEDB) tool.

Do the following for all Call Stored Procedure (OLEDB) global connections that have similarly named stored procedures:

  1. Highlight the connection and click Properties.
  2. Go to the Options tab.
  3. Change the Uses Schema property to Yes.
  4. Click OK to save the changes.