Alteryx Not Finding Stored Procedures

[michael_franz]

I am trying to access 2 stored procedures that our online host has provided me access to. However, in the Input Data tool is not reading them. I have another program I am migrating from and moving to Alteryx. This other program is reading and executing them perfectly fine.

exec DBO.sps_Custom_RR_RptGenLedger

exec DBO.sps_Custom_RR_ProdAnalysis

If I add the above to the SQL Editor the Error says Unable to find Stored Procedure.

PLease advise. This is slowing my migration away from a mu

[BrandonB]

You most likely need to adjust the Default Database when you are setting up the database connection. If you pick the database that has the stored procedure you should then see it available. 

[michael_franz]

 This should be the fully qualified path and not getting columns

[BrandonB]

Please see the information in this link: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/No-Columns-Returned-for-Stored-Proc...

Do you happen to know what the Stored Procedure is doing? Sometimes I have found that running the stored procedure as PreSQL and executing it into a temp table and then calling the results of that temp table in the main Input Data tool query can be more flexible in accommodating stored procedures. 

[michael_franz]

Yes. I understand the results and I do know it will kick off several temp tables. Well only one of them does. The other does not. I've tried recreating it and get close and I live with it. But I have to adjust each time the ERP system is adjusted so I want to use the SP to run it. 

I do not understand the PReSql.

[BrandonB]

PreSQL is just SQL that executes before the main query in your Input Data tool. You can see more information here: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Alteryx-Pre-Post-SQL-Statements/ta-...

[CharudattaChousalkar]

Change the odbc connection to OleDB connection, then it will work.

In manage In-DB Connection,

Select Driver: SQL Server OLE DB

In Connection String>Data Link Properties>Provider=Microsoft OLE DB Provider for SQL Server.

And finally provide server name and authentication in Connection tab.

 

[michael_franz]

Thanks for the info. Progress has been made. I can see the a subset of the system stored procedures, but not the 2 that I am looking for. When I use SQL Mgt Studio, I see the other 2 I am looking for, but can not see them in Alteryx.