How to run Long SQL with bat command via iterative macro?

[Crazycc]

I currently use <run command> to run <run.bat> , just like this: 

@echo off
sqlcmd -S localhost -d xx -U sa -P xxxx -i "D:\test0515\test02.sql" -o "D:\test0515\test.txt"

<test02.sql> is a long mssql  sql file(select * into test), that always runs over 3mins. To avoid the situation where SQL has not finished executing before Alteryx continues with the subsequent processes. 

I decide to use iterative macro:

1, put  iterative macro between <run.bat> and other module(Dynamic Input to read table that generate from SQL)

2, read test.txt every 30 seconds, find <xx rows affected>

3, if not find it then continue in iterative macro, it do exit the macro

I currently have no idea with specific solutions above, plz help.

[Krzysiek · Accepted solution]

Hey, 

I am not sure if I got your issue correctly, but if it is about executing workflow just after SQL completes, you may want to try using "Block Until Done Tool".

This way your workflow will be stopped and executed only after swim lane with your SQL query will be fully executed.

Please see below:

[apathetichell]

1) your cmd syntax seems fine.

2) 3 minutes is nothing in terms of SQL queries...

3) I'd use a standard workflow design.

[Crazycc]

It works, really appreciate!

[Crazycc]

1) your cmd syntax seems fine.

2) 3 minutes is nothing in terms of SQL queries...

3) I'd use a standard workflow design.

This is just a demo. In actual scenarios, the SQL processes need to run for more than 5 hours to handle over 3.5 billion rows of data.