Execute SQL Server Script Files with the sqlcmd Utility

By: Daniel Farina | | 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 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.

ArgumentDescription
-iThis argument followed by the script name serves as input for sqlcmd. After executing the commands in the input file sqlcmd exits.
-oWith this argument you can make the input queries write the output to a file.
-uSpecifies that the output file is stored in Unicode format.
-eEcho 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

Last Update: 2017-07-06

About the author

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

View all my tips

Related Resources

MSSQLTips责编内容来自:MSSQLTips (源链) | 更多关于

阅读提示:酷辣虫无法对本内容的真实性提供任何保证,请自行验证并承担相关的风险与后果!
本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 综合技术 » Execute SQL Server Script Files with the sqlcmd Utility

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录