Sql server command line windows

The sqlcmd utility in SQL Server is a command-line tool that lets you submit T-SQL statements or batches to local and remote instances of SQL Server. The utility is extremely useful for repetitive database tasks such as batch processing or unit testing. It also provides an easy way of simulating load to a database under development.

To help you develop and test sqlcmd scripts, the query window in SQL Server Management Studio (SSMS) supports SQLCMD Mode, which lets you run most sqlcmd ‘colon’ directives as well as T-SQL statements. You can submit commands from within Windows script files (such as .bat), via sp_cmdshell, or through SQL Server Agent CmdExec jobs. You can also run sqlcmd within PowerShell. In addition, PowerShell includes, via SQLPS, a version of sqlcmd called invoke-sqlcmd that runs many, but not all, of the directives. This has the advantage of returning results as PowerShell objects.

In this article, I introduce you to the basics of the sqlcmd utility and discuss how to create scripts from within SSMS and at a Windows command prompt and how to run or schedule these scripts. Once you know how to use the utility within these environments, you’ll be able to create commands that you can add to your Windows script files, SQL Server Agent jobs, and PowerShell SQLPS applications.

The article includes a number of examples that demonstrate how to use sqlcmd. I developed these examples against a local instance of SQL Server 2012, with the AdventureWorks2012 database installed. However, you can use sqlcmd with any accessible SQL Server instance, from version 2005 on, and against any accessible database. Simply substitute the connection and database information in the examples, as appropriate, and you should have no problem following along.

NOTE: The sqlcmd utility replaces the osql utility in earlier versions of SQL Server. Although the utility is still supported in SQL Server 2012, Microsoft plans to remove osql in a future version of SQL Server and recommends that you avoid using it for current development.

Running sqlcmd in SQL Server Management Studio

If you’re already using SSMS regularly, as are most SQL Server developers and DBAs, you’ll find the SSMS query window a good place to start learning about sqlcmd. To run sqlcmd statements, you must change the query window to SQLCMD Mode by clicking the SQLCMD Mode option on the Query menu.

NOTE: You can also click the SQLCMD Mode button on the toolbar if the button is there. If it’s not, you’ll have to manually add it by customizing your toolbar. The exact method for doing so varies from one SQL Server version to the next, so refer to SQL Server Books Online for details specific to your version.

SSMS in SQLCMD Mode provides a handy environment for creating and testing your sqlcmd statements. Editing commands, for example, is much easier in SSMS than at a Windows command prompt. To get started in SSMS, connect to a SQL Server instance, open a query window, and switch to SQLCMD Mode. Then start typing.

When working in SQLCMD Mode, you can enter two types of statements: sqlcmd directives and T-SQL statements. The sqlcmd directives are a set of special commands you can use in conjunction with the T-SQL statements. Let’s look at an example to better understand how they work. The following set of statements delete the PersonData.txt file if it exists, connects to a local instance of SQL Server, retrieves data from the Person table in the AdventureWorks2012 database, and saves the output to a new copy of the PersonData.txt file:

We’ll go through this code line-by-line shortly, but first take a look at Figure 1. Notice that the sqlcmd directives are automatically highlighted in gray when you work in SQLCMD Mode. The T-SQL statements appear as they would in regular query mode.

Картинка с сайта: www.red-gate.com

Figure 1: Running command in SQLCMD Mode in SSMS

As you can see in Figure 1, a colon (:) precedes the sqlcmd directives. The colon is required for most of these commands. For a few of them, however, such as quit and exit, you can omit the colon. (This was done to support backward compatibility with the osql utility.) For details about this and other command-related issues, see the topic “sqlcmd Utility” in SQL Server Books Online. In the meantime, let’s look at the commands used in our example. The first one begins with double exclamation points (!!):

The exclamation points let you run a Windows operating system command, such as those you’d run directly at a command prompt. In this case, the command first uses if exists to verify the existence of the PersonData.txt file and then uses del to remove the file if it exists. (Note that each sqlcmd directive must run on its own line.)

The second line in our example uses the connect command to specify a connection to a SQL Server instance:

Although this example is specific to a local SQL Server instance, you can connect to any available instance for which you’ve been granted access. If that instance is the default one, you need only provide the server name. In addition, when you specify the server and, optionally, the instance name, sqlcmd uses Windows authentication to connect to SQL Server with your current account. However, you can also specify a username and password when connecting to an instance. To specify a username, add the -U switch followed by the username. To include a password, add the -P switch followed by the password. If you enter the username option without the password option, you’ll be prompted for a password.

