Calling Stored Procedures in Dynamic Input Tool

[NikhilKhurana]

Hi,

This is my first post and I'm fairly new to Alteryx but thanks to the useful knowledge base here I've been able to find my way. 

I've come across a problem which hopefully someone would have encountered and resolved. Before that let me explain my requirement:

1.  Update a status column in a table where table name is parametrized.

2.  Table name is defined in Alteryx Database.

I am trying to solve the above problem by calling a Stored procedure in Dynamic Input Tool. The problem is 

1. If I use an OLEDB connection using SQL Native Client 11.0 as suggested in post "https://community.alteryx.com/t5/Engine-Works-Blog/Troubleshooting-Stored-Procedures/ba-p/13355" , I keep getting the error: Error: Dynamic Input (2): Error: No target OLEDB object available.
in Query: EXEC updatePrintStatus DirectImport_LeadSheet

2. In one of the post it is suggested you can all use ODBC connection as well, just mention the parameter in SQL editor. 

(https://community.alteryx.com/t5/Data-Sources/Stored-Procedure-Error-quot-No-Columns-Returned-quot/t...). In this scenario I get the error

Error: Dynamic Input (2): Error opening "EXEC dbo.updatePrintStatus": No Columns Returned.

My procedure is 

CREATE PROCEDURE updatePrintStatus
	@PassedTableName AS NVARCHAR(255)
AS
	DECLARE @ActualTableName AS NVarchar(255)

	SELECT @ActualTableName = QUOTENAME( TABLE_NAME )
		FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = @PassedTableName
	    
	DECLARE @sql AS NVARCHAR(MAX)

	SELECT @sql = 'Update '+@ActualTableName+' set PrintStatus=''Y'' where PrintStatus=''N'''

	EXEC(@SQL)

and the flow is attached.

The funny thing is the column gets updated even though the flow throws an error.

I have followed every suggestion provided in the knowledge base and community but I am unable to resolve this error.

What can be the possible reason it is not working for me?

How can I resolve it?

Please help me with this

Thanks in advance !!

[Kanderson]

This procedure will update a table but not return any values to feed through the rest of the workflow. You can either have the function return something to alterx, or what I often do is use my database command line tools to call the procedure. Alteryx can dynamically build the call statement (including the needed parameters) and execute it with the command line tool. 

Another option is to output your workflow table name to your database and use that value in your stored procedure. You could then simply call your proc as a post SQL statement.

Unfortunately the pre/post SQL statements and workflow events cannot accept input from a workflow.

[NikhilKhurana]

Hi Kanderson,

Thank you for alternate solutions.

The last suggestion worked for me but I want to understand for my knowledge that even after I changed the stored procedure and had it return value to alteryx but it's still giving error.

Could you also please explain the solution "use database command line tools to call the procedure. Alteryx can dynamically build the call statement (including the needed parameters) and execute it with the command line tool." as I am new to alteryx so not sure about this .Please correct me if I am wrong, are you suggesting that I run the procedure using command line tool wherein I will call sqlcmd.exe?

[tonyppham]

I'd also like some clarification on the database command line tool.