Exécution d'une procédure stockée et le code retour associé

Question

Je me permets de poser la question en espérant obtenir de l'aide.

Pour alimenter des tables de notre entrepôt de données, nous avons fait le choix d'utiliser pour certaines tables, l'utilisation de procédure stockée.

Toutefois, nous nous rendons compte que lorsque la procédure stockée est exécutée, celle-ci rend la main comme si cela s'était bien passé. Or, il s'avère que la procédure stockée est tombée en erreur.
Je précise que c'est une procédure stockée utilisée dans le Post SQL.

Existe t-il un moyen pour qu'Alteryx puisse connaître le résultat d'exécution de la procédure stockée ?

dossantos_f · Answer

Hi mceleavey,

Thanks for your question.

We use Alteryx well to power all of our tables.

The problem is that a large number of treatments supply CANCEL / REPLACE (Full) every day.
However, using Bulk (recommended by Alteryx), our processing times are not always very good.

Here are some details to set the context:
- Our database is SQL SERVER (hosted on a dedicated server)
- Alteryx is also hosted on a dedicated server (used only by Alteryx)

Therefore, we have decided to modify our treatments so that they feed our tables with DELTA and FULL.
However, as ssvb (Bulk) is not very efficient on millions of rows (during a FULL), we decided to make the database work using stored procedures.

For information, the procedures only carry very few functionalities compared to the Alteryx workflow:
- Copies the data from the TMP -> target table in DELTA / FULL
- Activation / deactivation of indexes during a FULL

But again, our goal is for Alteryx to tell us if a stored procedure (Post SQL) execution completed successfully.

mceleavey · Answer

Hi @dossantos_f ,

Not really, but why are you using a stored procedure?

Can't you just replace that with the action in Alteryx?

M.

dossantos_f · Answer

Yes, you are absolutely right ‌‌😀

Here is the translation of my message in French :

So, I take the liberty of asking the question in the hope of getting some help.

To feed the tables of our datawarehouse, we have chosen to use for certain tables, the use of stored procedure.

However, we realize that when the stored procedure is executed, it returns control as if it had gone well. However, it turns out that the stored procedure fell in error.
I specify that it is a stored procedure used in Post SQL.

Is there a way for Alteryx to know the result of stored procedure execution?

Thank for your help