Alteryx Designer Desktop Knowledge Base
Definitive answers from Designer Desktop experts.How to use the Run Command tool for connecting to SQL Server and importing data
How to use the Run Command tool for connecting to SQL Server and importing data
Sometimes the Input Data tool may have difficulty with complex SQL that includes things like cast statements and temporary tables or stored procedures with unusual parameter configuration. Also, you may have a use case for running multiple stored procedures from a batch script in a workflow. For these scenarios, the Run Command tool provides an alternate method for returning data to Designer from a SQL Server that might not be possible with an Input Data tool.
Prerequisites
Designer, SQL Server
Procedure
The sqlcmd utility can create a connection to a SQL Server, send SQL statements and stored procedures, and return data from the Command Prompt. The commands for these actions are stored in a batch file, and the Run Command tool calls this batch file.
1. Download the sqlcmd utility using this link: download.
2. Create a .sql file.
The file can be created with an SQL Editor such as SQL Studio or Toad, or with a text editor like Notepad. Copy the SQL script into the document and use this format.
USE database name;
GO
stored procedure exec statements and/or other SQL;
GO
A Microsoft example script for the sqlcmd utility is available here. When saving with a text editor such as Notepad include .sql at the end in the File name text box.
3. Test at the Command Prompt
Right-click the Command Prompt option and run as Administrator. Test the sqlcmd command with at least three sqlcmd parameters to:
a. connect to SQL Server (-S)
b. run the .sql script (-i)
c. create an output file with results (-o)
Here is an example: sqlcmd -S Server\instance -i C:\MyScripts\script.sql -o C:\test\output.csv.
For a complete list of options and syntax used with the sqlcmd command, click here and look for the Syntax section.
Ensure the sqlcmd can connect to the database, return data with the SQL script, and write the output file as expected.
4. Create a batch file.
The tested sqlcmd command with its parameters should be copied to a new Notepad document. When saving the batch file, include .bat at the end in the File name text box.
5. Configure the Run Command tool
Add the location of the batch file in the Command section of the Run Command tool Configuration screen.
The output file created with the sqlcmd -o parameter will have the results of the SQL. Enter the location of the output file in the Read Results section of the Run Command tool Configuration screen. Afterward, the contents of the file become the output of the tool.
keyword search: SQL Server, multiple stored procedures, parameters, cast statement, temp table, temporary table, sqlcmd, import data
Sometimes the Input Data tool may have difficulty with complex SQL that includes things like cast statements and temporary tables or stored procedures with unusual parameter configuration. Also, you may have a use case for running multiple stored procedures from a batch script in a workflow. For these scenarios, the Run Command tool provides an alternate method for returning data to Designer from a SQL Server that might not be possible with an Input Data tool.
Prerequisites
Designer, SQL Server
Procedure
The sqlcmd utility can create a connection to a SQL Server, send SQL statements and stored procedures, and return data from the Command Prompt. The commands for these actions are stored in a batch file, and the Run Command tool calls this batch file.
1. Download the sqlcmd utility using this link: download.
2. Create a .sql file.
The file can be created with an SQL Editor such as SQL Studio or Toad, or with a text editor like Notepad. Copy the SQL script into the document and use this format.
USE database name;
GO
stored procedure exec statements and/or other SQL;
GO
A Microsoft example script for the sqlcmd utility is available here. When saving with a text editor such as Notepad include .sql at the end in the File name text box.
3. Test at the Command Prompt
Right-click the Command Prompt option and run as Administrator. Test the sqlcmd command with at least three sqlcmd parameters to:
a. connect to SQL Server (-S)
b. run the .sql script (-i)
c. create an output file with results (-o)
Here is an example: sqlcmd -S Server\instance -i C:\MyScripts\script.sql -o C:\test\output.csv.
For a complete list of options and syntax used with the sqlcmd command, click here and look for the Syntax section.
Ensure the sqlcmd can connect to the database, return data with the SQL script, and write the output file as expected.
4. Create a batch file.
The tested sqlcmd command with its parameters should be copied to a new Notepad document. When saving the batch file, include .bat at the end in the File name text box.
5. Configure the Run Command tool
Add the location of the batch file in the Command section of the Run Command tool Configuration screen.
The output file created with the sqlcmd -o parameter will have the results of the SQL. Enter the location of the output file in the Read Results section of the Run Command tool Configuration screen. Afterward, the contents of the file become the output of the tool.
keyword search: SQL Server, multiple stored procedures, parameters, cast statement, temp table, temporary table, sqlcmd, import data
