SQL CMD:
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.
Installing SQLCMD on Windows or Linux
# Windows
sqlcmd -S localhost -U sa -P
password
# Linux
sqlcmd -S localhost -U sa -P password
Syntax:
sqlcmd -S
yourservername\instancename -d database_name -o outputfilename_withpath -Q "your select query"
Example:
In this example, The out put of SQL
query stored in to the text file. Please go through below document get more information.
Switches are as follows (few more commands are listed
below.=)
-S for server name and instance
name
-U user name '
-P passwoes
-d
for source database
-i specifies the input file
-o for target output file (it will
create output file)
-Q
for query to fetch data
-E indicates a trusted connection
using Windows
-l option to set login timeouts
-t option to set query timeouts
To determine the installed
version, run the following statement at the command line:
sqlcmd -?
[-S server] [-H
hostname] [-E trusted
connection]
[-N Encrypt Connection][-C Trust Server Certificate]
[-d use database name] [-l login timeout] [-t query timeout]
[-h headers] [-s
colseparator] [-w screen width]
[-a packetsize] [-e echo
input] [-I Enable Quoted
Identifiers]
[-c cmdend] [-L[c] list
servers[clean output]]
[-q "cmdline query"]
[-Q "cmdline query" and exit]
[-m errorlevel] [-V
severitylevel] [-W remove trailing
spaces]
[-u unicode output] [-r[0|1]
msgs to stderr]
[-i inputfile] [-o
outputfile] [-z new password]
[-f <codepage> | i:<codepage>[,o:<codepage>]] [-Z new
password and exit]
[-k[1|2] remove[replace] control characters]
[-y variable length type display width]
[-Y fixed length type display width]
[-p[1] print statistics[colon format]]
[-R use client regional setting]
[-K application intent]
[-M multisubnet failover]
[-b On error batch abort]
[-v var = "value"...]
[-A dedicated admin connection]
[-X[1] disable commands, startup script, environment variables [and
exit]]
[-x disable variable substitution]
[-j Print raw error messages]
[-g enable column encryption]
[-G use Azure Active Directory for authentication]
[-? show syntax summary]
Examples: (SQLCMD examples with basic commands)
Connect to a default instance
by using Windows Authentication to interactively run T-SQL statements:
Syntax: sqlcmd -S
<ComputerName>
sqlcmd -S
<ComputerName>\<InstanceName>
sqlcmd -S .\<InstanceName>
To connect to a specific
database, use the following command:
sqlcmd -S server_name -d
database_name -U username -P password
sqlcmd -q "SELECT * FROM
AdventureWorks2022.Person.Person"
SQLCMD is not just limited to basic queries. It provides several advanced functionalities.
sqlcmd -v
var_name="value" -Q "SELECT * FROM Table WHERE Column =
@var_name"
Running Scripts from Files
sqlcmd -S localhost -U sa -P
password -i MyScript.sql
Outputting Query Results to a
File
sqlcmd -S localhost -U sa -P
password -Q "SELECT * FROM MyTable;" -o output.txt
sqlcmd -S
DESKTOP-5K4TURF\SQLEXPRESS -E -i c:\sql\columns.sql -o c:\sql\output.txt
sqlcmd -Q "SELECT * FROM
AdventureWorks2022.Person.Person" -o MyOutput.txt
Connecting to a Named Instance:
sqlcmd -U MyLogin -S
<ComputerName>\<InstanceName>
sqlcmd -S localhost\InstanceName
-U sa -P password
Utilizing Windows
Authentication
sqlcmd -S localhost -E
Executing Commands with
Variables
sqlcmd -S localhost -U sa -P
password -Q "EXEC sp_databases; :SETVAR MyVariable 'MyValue'; PRINT
'$(MyVariable)';"
Implementing Transaction
Control
sqlcmd -S localhost -U sa -P
password -Q "BEGIN TRANSACTION; UPDATE Table1 SET Column1 = 'value' WHERE
Column2 = 'condition'; COMMIT;"
Generate XML Output using the
SQLCMD tool
sqlcmd -S localhost -U sa -P
password -Q "SELECT * FROM MyTable FOR XML AUTO;" -r 1 -o output.xml
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.
Other Commands:
sqlcmd
-a packet_size
-A (dedicated administrator connection)
-b (terminate batch job if there is an error)
-c batch_terminator
-C (trust the server certificate)
-d db_name
-D
-e (echo input)
-E (use trusted connection)
-f codepage | i:codepage[,o:codepage] | o:codepage[,i:codepage]
-g (enable column encryption)
-G (use Azure Active Directory for authentication)
-h rows_per_header
-H workstation_name
-i input_file
-I (enable quoted identifiers)
-j (Print raw error messages)
-k[1 | 2] (remove or replace control characters)
-K application_intent
-l login_timeout
-L[c] (list servers, optional clean output)
-m error_level
-M multisubnet_failover
-N (encrypt connection)
-o output_file
-p[1] (print statistics, optional colon format)
-P password
-q "cmdline query"
-Q "cmdline query" (and exit)
-r[0 | 1] (msgs to stderr)
-R (use client regional settings)
-s col_separator
-S [protocol:]server[instance_name][,port]
-t query_timeout
-u (unicode output file)
-U login_id
-v var = "value"
-V error_severity_level
-w screen_width
-W (remove trailing spaces)
-x (disable variable substitution)
-X[1] (disable commands, startup script, environment variables, optional
exit)
-y variable_length_type_display_width
-Y fixed_length_type_display_width
-z new_password
-Z new_password (and exit)
-? (usage)
--this is the reference link
Quoted strings:
sqlcmd
PRINT "Length: 5"" 7'";
GO
Strings that span multiple
lines
SELECT <First line>
FROM <Second line>
WHERE <Third line>;
GO
Run a script by using
sqlcmd
Start Notepad, and type the
following T-SQL statements:
USE AdventureWorks2022;
GO
SELECT TOP (3) BusinessEntityID,
FirstName, LastName
FROM Person.Person;
GO
Create a folder named MyFolder and
then save the script as the file person_person_select.sql in the
folder C:\MyFolder. Enter the following command at the command prompt to
run the script and put the output in MyOutput.txt in MyFolder:
sqlcmd -i C:\MyFolder\MyScript.sql
-o C:\MyFolder\MyOutput.txt
Use sqlcmd with a dedicated
administrative connection
\Temp\>sqlcmd -S ServerName -A
SELECT session_id, blocking_session_id FROM `sys.dm_exec_requests`
WHERE blocking_session_id <> 0;
GO
KILL 64;
GO
Use sqlcmd to execute a stored
procedure
The following example shows how to
execute a stored procedure by using sqlcmd. Create the following
stored procedure.
USE AdventureWorks2022;
GO
IF OBJECT_ID('dbo.ContactEmailAddress',
'P') IS NOT NULL
DROP PROCEDURE dbo.ContactEmailAddress;
GO
CREATE PROCEDURE dbo.ContactEmailAddress
(
@FirstName NVARCHAR(50),
@LastName NVARCHAR(50)
)
AS
BEGIN
SET NOCOUNT ON;
SELECT EmailAddress FROM Person.person
p
INNER JOIN Person.EmailAddress e
ON p.FirstName = @FirstName
AND p.LastName= @LastName
END
GO
At the sqlcmd prompt, enter the
following:
1> sqlcmd
2> :Setvar FirstName Ken
2> :Setvar LastName Sánchez
2> EXEC dbo.ContactEmailAddress
$(FirstName),$(LastName)
3> GO
Msg 2812, Level 16, State 62,
Server TSHYHJBN\MSSQLSERVER2022, Line 1
Could not find stored procedure
'sqlcmd'.
EmailAddress
--------------------------------------------------
ken3@adventure-works.com
ken0@adventure-works.com
Use sqlcmd for database
maintenance
The following example shows how to
use sqlcmd for a database maintenance task. Create C:\Temp\BackupTemplate.sql
with the following code.
SQL
USE master;
BACKUP DATABASE [$(db)] TO
DISK='$(bakfile)';
At the sqlcmd prompt, enter the
following code:
Windows Command Prompt
C:\Temp\>sqlcmd
:connect <server>
Sqlcmd: Successfully connected to
server <server>.
:setvar db msdb
:setvar bakfile C:\Temp\msdb.bak
:r C:\Temp\BackupTemplate.sql
GO
Changed database context to
'master'.
Processed 688 pages for database
'msdb', file 'MSDBData' on file 2.
Processed 5 pages for database
'msdb', file 'MSDBLog' on file 2.
BACKUP DATABASE successfully
processed 693 pages in 0.725 seconds (7.830 MB/sec)
Use sqlcmd to execute code on
multiple instances
:CONNECT
<server>\,<instance1>
EXEC dbo.SomeProcedure
GO
:CONNECT
<server>\,<instance2>
EXEC dbo.SomeProcedure
GO
Return XML output
Q:\>sqlcmd -S
TSHYHJBN\MSSQLSERVER2022 -d AdventureWorks2022
:XML ON
SELECT TOP 3 FirstName + ' ' + LastName + ', '
FROM Person.Person
GO
Reference Linke:
Use
sqlcmd - SQL Server | Microsoft Learn
No comments:
Post a Comment