Calling Stored Procedures in Dynamic Input Tool

[gayandj]

Hi,

This is my first post and I'm fairly new to Alteryx but thanks to the useful knowledgebase 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. Cross check two tables A and B to ensure that all records in A are populated in B.

2. If this is the case I take the record ID in B and update the 'Transfer Status' to 'Y' in table A matching the record ID. The transfer job is handled by a separate interface.

My task is to update the transfer flag based on the record ID. The problem is I'm calling a stored procedure from the dynamic input tool. Reason for using the dynamic input tool is that the transfer flag has to be updated against a given record ID. I have a simple stored procedure in SQL server which takes in the record ID parameter and performs the update. The SP works when executed in the SSMS. However, when the SP is selected in the dynamic input tools I get the following message: Error opening "EXEC SPName <Record ID>": No Columns Returned.

What could be the reason for this error? I've tried returning a value but it still hasn't solved the issue. Any suggestions are highly appreciated. I hope I've chosen the right tool.

Thanks in advance

[michael_treadwell]

You can certainly use the Dynamic Input tool to run stored procedures so I think you are using the correct tool. The error message makes me wonder if the tool is properly configured. Could you post a screenshot of your Dyanmic Input configuration window to give us more info?

[gayandj]

Hello Michael,

Thanks for your response. I've included a screenshot of the configuration window. I hope this is helpful.

 

[michael_treadwell]

When you choose a stored procedure from the list you will choose a default value for the SP parameter:

Then, when you choose to update the SP in the Dynamic Input configuration, you choose the parameter to update and the field with which to update it:

The update works by simply replacing the text (in this case '123') in the SP with the text found in the replacement field from the input data (in this case the field [c_id])

[gayandj]

Thanks, I did try that. 

This is my Query window where the stored procedure is selected and a default value is specified:

However, my parameter configuration window unlike yours does not show parameter name or default value, instead just indicates as 'Unknown'

That is strange and I'm going to investigate the cause of this. Could it be as you mentioned a configuration error?

Many thanks.

[gayandj]

Hi,

Could the fact I'm using SQL server 2008 R2 have anything to do with this?

[gayandj · Accepted solution]

After much head scratching and hair pulling I believe the problem is now resolved.

Though the stored procedure is visible in the stored procedures section, in the SQL editor I gave the fully qualified name. In other words it was specifying the <Database Name>.<Table Name>

Once this was done and when I tested the query and the message popped up confirming it is now successful. The reason it didn't strike me at first was that all stored procedures were visible so never expected I would need to specify the fully qualified name. Anyhow, the matter is now resolved. Thanks for all the help given.

[asteryx]

Hi - I am trying to accomplish the same thing: launching a SQL Server Stored Proc from Alteryx.

At first I was using OLEDB and this didn't present the same options as those suggested in this thread. I then created a User DSN entry with a new ODBC Driver and tested the connection successfully.

Then, in Alteryx, I tried to create the connection using ODBC and still didn't get anything to choose from, BUT, when I went back to OLEDB again, the procedures were now visible. I don't get why that is, but it's progress.

Now, I don't see how to bring up the Update Stored Procedure window that is described here. Can someone tell me how to open that feature?

Also, what are you using as input into the Dynamic Input tool? Is it not possible to call a procedure "from scratch"? Or would that require a regular input tool?

Thanks!

[Shivang]

I Have stored procedure with multiple result sets.

On calling from Alteryx procedure is returning only 1st result set not other.

Please help me.

[Madhuri2]

Could you please explain in detail what exactly you meant by <Database Name>.<Table Name>

Iam facing the same error "No columns returned" i could see that parameters as well.

thanks