Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Problem With Dynamic Input For SQL Server

amanda04
6 - Meteoroid

I'm having this problem with Dynamic Input for Sql Server. I want to dynamically input a data source and a Select statement but when I run my workflow this error appears:

 

Dynamic Input (23): No table chosen; Please select a Table from data sourceDynamic Input (23): No table chosen; Please select a Table from data source

My input Data Source Template is:

 

 

odbc:DSN=Server1;UID=99999;PWD=__EncPwd1__

 

 

And I also input a table to select for test:

Select.PNG

 

Idk what I'm doing wrong, is there a away to solve this issue?

10 REPLIES 10
Thableaus
17 - Castor
17 - Castor

Hi @amanda04 

 

I'm not so sure if you can pass a connection string to Dynamic Input with user and password in it, since the password is encrypted.

https://help.alteryx.com/11.7/index.htm#PasswordEncryption.htm?Highlight=encrypt

 

Do you really need to change the connection to the database?

 

Cheers,

amanda04
6 - Meteoroid

I'm using the password and the user name as a parameter and replace them on my data source template. The problem is the select statement I guess.

Thableaus
17 - Castor
17 - Castor

@amanda04 

Yes, I can see that, that's not the point. I'm saying this might be the cause of the error.

You're changing full path of the connection string, including username and password through a Formula Tool. In the connection string, username and password are encrypted. I'm not so sure if this is the best way to do this.

 

Have you tried to use a regular Input Tool, with the parameter you're passing in the Formula Tool as a reference and do a simple select statement over there? 


Cheers,

amanda04
6 - Meteoroid

I don't know how can I use then simple Input data with variables because I will need to configure a connection to input. Can you give me example to do that?

 

Tks a lot for the help!

Thableaus
17 - Castor
17 - Castor

@amanda04 

 

That's just to test your select statement and connection you're trying to get through the Dynamic Input Tool.

 

What I'm saying is: first, test in a regular Input Tool using your parameter as a reference and do a select statement there.

If it works fine, then it probably doesn't have to do with the statement itself, and it could be something to do with how you're using the Dynamic Input tool.

 

I haven't had this scenario before and I'm curious to see the output. @CharlieS  or @danilang , did you guys do something similar in the past?


Cheers,

amanda04
6 - Meteoroid

Undertood, I will try!

CharlieS
17 - Castor
17 - Castor

If you just want to change the table/query and keep the same connection, the Dynamic Input will take care of that. You'll need to change the configuration to "Modify SQL Query" to make those changes.

 

In this case, your ask was to modify the connection settings too. Since that isn't a part that the Dynamic Input is ready to modify, you'll have to do it yourself by editing the XML (which mean macro time). I'll see if I can put something together as an example here shortly. 

 

 

Thableaus
17 - Castor
17 - Castor

@CharlieS 

 

That second part I'm curious about. I had an idea that Dynamic Input tool doesn't fit in this scenario of changing connection credentials. 

 

Cheers,

CharlieS
17 - Castor
17 - Castor

@Thableaus 

The "Modify SQL Query" options of the Dynamic Input tool, the "replace string" is only applicable to the query context, not the connection. The connection can be modified, but only via XML, so I typically build a quick batch to do that.

 

@amanda04 

Check out the attached workflow that shows how to use a Dynamic Input to modify the table/query, and also a batch macro to dynamically modify the table/query and the connection parameters via XML.

 

 

Labels