Execute SQL Server Stored Proc

OverSQL

Hi Alteryxers

I am trying to run a stored proc to process a Tabular model.  I have run up against the issue where designer complains that :

Error: Dynamic Input (3): Error opening "EXEC ProcessTabularDatabaseXMLA": No Columns Returned.

I have added a dummy data set to be returned at the end of the proc after the XMLA is execute but still getting the error.  I was also hoping for the second step of the proc to only execute once the XML script has finished executing, otherwise it looks like the process is finished (while the cube is still being processed in the background).

I've tried changing between OLEDB, ODBC and MS SQL Native Client but no joy.  Any pointers would be greatly appreciated.

Thanks

Andrew

jwalder

Not completely following, but if you are trying to execute a stored proc that is not intended to return a recordset then it is basically the same as executing a non-query like an update or a delete in terms of the lack of a recordset. If your exec is static, i.e. always "EXEC ProcessTabularDatabaseXMLA" with no parms, then you could use the input tool and put the EXEC in the "Pre SQL Statement" and something to return execution statistics as the "Table or Query". You could use something like:

Select Top 1 d.object_id, d.database_id, Object_Name(d.object_id, d.database_id) 'proc name', d.cached_time, d.last_execution_time, d.total_elapsed_time, d.total_elapsed_time / d.execution_count As avg_elapsed_time, d.last_elapsed_time, d.execution_count From sys.dm_exec_procedure_stats As d Where Object_Name(d.object_id, d.database_id) = 'ProcessTabularDatabaseXMLA' Order By d.last_execution_time Desc

If you need to be more dynamic about the execution, then you could stick that in a macro and use a parameter to pass in the "Pre SQL Statement". Since it has the ability to supply the SQL on the input row, another alternative would be to use the Execute SQL tool here: https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Partner-Tools/ta-p/123297