Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

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

Felipe_Ribeir0
16 - Nebula

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. 

 

Animation.gif

Felipe_Ribeir0_0-1668625184825.png

 

 

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

 

8 REPLIES 8
gautiergodard
13 - Pulsar

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

Felipe_Ribeir0
16 - Nebula

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

 

Felipe_Ribeir0_1-1668682841379.png

 

Sarreddy
9 - Comet

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
16 - Nebula

hI @Sarreddy 

 

Does not work either on the input tool

 

Felipe_Ribeir0_0-1668684126495.png

 

IraWatt
17 - Castor
17 - Castor

Hey @Felipe_Ribeir0,

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

IraWatt_0-1668685191676.png

IraWatt_1-1668685250013.png

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

 

 

Sarreddy
9 - Comet

@Felipe_Ribeir0

Sarreddy_0-1668685331133.png

 

 

Can click the box what I highlighted.



Sarreddy_1-1668685456419.png

 

 

 

Felipe_Ribeir0
16 - Nebula

@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
7 - Meteor

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.

Labels
Top Solution Authors