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!
Solved! Go to Solution.
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
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.
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!
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?