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

Interface tool to choose SQL Server database and Table to write to

DeepakMR85
5 - Atom

Dear All,

 

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 connectionThis 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.

 

Analytics App_to_write_to_MS SQL Server.PNG

 

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.

 

Regards,

Deepak

5 REPLIES 5
LindaT
Alteryx
Alteryx

Hi Deepak,

In your Update Action, try using the Update Value with Formula.  I have attached a simple example of how I replace my table name with a formula.

JordanB
Alteryx
Alteryx

Hi @DeepakMR85

 

In the action tool when you select the SQL statement you will the string within the grayed out section at the bottom of the configuration window.

 

Here you can tick the 'replace a specific string' option and then delete everything but the table/database you are replacing. 

 

Do this process twice with one interface updating the database and another interface tool updating the table. 

 

I would highly recommend using the dropdown tool so you can paramaterize the table and db options yourself and avoid human error when typing the values in through a text box. 

 

pic1.jpg

 

pic2.jpg

 

Best,

 

Jordan Barker

Solutions Consultant

 

 

 

DeepakMR85
5 - Atom

Hey Linda and Jordan,

 

This is fantastic. Thanks a lot to both of you for your help. It works now.

 

Hi Jordan,

 

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.

 

Capture.PNG

 

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. 

 

Capture.PNG

 

Thanks once again.

 

Regards,

Deepak

JordanB
Alteryx
Alteryx

Hi @DeepakMR85

 

The table format needed to populate a dropdown would need to be NAME & VALUE

 

Name Value
Table1 Table1

 

So you can either create this within the DB or create another external source.

 

Best,

 

Jordan Barker

Solutions Consultant

RRaoDannayak
7 - Meteor

Hi @JordanB ,

 

@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 @first AS 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

Labels