This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I'm trying to convert one of my Alteryx workflow into an Analytics App and I have a MS SQL Server table that I'm trying to write to. In the Analytics App I want to be able to choose which SQL database and output table I want to write to. Can't seem to find the correct interface tool to achieve this. Has anyone got any ideas on how I should go about this? I have an ODBC connection set up with Microsoft SQL server and I can successfully write to a table that I want by just executing the workflow but just can't seem to find the interface tool to translate my workflow into analytics app and then to choose the database and SQL table via the App.
Please see below screen shot of the workflow with Output Data tool that works with ODBC connection to SQL server,
This is a normal workflow that shows how I'm writing into SQL server using ODBC connection
What I then did was tried to use the 'Text box' and 'Action' interface tools to connect to the 'Output Data' tool and see if I can update the SQL database and table name but doesn't seem to work.
Update value in the 'Action' tool then expects you to update the entire connection string but I don't expect the user who is running the App to know the connection string but instead should simply choose the database 'ACM_Robotics' and SQL table '[02_cdm].[General_Ledger_new]' and then run the App.
I really appreciate any help anyone can offer. Thanks very much.
This is fantastic. Thanks a lot to both of you for your help. It works now.
I think I'm very close to where I want to get to with this one. I have a question with respect to the drop down tool. Instead of manually setting the values for database name and table name, is there a way to dynamically pull the list of database names within the MS SQL server and all the table names from the database selected. I tried to do this and I got the following error. Appreciate if you can share some insights into this.
What I'm trying to do is the following within drop down tool in your Analytic App (for example within Choose Database drop down tool). I changed the List values option from 'Manually set values' to 'External source - must contain Name && Value fields (can be relevant path)'. This resulted in the above error. Not sure if this is the way I should be doing it or is there another way to connect to the server with the existing ODBC connection and bring in the list of MS SQL databases and tables.
@JordanB I tired to insert the data into SQL Server tables with a Loop statement for 500 random record numbers, It works perfectly fine with SQL Server but when i applied the same query from Pre SQL Statement I can only insert up to 144 records Max, Is there a limitations to insert the number of records into SQL Sever with Alteryx ??
Here is the code I used
DECLARE@firstAS INT = 1 DECLARE @last AS INT = 500
WHILE(@first <= @last) Begin insert into Dummy table(Customer_nbr,Account_title,acct_assn_nbr,acct_appl_code,acct_cfm,update_type) values(RAND() * 100000, 'Dummy checking',RAND() * 10,'PK',RAND() * 10000,1) SET@first+= 1 END