Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
The Trade-off Between SQL Server Security and Performance - Free Webinar
 

Execute SQL Server Script Files with the sqlcmd Utility


By:   |   Last Updated: 2017-07-06   |   Comments (3)   |   Related Tips: More > Database Administration

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 DBAs 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 wont 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.

SQLCMD to Screen - Description: Screen capture showing the screen execution of sqlcmd including the input batches on the output.
Next Steps


Last Updated: 2017-07-06


next webcast button


next tip button



About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, April 10, 2018 - 9:46:05 PM - Daniel Farina Back To Top

 

Hi Andrew!

Thanks for asking. Look if the following sentence works for you:

sqlcmd -S SERVER -E -Q master.dbo.sp_help

The previous command will execute the stored procedure sp_help on the master database and exit.

Please let me know if this solved your problem.

Best Regards!

Tuesday, April 10, 2018 - 10:27:31 AM - Andrew Back To Top

 Hi Daniel 

Could you please explain the setup if i want to only call a Stored Procedure in mssql using sqlcmd. I do not need to see any output but merely to execute the proc without having to login to mssql.

 

regards

A

 

 


Thursday, July 06, 2017 - 8:07:28 AM - Channdeep Singh Back To Top

Very nice and much needed work. Many many thanks. We all have needed many of these commands time-to-time in our journey; but they were not collated in one place.....and everytime I had to gather and do the "hit and trial" :) Thanks again.


Learn more about SQL Server tools