Passing parameters to stored procedures (SQL Server OLEDB Input Tool)

[Felipe_Ribeir0]

Hi all,

I am testing the use of Alteryx to get data from SQL Server stored procedures, this one has only one parameter. With NULL it works, with '319' it shows an error and with 319 it shows an error too. The parameter is a varchar. Both statements works on the SQL Server interface. 

What am i missing here? Already did this in the past, but i dont have access to the workflow anymore to see it.

[gautiergodard]

@Felipe_Ribeir0 I am sure you tried this - but have you tried double quotes?

[Felipe_Ribeir0]

@gautiergodard yes, unfortunately it is not being accepted too:

[Sarreddy]

Hi @Felipe_Ribeir0 

If it is MS SQL Server passing the variables can you try to change select statement.

execute procedureName '319'

Regards,

Ajith Redy 

[Felipe_Ribeir0]

hI @Sarreddy 

Does not work either on the input tool

[IraWatt]

Hey @Felipe_Ribeir0,

Not hugely helpful but here is the syntax I used in the Input data tool, and it works:

I had issues with Stored procedures and found adding SET ANSI_NULLS ON to the SQL fixed the issues.

[Sarreddy]

@Felipe_Ribeir0

Can click the box what I highlighted.

[Felipe_Ribeir0]

@IraWatt , the procedure already has the SET ANSI_NULLS ON.

@Sarreddy clicked the box, the problem still the same.

I think that this is some weird and particular problem of my environment somehow, it should be working.

Found a workaround for now using pre/post SQL statements and loading the procedure data into a temporary table (the crazy thing is that the same procedure statement works as pre sql statement) following this topic:

Re: No target OLEDB object available? - Alteryx Community

For now it will be enough to make it work, but it will add more unnecessary maintenance complications to the client. I will keep the topic as unsolved, as this is just a workaround. If you all have more possible solutions, i will be glad to try out 😁

[Namshad]

Hi @Felipe_Ribeir0 ,

Could you please confirm whether you have modified the stored procedure to load the results to temporary table or normal database table.
If it is a temporary table then how you are reading the results of the table in Alteryx ?
Could you please share the configuration of input tool showing pre sql statement and table query.