Dynamically Select SQL server based on a variable & dynamically change SQL query

[Denisa_Grecu]

Hello everyone,

I am struggling to solve the following situation:

 - I am trying to create the Alteryx environment variables (DEV, PROD, UAT) while bringing in an OLE DB SQL connection using a dynamic input tool.

- The Alteryx environment variables (DEV, PROD, UAT) need to update automatically in the SQL connection the Workstation ID based on the environment

- Also, the SQL query itself is using an automatic field that updates the "ingestion_date" in the query based on a date given in the input.

I tried to put everything in a workflow (PFA) but I am having an error: "Error opening connect string. Can't create data source object."

I have noticed that not all the information previous written in the query is being outputted, so I think this is the issue, but I don´t know how to solve it.

Any help or hints that you might have for me?

Please note that the attached WF has some fake info just for testing and confidentiality reasons.

Thank you in advance,

Denisa 

[geraldo]

@Denisa_Grecu 

The sample workflow sample that you attached to the formula tool SQLQuery field is V_String 2000 so I'm signaling the others are VW_String with 1073741823. If you've corrected it, great. Another point is that the concatenation of SQLconn and SqlQuery in the xml is composed of 3 "|" and you are only putting one.

><![CDATA[odb:Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=SWPDUSSQL15\INST1;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=VDKNYCGNL2785;Use Encryption for Data=False;Tag with column collation when possible=False|||select 'LOAN_IQ' as nx_appli_emettrice,

[ArnaldoSandoval]

Hi @Denisa_Grecu 

I noticed the same thing as @geraldo your variable applies a single pipe, should it be three, I do not know, and I can't replicate the environment.

[geraldo · Accepted solution]

@Denisa_Grecu 

I used the woirkflow that you attached and made the corrections that I thought were necessary to generate the concatenated connection and query.
I made an example using macro.
Directly in the app the dynamic input tool is changing the "|||" by 3 "_" and causes an error

the workflows are attached

[Denisa_Grecu]

Hello @geraldo .

I tried putting the "|||" in the normal workflow that I had and it didn´t work.

Using your macro example the workflow works now.

Thank you for your help :-)