The next sqlcmd directive in our example is out:

The command specifies that all query results should be redirected to an output file, in this case, the PersonData.txt file. If the file already exists when you run this commend, the file will be truncated. For this reason, you might not need to run a Windows command to first delete the file, as we do in the first line. It all depends on your specific needs.

Next in our example are the T-SQL statements that change the database context to AdventureWorks2012 and retrieve data from the Person table:

The two statements are both straightforward T-SQL. You simply include them along with your sqlcmd directives. When you run a block of code such as the one in our example, your commands and T-SQL statements are executed in the specified order. For this reason, after the connect command runs, you’ll see the following results in the output pane of the query window:

However, after the out command runs, query output is saved to the PersonData.txt file. The following results show what you’ll find in the file:

Of course, you don’t have to redirect your output to a file, but if you don’t, the results will appear in the output pane of the query window.

Running sqlcmd in Interactive Mode

As mentioned earlier, using SQLCMD Mode in SSMS can be useful for developing and testing your sqlcmd code. However, you can then run your commands at a Windows command prompt by using sqlcmd in interactive mode. To work in interactive mode, open a command prompt window and enter sqlcmd at the command prompt. The prompt’s drive letter will be replaced by a 1, which represents the first line where you start entering your commands. The lines are numbered sequentially until all the commands are submitted, and then the numbering starts again.

When you use sqlcmd to switch to interactive mode, the utility establishes a connection to the default instance of SQL Server. If your system includes only named instances or you want to connect to a specific instance, you must specify the instance by adding the –S switch and instance name, as shown in the following command:

In this case, I’ve used a period to represent the local computer, and I’m connecting to an instance named SqlSrv2012. But you can connect to any accessible instance.

Once you’ve entered interactive mode, you can type in your T-SQL statements. To add a line break after a statement or part of a statement, press Enter. The sqlcmd utility does not submit your T-SQL statements to SQL Server until you run the GO command. For example, suppose you enter the following statements at the sqlcmd command prompt:

The USE statement will change the database context to the AdventureWorks2012 database, and the SELECT statement will retrieve data from the database. However, these statements are not submitted to SQL Server until you type GO and then press Enter. The utility then submits the statements and returns the results to the command prompt window.

NOTE:  The GO command is a client-side ‘batch delimiter’ directive that is common to sqlcmd and SSMS. It is not a Transact SQL statement. it can be followed on the same line by the number of times you wish to run the preceding batch. Unless you specify this, it is just run once.

Figure 2 shows what the command prompt window looked like on my system after I switched to interactive mode and submitted the statements. Notice that with each line of code, the line numbers are incremented, but after the GO command runs, the command prompt returns to 1.

Картинка с сайта: www.red-gate.com

Figure 2: Running a sqlcmd query in interactive mode

In the examples we’ve looked at so far, our T-SQL has consisted primarily of data manipulation language (DML) statements. However, you’re not limited to DML when issuing T-SQL statements. You can run data definition language (DDL) statements, execute stored procedures and perform database maintenance. For example, you can just as easily run the following SELECT...INTO statement:

Once again, you enter each line and then submit the statements by using GO. Figure 3 shows what the command prompt window looked like on my system after adding the next set of commands.

Картинка с сайта: www.red-gate.com

Figure 3: Using sqlcmd in interactive mode to create a table

In this case, I typed nothing at the first line and simply pressed Enter to add space to my commands. Then I typed the T-SQL statements and entered GO. The results show that I changed the database context and that 273 rows were affected. If I had wanted, I could have then run a query against the new table to verify that it had been created correctly.

When using sqlcmd in interactive mode, you can also include sqlcmd directives, as we did in SSMS in SQLCMD Mode. For example, the following set of statements includes several sqlcmd directives:

The connect command establishes a connection to a SQL Server instance, and the out command specifies that any query results should be outputted to the PersonData.txt file. Next come the T-SQL statements, followed by the GO command. After all this, we issue an exit command, which tells sqlcmd to quit interactive mode and return the command prompt window to its normal state.

Running sqlcmd in Command Mode

Interactive mode is useful when you want to run ad hoc queries at a command prompt. However, where you’re likely to gain the most benefit from the sqlcmd utility is by creating complete commands that you can run at a command prompt (in what I refer to as command mode), add to your Windows script files, run as part of SQL Server Agent jobs, or use in other ways.

The best way to learn how to create these types of commands is to return once again to the command prompt window. However, rather than switch to interactive mode, we can simply run our commands at the default prompt. For example, the following command retrieves data from the AdventureWorks2012 database on a local named instance of SQL Server:

