Dynamically Select SQL server based on a variable

[Claje]

Hi,

I'm posting here because I'm not even sure if this is something we could easily do.

What I'd like to do is be able to use some sort of environmental variable, like the location of the workflow or the username, to change what server a query will run on.

IE, if the workflow is in a folder named "PROD", run against the "PROD" server.  If the workflow is in an "MO" folder, run against "MO", etc.

Any suggestions would be great - I thought Dynamic Input might be a place to start but I haven't been able to make headway.

I'm also fine with a Macro solution to this issue, as ideally we will standardize this among our Alteryx users.

Thanks!

[jdunkerley79]

Yes think do able with a Dynamic Input.

Have attached a demo showing how you might do it.

First part gets the folder name

Then it makes a full connection string

Finally applies this to a dynamic input.

[Claje]

Thanks for the quick response!

I think this is very close to what I need, but it seems that there's an error triggered if the connection string is too long, and if I stop the string after server name there is also an error.

This is roughly what our SQL Server connection strings look like:

odb:Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Houses;Data Source=prd;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=C-1234;Use Encryption for Data=False;Tag with column collation when possible=False

 

Is there a way to reduce the amount of information here or pass in a larger string, that you know of?

Below is the error message:

 Dynamic Input (6) Error opening table: Microsoft OLE DB Provider for SQL Server: Invalid object name 'odb_Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Houses;Data Source=prd'.\42S02 = 208

[jdunkerley79 · Accepted solution]

Think you can remove:

;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=C-1234;Use Encryption for Data=False;Tag with column collation when possible=False

You will need to alter the Initial Catalog with the name of the database as well.

I found it worked better on mine to do a full replace for file and table/query. Easiest way to get the template for this is to create a dynamic input and then view the Xml.

The string you need to base the new one on is in the File node. It has the actual SQL query or table name in it as well.

Have attached a tweaked demo which I got working on mine against two different Local DB instances.

[Claje]

This helps a lot!

I'd be happy to mark this as a solution, but I do have one more question that I'm wondering if you have any thoughts about.

If the folder doesn't match one of the ones I want, IE MO/PROD, I want to run the exact template the user configured.

Is this sort of thing possible?

Thanks!

[jdunkerley79]

Yep. Not in front of Alteryx at moment so this is approximate!

In the formula tool making the connection and query string, replace the [Folder] with something like
IIF([folder] in ('mo','prod'),[Folder],'default')

Replacing default with the original name you need.

Hope that gives you enough to go on

[MZaidi]

Hi there, 

if there a way to add SQL username in this connection string. I am getting error using the string you mentioned because the Alteryx Services user is not in our SQL server that is running this string. How do incorporate User in here?