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 will not extend too much, because there are actually many and many parameters of SQLCMD, besides commands of the tool itself to "program" in it, like some macros, variable definition, etc. I will focus only on key features. If you want to dig deeper, I suggest visiting the Microsoft SQLCMD Page or check this link
- -d <database>: Specifies the name of the database that the command will execute. This parameter is currently obsolete and should be deactivated in future versions, being replaced by the USE command [bank].
- -S <server>: Serves to specify the connecting server of SQL Server. If you want to connect to a non-default instance or the server has a non-default port (1433), use the syntax:
sqlcmd -S <server> \ <instance>, <port>.
Ex: sqlcmd -S home_server \ instanceTest, 1453
- -A: Lets you connect to the server with a dedicated Dedicated Administrator Connection (DAC) connection
- -E: Attempts to connect to the server using Windows Authentication authentication mode. It is not necessary to enter the username or password, since the connection is made using the user logged in the machine, which is previously registered in Active Directory. If the -P or -U parameters are not entered, this parameter is used automatically.
- -U <user> -P <password>: Attempts to connect to the server using SQL Server Authentication authentication mode, where a user and password must be entered and that user must have been previously created in the server management policies (No Management Studio, Security> Logins)
File Input and Output Parameters
- -i <file>: Defines the input file to be executed by SQLCMD. If part of the path has spaces, use quotation marks (I always use spaces or not).
Ex: sqlcmd -S pc-casa -i “C: \ My Files \ query.sql”
- -o <file>: Defines the output file, where output messages returned by SQLCMD will be written.
- -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).
- -q <query>: Execute the query passed by parameter (use quotes). To execute more than one query, use the semicolon (;). This parameter cannot be used in conjunction with -i as they are mutually exclusive.
- -Q <query>: Does the same thing as the -q parameter, but after execution, closes SQLCMD.
- -t <seconds>: Defines the timeout time (in seconds) of the query to be executed. After exceeding the set limit, the connection will be terminated even if it has not yet been completely executed.
- -s <character>: Defines the column separator character.
Ex: sqlcmd -S pc-home -s; -Q “select * from sys.sysobjects”
- -W: Removes trailing blanks.
- -m <error level>: Defines from which error severity level messages will be written to the output file. Using the -1 parameter, all messages, even informational ones, will be recorded. This parameter does not accept spaces. -m-1 is valid, but -m -1 is not.
- -V <error level>: Defines from which severity level returned messages are treated as error.
- - ?: Displays the SQLCMD HELP
-- Conecta no servidor "servidor-casa", utilizando o usuário "dirceu", senha "resende", executa o script "teste.sql" e grava o resultado e o próprio arquivo de entrada no arquivo teste.log
sqlcmd -S servidor-casa -U dirceu -P resende -i "C:\Meus Dados\SQL\teste.sql" -e -o "C:\Meus Dados\SQL\teste.log"
-- Conecta no servidor "servidor-casa" utilizando autenticação Windows e executa o script "teste.sql"
sqlcmd -S servidor-casa -i "C:\Meus Dados\SQL\teste.sql"
-- Conecta no servidor "servidor-casa", instância "instanciaTeste", porta 1453. Executa uma query no servidor, onde o tempo de expiração é de 60 segundos. O resultado da query virá separado por ";" e não terá espaços em branco.
sqlcmd -S servidor-casa\instanciaTeste,1453 -q "SELECT TOP 10 * FROM sys.sysobjects" -t 60 -s ; -W
-- Conecta no servidor "servidor-casa", executa uma query no servidor, onde o tempo de expiração é 30 segundos. O resultado da query virá separado por ";", não terá espaços em branco e será gravado no arquivo "saida.log"
sqlcmd -S servidor-casa -q "SELECT * FROM sys.sysobjects" -t 30 -s ; -W -e -o "C:\saida.log"
It is also possible to execute queries normally in SQLCMD (then I think it is better to do it in Management Studio .. rs)
That's it guys,
To the next!