nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

DeepakMR85

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

Interface Tools

Accepted answers

LindaT

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.

UpdateActionTest.yxwz

JordanB

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.

Best,

Jordan Barker

Solutions Consultant

Sample_App.yxwz

All comments

LindaT

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.

UpdateActionTest.yxwz

JordanB

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.

Best,

Jordan Barker

Solutions Consultant

Sample_App.yxwz

DeepakMR85

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.

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.

Thanks once again.

Regards,

Deepak

RRaoDannayak

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

Quick Links

Popular Tags

Child Item

This months top contributors

atcodedog05 19598

Qiu 15976

binu_acs 15783

MarqueeCrew 13710

apathetichell 13703