SQL Server Procedure with multiple DML statements - only first DML statement execute?

[ledhai]

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

[jamielaird]

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?)

[ledhai]

@jamielaird 

Screen print is updated.  Thank you.

[jamielaird]

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.

[ledhai]

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

[ledhai · Accepted solution]

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.