Problem
Usually when we need to run a SQL script we open it in SQL Server Management Studio and execute it, but there are cases when the file is too big. For example, when the script we need to run is a scripted database containing a large schema and data. Also, you may need to run a script on a SQL Server instance running on Linux where you cannot connect using SSMS due to firewall rules. In this tip I will show you how you can accomplish these tasks with sqlcmd.
Solution
It is a fact that we as SQL Server DBA’s are more prone to use graphical tools for our day to day work, mostly because SQL Server has historically run on the Windows platforms. Things are changing and with the release of SQL Server 2017 it won’t be uncommon to see SQL Server instances running on Linux. That will force us to adapt to new ways of doing our work, like running script files.
Sqlcmd to the Rescue
As I told you in my previous tip Introduction to SQL Server’s sqlcmd Utility, this command line tool allows you to execute T-SQL statements, stored procedures, and script files from the console. Amongst the sqlcmd arguments there are three that will serve us when we need to execute a script from the command line.
| Argument | Description |
|---|---|
| -i <filename> | This argument followed by the script name serves as input for sqlcmd. After executing the commands in the input file sqlcmd exits. |
| -o <filename> | With this argument you can make the input queries write the output to a file. |
| -u | Specifies that the output file is stored in Unicode format. |
| -e | Echo input. Basically, when you specify this argument, sqlcmd writes the commands of the input file to the console or the output file before showing the results. |
SQL Server sqlcmd Examples
Now I will show you a few examples on how to run script files with sqlcmd for different scenarios. I am using a script file with the AdventureWorksDW database which you can download from Microsoft for free at this link: AdventureWorks Databases and Scripts for SQL Server 2016.
Passing an Input file to sqlcmd
If you need to execute a script file with sqlcmd on a server using Windows Authentication (a Trusted Connection), you can do so with the following command:
sqlcmd -S 127.0.0.1 -E -i AdventureWorksDW2012.sql
The -S argument is the server name, and the -E argument is for a Trusted Connection.
On the other hand, if we need to accomplish the same task, but use a SQL Server login, there is a slight variation to the previous command:
sqlcmd -S 127.0.0.1 -U sa -P 1234 -i AdventureWorksDW2012.sql
In this command the -U argument is used to specify the SQL login account and the -P is for the account password. As you may notice, this is not the best way to authenticate to SQL Server if you need to use sqlcmd in a batch script, since it shows the password.
Saving the Output to a Text File
Usually when we are running a big script, we cannot use the screen output to determine if one of the batches on the input script has failed because the output is displayed really fast on the screen. To overcome this situation we must redirect the output to a text file for further analysis.
The following command will connect to SQL Server using Windows Authentication, execute the file after the -i argument and save the execution results in the file after the -o argument.
sqlcmd -S 127.0.0.1 -E -i AdventureWorksDW2012.sql -o QueryResults.txt
Saving the Output to a Text File Including the Input Batches
This feature is very useful when you are running small scripts on a database and need to send the results to another person, like the development team. To do so, we have to add the -e argument:
sqlcmd -S 127.0.0.1 -E -i AdventureWorksDW2012.sql -o QueryResults.txt -e
Remember that the -e argument is not required to be used with the -o option. You can also use it without the -o argument if you need the results to be displayed on the screen. That may be useful if you are invoking sqlcmd from another program that reads the standard output.
Suppose you have a script file named sample.sql with the following contents:
SELECT @@VERSION
GO
SELECT @@SERVERNAME
GO
If you want to execute this file and display the output to the console, you can use the following command:
sqlcmd -S 127.0.0.1 -U sa -P 1234 -i sample.sql -e
The output of the command will be like the screen capture below. I enclosed the commands with a red box to make this clear.
Next Steps
- Read my previous tip – Introduction to SQL Server’s sqlcmd Utility.
- If you need to execute multiple files with sqlcmd, you can take a look at Armando’s tip: Using SQLCMD to Execute Multiple SQL Server Scripts.
- If what you need is to run a SSIS package from command line, please review the following step Command line tool to execute SSIS packages from the SQL Server Integration Services (SSIS) Tutorial.
- Need to run Analysis Services scripts from the command line? In the following tip you will find the answers: Using ASCMD to run command line scripts for SQL Server Analysis Services.