The first issue worth pointing out is that the command, as shown here, might appear on multiple lines when you view it. In reality, this is a single-line command that is wrapping across multiple lines here because of margin limitations. However, you do not press Enter until you’ve entered the entire command.

The command itself starts with sqlcmd to launch the utility and then uses the -S switch to specify the SQL Server instance, as we sometimes do when entering interactive mode. Next, we use the -d switch to specify the database. (Note that switch names are case-sensitive.) Finally, we use the -Q switch to define our T-SQL statement, enclosed in double quotes. In this case, we use an uppercase Q to run the statement and immediately exit the sqlcmd environment. If we use a lowercase q, we’d end up in interactive mode. Figure 4 shows what the command prompt window looks like after I ran the command.

Картинка с сайта: www.red-gate.com

Figure 4: Running sqlcmd in command mode in the Windows console

Rather than returning the results to the command window, we can instead use the -o switch to output the data to a text file, as shown in the following example:

This time, we’re returning a list of employees from the Person table and ordering the data by name. When we run the command, the sqlcmd utility adds the employee names to the file, without returning results to the command prompt.

The sqlcmd utility also lets us run script files that contain the statements we want to execute. For example, suppose we create a script file that contains the following SELECT statement:

In this case, I’ve named the file EmployeeQuery.sql and saved it to the C:\DataFiles folder. To run the statement, I create a command that uses the -i switch to call the EmployeeQuery.sql file, as shown in the following example:

Once again, the results are outputted to the Employees.txt file.

At times, you might want to create a script file that you can use in multiple situations, in which case you might want to be able to pass in variable values when calling the file. To do so, you add placeholders to your script file that let you pass in the values.

For example, suppose you want to be able to specify the person type and last name when querying the Person table. You can create a script file that contains a T-SQL statement similar to the following:

Notice that, to specify the PersonType value in the WHERE clause, I use $(type). The type value serves as the variable name, which can be referenced when calling the file. To use it as a variable, I’ve enclosed it in parentheses, preceded those with a dollar sign, and enclosed the entire thing in single quotes. I then follow the same convention for the name variable.

I saved the file as EmployeeQuery2.sql. Now, when I call the file within my command, I also include the -v switch and specify values for the type and name variables, as shown in the following example:

Notice that I use a single -v argument and specify a value enclosed in double quotes for each variable. Once again, the command returns data about two employees whose last name is Smith. The query results are outputted to the Employees2.txt file.

Working with the sqlcmd Utility

Not surprisingly, the sqlcmd utility can do a lot more than what I’ve demonstrated here. As mentioned earlier, the utility supports a number of sqlcmd directives in addition to those we’ve covered. The utility also supports many more switches than what we’ve used in the examples. Again, refer to the topic “sqlcmd Utility” in SQL Server Books Online for more details about commands and switches. You’ll also find plenty of useful information in the Simple-Talk workbench “The SQLCMD workbench,” written by Robyn Page and Phil Factor. What you’re likely to find is that the more you dig into the sqlcmd utility, the better you’ll appreciate what a valuable tool it can be, especially if you want to automate and schedule tasks related to local and remote instances of SQL Server.

Practical guide on how to use the SQL Server SQLCMD tool.

The tutorial about the SQLCMD examples is divided into different sections, providing a wide overview of various functionalities, use cases, and handy tips with the command line tool. It includes practical code examples to enhance understanding and automate daily DBA tasks.

SQLCMD is a command-line tool that allows you to interact with SQL Server efficiently. With the SQL Server SQLCMD tool, you can run Transact-SQL commands, system procedures, and SQL scripts. This invaluable tool provides a bridge between the user and SQL Server, allowing for direct communication and manipulation of databases.

1.1 Why use the SQL Server SQLCMD command line tool?

SQLCMD is not just a tool but a powerful asset in the hands of database administrators and developers. Through its streamlined interface, it brings the following advantages:

• Ease of Access: SQLCMD makes it simple to run and manage SQL queries directly from the command line.
• Automation Capability: You can script complex procedures, enabling a smooth workflow and reducing the room for error.
• Integration with Other Tools: It seamlessly integrates with various other SQL Server tools, enhancing functionality and efficiency.

1.2 How SQLCMD Works ?

The MS SQL command-line utility works as an interface to SQL Server. By accepting commands in Transact-SQL (T-SQL), it translates human-readable queries into actions that the SQL Server can perform. This includes executing commands, retrieving data, and even managing entire databases. The convenience, flexibility, and power of the SQLCMD tool make it an essential resource for anyone working with Microsoft SQL.

