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

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.

Input

Accepted answers

All comments

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.

New Workflow3.yxmd

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703