Passing parameters to stored procedures (SQL Server OLEDB Input Tool)
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Labels:
- Input
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@Felipe_Ribeir0 I am sure you tried this - but have you tried double quotes?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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 😁
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.