sqlcmd with code examples

SQL Server provides the sqlcmd command-line utility to run Transact-SQL (T-SQL) scripts and manage SQL Server instances. The sqlcmd utility allows you to interact with SQL Server from the command prompt, and it enables you to run T-SQL scripts and commands. This article will provide an overview of the sqlcmd utility and several code examples to demonstrate its usage.

Overview of sqlcmd
The sqlcmd utility is installed with SQL Server and is located in the \Program Files\Microsoft SQL Server\140\Tools\Binn folder. To use sqlcmd, you need to open a Command Prompt window and navigate to the folder where sqlcmd is installed. Once you are in the folder, you can run the sqlcmd command with various options and parameters.

Connecting to a SQL Server Instance
To connect to a SQL Server instance, you need to specify the server name and the authentication method. The basic syntax for connecting to a SQL Server instance is as follows:

sqlcmd -S server_name -U user_name -P password

For example, to connect to a SQL Server instance named "SQL01" using a Windows authentication, the command would be:

sqlcmd -S SQL01 -E

Running T-SQL Scripts
Once connected to a SQL Server instance, you can run T-SQL scripts by specifying the script file name with the -i option. The basic syntax for running a T-SQL script is as follows:

sqlcmd -S server_name -U user_name -P password -i script_file

For example, to run a script named "script.sql" on the "SQL01" instance, the command would be:

sqlcmd -S SQL01 -U sa -P mypassword -i script.sql

Executing T-SQL Commands
You can also execute T-SQL commands directly from the command prompt by using the -Q option. The basic syntax for executing T-SQL commands is as follows:

sqlcmd -S server_name -U user_name -P password -Q "T-SQL command"

For example, to display the current date and time on the "SQL01" instance, the command would be:

sqlcmd -S SQL01 -U sa -P mypassword -Q "SELECT GETDATE()"

Exporting Query Results to a CSV File
You can export query results to a CSV file by using the -o option. The basic syntax for exporting query results to a CSV file is as follows:

sqlcmd -S server_name -U user_name -P password -Q "T-SQL query" -o output_file.csv

For example, to export the results of a query that selects all rows from the "Employees" table to a CSV file named "employees.csv", the command would be:

sqlcmd -S SQL01 -U sa -P mypassword -Q "SELECT * FROM Employees" -o employees.csv

Conclusion
The sqlcmd utility is a powerful tool that allows you to interact with SQL Server from the command prompt. With the examples provided, you can now run T-SQL scripts, execute T-SQL commands, and export query results to a CSV file using the sqlcmd utility. Keep in mind that the examples above are just a small subset of the functionality that sqlcmd provides, and you can use the many
Additional sqlcmd options

• -b : Allows you to exit sqlcmd if an error occurs.
• -d : Allows you to specify the database to connect to when connecting to a SQL Server instance.
• -h : Allows you to specify the maximum number of rows that will be displayed in the query results.
• -x : Allows you to display the column names in the query results.
• -X : Allows you to disable the execution of the T-SQL commands in a script and only show the commands that will be executed.
• -v : Allows you to define variables that can be used in T-SQL scripts and commands.

Advanced usage of sqlcmd

• Stored Procedures : You can execute stored procedures by using the -Q option and passing the stored procedure name as a parameter. For example, to execute a stored procedure named "usp_GetEmployees" on the "SQL01" instance, the command would be:

sqlcmd -S SQL01 -U sa -P mypassword -Q "EXEC usp_GetEmployees"

• Output Parameters : You can also use sqlcmd to execute stored procedures that have output parameters. To capture the output parameter value, you can use the -v option to define a variable and then use the variable in the T-SQL command. For example, to execute a stored procedure named "usp_GetEmployeeCount" on the "SQL01" instance, the command would be:

sqlcmd -S SQL01 -U sa -P mypassword -Q "DECLARE @count INT; EXEC usp_GetEmployeeCount @count OUTPUT; SELECT @count"

• sqlcmd scripting: You can use the -i option to execute multiple T-SQL scripts in sequence. For example, you can create a master script file that calls other script files in sequence. This can be useful for automating repetitive tasks or creating a deployment script for a database.

Conclusion

The sqlcmd utility is a powerful and flexible tool that enables you to interact with SQL Server from the command prompt. With the examples and additional information provided, you can now run T-SQL scripts, execute T-SQL commands, export query results to a CSV file, execute stored procedures, capture output parameters and even automate repetitive tasks with sqlcmd scripting. Remember to always test your commands and scripts in a non-production environment before running them in production to avoid any potential data loss or errors.

Popular questions

1. What is the purpose of the sqlcmd utility?
   Answer: The sqlcmd utility allows you to interact with SQL Server from the command prompt. It enables you to run T-SQL scripts, execute T-SQL commands, export query results to a CSV file, execute stored procedures, capture output parameters, and automate repetitive tasks.

2. How do you connect to a SQL Server instance using sqlcmd?
   Answer: To connect to a SQL Server instance, you can use the -S option followed by the server name or IP address and -U option followed by the username and -P option followed by the password. For example: sqlcmd -S SQL01 -U sa -P mypassword

3. How do you export query results to a CSV file using sqlcmd?
   Answer: To export query results to a CSV file, you can use the -o option followed by the file name. For example: sqlcmd -S SQL01 -U sa -P mypassword -Q "SELECT * FROM Employee" -o "C:\Employee.csv" -s,

4. How do you execute a stored procedure using sqlcmd?
   Answer: To execute a stored procedure, you can use the -Q option and pass the stored procedure name as a parameter. For example, to execute a stored procedure named "usp_GetEmployees" on the "SQL01" instance, the command would be: sqlcmd -S SQL01 -U sa -P mypassword -Q "EXEC usp_GetEmployees"

5. How do you capture the output parameter value of a stored procedure using sqlcmd?
   Answer: To capture the output parameter value, you can use the -v option to define a variable and then use the variable in the T-SQL command. For example, to execute a stored procedure named "usp_GetEmployeeCount" on the "SQL01" instance, the command would be: sqlcmd -S SQL01 -U sa -P mypassword -Q "DECLARE @count INT; EXEC usp_GetEmployeeCount @count OUTPUT; SELECT @count"

Tag

SQL