2. Installation and Configuration of SQLCMD

To embark on the journey of using SQLCMD, a couple of initial steps need to be undertaken. First and foremost, the installation of SQLCMD must be accomplished on your operating system. It’s a fairly straightforward process that can be executed by following the specific instructions provided for your OS version: Windows or Linux.

2.1 Difference between the Windows and the Linux configurations

The installation and configuration of SQLCMD vary between Windows and Linux. On Windows, SQLCMD can be installed as a part of the SQL Server Management Studio (SSMS) and is typically configured using a GUI interface. Commands can be run from the Command Prompt.

In contrast, Linux requires manual installation of the mssql-tools package and uses a terminal interface for configuration.

The command-line syntax may differ slightly between the two, such as the path conventions and scripting nuances. Despite these differences, both provide a powerful command-line tool for managing SQL Server instances.

2.2 Installing SQLCMD on Windows or Linux

The commands are the same for both Windows and Linux, as SQL Server’s command-line tools are designed to work consistently across different platforms.

# Windows
sqlcmd -S localhost -U sa -P password

# Linux
sqlcmd -S localhost -U sa -P password

3. SQLCMD examples with basic commands

Let’s dive into some basic commands to get familiar with the SQL Server SQLCMD tool.

3.1 Connecting to a Database

To connect to a specific database, use the following command:

sqlcmd -S server_name -d database_name -U username -P password

3.2 SQL CMD examples with queries

With SQLCMD, you can easily run SQL queries directly from the command line. Here’s an example:

sqlcmd -Q "SELECT * FROM Users"

4. SQL Server SQLCMD Advanced Use Cases

SQLCMD is not just limited to basic queries. It provides several advanced functionalities.

4.1 Using Variables with SQL Server SQLCMD

You can use variables within your SQL scripts. Here’s an example:

sqlcmd -v var_name="value" -Q "SELECT * FROM Table WHERE Column = @var_name"

SQLCMD provides a robust interface to interact with SQL Server, allowing for various advanced use cases. Below are some of them.

4.2 SQL CMD example to Query Multiple Databases

You can use SQLCMD to execute queries across multiple databases on the same server.

sqlcmd -S localhost -U sa -P password -d Database1 -Q "SELECT * FROM Table1; USE Database2; SELECT * FROM Table2;"

4.3 Running Scripts from Files

You can run a SQL script from a file using the -i option.

sqlcmd -S localhost -U sa -P password -i MyScript.sql

4.4 Outputting Query Results to a File

Redirect the output of your queries to a text file using the -o option.

sqlcmd -S localhost -U sa -P password -Q "SELECT * FROM MyTable;" -o output.txt

4.5 Connecting to a Named Instance

If you’re working with a named instance of SQL Server, specify it using the -S option.

sqlcmd -S localhost\InstanceName -U sa -P password

4.6 Utilizing Windows Authentication

You can connect to SQL Server using Windows Authentication by omitting the username and password.

sqlcmd -S localhost -E

4.7 Executing Commands with Variables

SQLCMD allows the use of variables within your scripts. You can declare and use them with the :SETVAR command.

sqlcmd -S localhost -U sa -P password -Q "EXEC sp_databases; :SETVAR MyVariable 'MyValue'; PRINT '$(MyVariable)';"

4.8 Implementing Transaction Control

You can manage transactions within your SQLCMD sessions by using the standard T-SQL transaction control statements like BEGIN TRANSACTION, COMMIT, and ROLLBACK.

sqlcmd -S localhost -U sa -P password -Q "BEGIN TRANSACTION; UPDATE Table1 SET Column1 = 'value' WHERE Column2 = 'condition'; COMMIT;"

4.9 Generate XML Output using the SQLCMD tool

You can generate XML output from a query by using the -y or -Y options with the -r option.

sqlcmd -S localhost -U sa -P password -Q "SELECT * FROM MyTable FOR XML AUTO;" -r 1 -o output.xml

5. SQLCMD Tips and Best Practices

To make the most of SQLCMD, follow these best practices:

1. Always close connections when not in use.
2. Use the -b switch to halt execution on errors.
3. Optimize scripts by using parameters.

Conclusion on SQLCMD examples

The SQLCMD examples offers an array of possibilities to manage SQL Server from the Windows command line. Explore different use cases, and don’t hesitate to delve into more complex operations.

https://expert-only.com/en/ms-dos/copy-windows-files-with-cmd/