Hello, After Alteryx posting data into AWS RDS SQL Server, it executes a procedure on the same database. Permissions are all verified. The problem is only the first DML statement execute successfully, the rest DML statements were ignored and was not execute at all. I had all statements within BEGIN... END. I tested with simple DMLs and more complicate DMLs and all are the same. This test is to verify that the complex DMLs are not the issue. The procedure ran fine when execute manually in SQL Server query editor. Any one has any idea where I should look? Is there a setting that I missed? Thank you HLe
Solved! Go to Solution.
Hi @ledhai
Could you post a screenshot showing the configuration of your Output tool, in particular the Post-SQL statement (I assume this is what you are using?)
That looks OK to me. Is the issue that you're including multiple Post-SQL statements and only the first is executing? If so I think that's as designed - you'll need to use multiple tools to run multiple stored procs.
Yes. It is only execute the first statement only.
What do you have in mind of "multiple tools to run multiple stored procs"? I am looking into different option such as with SQL Server table's trigger. Do you have an idea within Alteryx tool set?
Thanks
SOLUTION:
To overcome this limitation that only a single DML statement can be executed, I created a "trigger" table that a record will be inserted after the main data are loaded into SQL table. There is trigger on INSERT on this "trigger" table to execute the procedure of multiple DML statements. It works.