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 · Accepted solution]

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

[OverSQL]

Sorry for delayed response.  This is what I needed!

[UpendraPandey]

Hello @jwalder 

I m trying to execute a stored procedure in output tool Post SQL 

Basically doing something like this

exec sp_proc_market_details "market_area"

market_area is static parameter.

Though Alteryx is saying it executed , but stored proc is not getting executed.

And same stored proc i am runnning to SQL Server managment studio and its working fine and loading the data.

[OverSQL]

If I have understood you correctly, you are trying to execute SQL using the dynamic input tool?

If this is the case you need to ensure that the SQL string getting passed into this tool includes the single quotes around the parameter.

If this does not solve it can you post the workflow and should be able to figure it out.

[OverSQL]

The other thing you could try to see what is getting executing is running a trace against SQL server (using Profiler). 

Understand that many users might not have the rights to do this though.......

[jwalder]

I'm not a SQL Server expert, but I am kind of surprised that (when executing it in SSMS) it would accept the parameter value in double quotes rather than single quotes.

i.e. Have your tried:
exec sp_proc_market_details 'market_area'

[godoyau]

Hi, I didn't see that this post existed, so I opened another one, but I believe I have a similar problem. Can you help me?

https://community.alteryx.com/t5/Alteryx-Designer/INPUT-DATA-CONSUMING-SQL-PROCIDURES/m-p/618301/hig...