SQLCMD - The SQL Server command line utility

Good night!

In this post I will talk a little more about SQLCMD, the SQL Server command line utility. As an Oracle DBA, I only used SQL * Plus, which is Oracle's command-line utility, to do my activities and found it very practical, lightweight, able to create highly customizable and easily executed scripts (@wait, @locks). , etc), 0% machine processor usage, and less than 20 MB memory.

I met SQLCMD when I was going to do some SQL Server database activity and thought that submitting execution evidence by taking screen print from SQL Management Studio was kind of weird. I was used to SQL * Plus, where I spooled and all the output of the executed commands and the return were written to an output text file, which I sent to the activity requesters as evidence.

Actually SQL * Plus is much more complete than SQLCMD, but that does not detract from SQLCMD, which was of great help to me on a demand where I needed to run an 500 MB SQL script to import data on a SQL Server basis. I tried by Management Studio some 10x, but it did not load the file or not. The solution was to resort to the well-known SQLCMD, which did the activity with 1 command line, no problem.

I won't go into too much, because there really are several and several SQLCMD parameters, in addition to commands from the tool itself to "program" it, like some macros, definition of variables, etc. I will focus only on the main features. If you feel like going deeper, I suggest visiting the Microsoft SQLCMD Page or check this link

Another interesting use for SQLCMD is mass script execution (batch), as I demonstrate in the article. SQL Server - How to Batch All .sql Scripts in a Folder or Directory by SQLCMD.

Connection Parameters

• -d : Used to specify the name of the database that the command will be executed. This parameter is currently obsolete and should be disabled in future versions, being replaced by the USE [bank] command.
• -S : Used to specify the connection server for SQL Server. If you want to connect to an instance other than the default or the server has a different port than the default (1433), use the syntax:
  sqlcmd -S \ , .
  Ex: sqlcmd -S home_server \ instanceTest, 1453
• -A: Allows you to connect to the server with a dedicated connection for the database administrator (Dedicated Administrator Connection - DAC)

Login Parameters

• -E: Attempts to connect to the server using Windows Authentication authentication mode. It is not necessary to inform the user or password, since the connection is made using the user logged on the machine, which is previously registered in Active Directory. If the -P or -U parameters are not entered, that parameter is used automatically.
• -U -P : Attempts to connect to the server using SQL Server Authentication authentication mode, where it is necessary to enter a user and password and this user must have been previously created in the server's user policies (No Management Studio, Security> Logins)

File Input and Output Parameters

• -i : Defines the input file to be executed by SQLCMD. If part of the path has spaces, quotation marks are used (I always use it, with space or not).
  Ex: sqlcmd -S pc-casa -i “C: \ My Files \ query.sql”
• -O : Defines the output file, where the output messages returned by SQLCMD will be recorded.
• -e: Set queries contained in the input file to appear in the output file as well.
• -u: Sets the output file to be written in Unicode format (UTF-8).

Query Parameters

• -q : Executes the query passed by parameter (use quotation marks). To execute more than one query, use a semicolon (;). This parameter cannot be used in conjunction with -i, as they are mutually exclusive.
• -Q : Do the same thing as the -q parameter, but after execution, close SQLCMD.
• -t : Defines the timeout time (in seconds) for the query to be executed. After exceeding the defined limit, the connection will be terminated, even if it has not been fully executed yet.
• -s : Defines the column separator character.
  Ex: sqlcmd -S pc-casa -s; -Q “select * from sys.sysobjects”
• -W: Removes trailing blanks.

Error Parameters

• -m : Defines at what level of error severity the messages will be written to the output file. Using parameter -1, all messages, even informational ones, will be recorded. This parameter does not accept spaces. -m-1 is valid, but -m -1 is not.
• -V : Defines the level of severity at which the returned messages will be treated as an error.

Miscellaneous Parameters

• - ?: Displays the SQLCMD HELP

Usage examples

It is also possible to execute queries normally in SQLCMD (then I think it is better to do it in Management Studio .. rs)

Setting the maximum width in 30 characters

Defining that there will be no blank spaces and the column separator will be the “;”

Defining that there will be no blank spaces, the column separator will be the “;” and without headers

That's it guys,
To the next!