Dynamically creating connection string works in Designer fails from Gallery

[pdbaile]

Hello all,

      I am responsible for maintaining multiple Excel tools and Alteryx workflows that hit multiple databases depending on needs. I have an azure database setup that contains all my credentials and connections, I have the excel tools set to pull credentials from that database when they open, I am trying to get Alteryx to work the same way. This approach allows me to maintain all my credentials in the DB and not have to update all the excel tools and Alteryx workflows everytime a password or connection path changes.

      I have the flow setup and working as expected in the designer, but for some reason when I upload to the gallery and run it i get the error:

OCILogon2 Error: ORA-12154: TNS:could not resolve the connect identifier specified (Tool Id:13)

The setup i have working in the designer uses an input tool to pull the credentials and the connection details in from the azure DB, it then uses a filter to select the desired connection. I use a formula tool to build the connection string and append the SQL query to the end of the string, then a select tool to only pass the completed connection string to the dynamic input tool. The dynamic input tool uses the field created by the formula tool to Change Entire File Path.

This flow works perfectly from the designer, but fails with the error :

OCILogon2 Error: ORA-12154: TNS:could not resolve the connect identifier specified (Tool Id:13)

when ran from the gallery. Any help with this would be greatly appreciated. Thank you.

[pdbaile]

[seven · Accepted solution]

This doesn't seem like an Alteryx issue. You probably have an Oracle driver or library installed on your machine that is not present on the Gallery server. Can you or an admin verify that you can connect to Oracle from the Gallery server first?

[pdbaile]

I can confirm 100% that the Gallery has all the necessary drivers to connect to Oracle. I currently have 25-30 workflows that run queries against the same connections on a daily and hourly basis from the Gallery. I think it has something to do with how I am passing the query along in the connection string.

I currently have it setup like:
"oci:"+[uid]+"/"+[pass]+"@"+[connection]+"|||"+[SQL]
if I change it to :
"oci:"+[uid]+"/"+[pass]+"@"+[connection]

I get 

• No table chosen; Please select a Table from data source. (Tool Id: 13)

[pdbaile · Accepted solution]

I figured it out. @seven  was pretty close to right on. It was my connection string. I was using something like:
      dsoraclero.s01234.us.:1234/AB34prd_standby
Works fine for local tools or from the designer, from the gallery I needed:
      dsoraclero.s01234.us.my-company.com:1234/AB34prd_standby

I would imagine it has something to do with what networks the DBs and the Gallery are on...Either way it is up and working now.

[seven]

@pdbaile 

Congrats on solving it. Always a good bet to see what the connection looks like from prod, not just dev. Excellent effort.

[yoyoincs]

Hey all,
I happen to have a similar query. I wanted to create an Alteryx App where allowed users will give SQL servers and DB name as input and can export data or update data if needed from that app.
Is it possible?