A. Running a script by using sqlcmd
Start Notepad, and type the following Transact-SQL statements:
USE AdventureWorks;
GO
SELECT TOP (5) ContactID, FirstName, LastName
FROM Person.Contact;
GO
Create a folder named MyFolder and then save the script as the file MyScript.sql in the folder C:\MyFolder. Enter the following 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
When you view the contents of MyOutput.txt in Notepad, you will see the following:
Changed database context to 'AdventureWorks'.
ContactID FirstName LastName
----------- ----------- -----------
1 Syed Abbas
2 Catherine Abel
3 Kim Abercrombie
4 Humberto Acevedo
5 Pilar Ackerman
(5 rows affected)
B. Using sqlcmd with a dedicated administrative connection
In the following example, sqlcmd is used to connect to a server that has a blocking problem by using the dedicated administrator connection (DAC).
C:\>sqlcmd -S ServerName -A
1> SELECT blocked FROM sys.dm_exec_requests WHERE blocked <> 0;
2> GO
Here is the result set.
spid blocked
------ -------
62 64
(1 rows affected)
Use sqlcmd to end the blocking process.
1> KILL 64;
2> GO
C. Using 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 AdventureWorks;
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
SET NOCOUNT ON
SELECT EmailAddress
FROM Person.Contact
WHERE FirstName = @FirstName
AND LastName = @LastName;
SET NOCOUNT OFF
At the sqlcmd prompt, enter the following:
C:\sqlcmd
1> :Setvar FirstName Gustavo
1> :Setvar LastName Achong
1> EXEC dbo.ContactEmailAddress $(Gustavo),$(Achong)
2> GO
EmailAddress
-----------------------------
gustavo0@adventure-works.com
D. Using sqlcmd for database maintenance
The following example shows how to use sqlcmd for a database maintenance task. Create C:\BackupTemplate.sql with the following code.
USE master;
BACKUP DATABASE [$(db)] TO DISK='$(bakfile)';
At the sqlcmd prompt, enter the following:
C:\ >sqlcmd
1> :connect <server>
Sqlcmd: Successfully connected to server <server>.
1> :setvar db msdb
1> :setvar bakfile c:\msdb.bak
1> :r c:\BackupTemplate.sql
2> 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)
E. Using sqlcmd to execute code on multiple instances
The following code in a file shows a script that connects to two instances. Notice the GO before the connection to the second instance.
:CONNECT <server>\,<instance1>
EXEC dbo.SomeProcedure
GO
:CONNECT <server>\,<instance2>
EXEC dbo.SomeProcedure
GO
E. Returning XML output
The following example shows how XML output is returned unformatted, in a continuous stream.
C:\>sqlcmd -d AdventureWorks
1> :XML ON
1> SELECT TOP 4 FirstName + ' ' + LastName + ', '
2> FROM Person.Contact
3> GO
Gustavo Achong, Catherine Abel, Kim Abercrombie, Humberto Acevedo,
F. Using sqlcmd in a Windows script file
A sqlcmd command such as sqlcmd -i C:\InputFile.txt -o C:\OutputFile.txt, can be executed in a .bat file together with VBScript. In this case, do not use interactive options. sqlcmd must be installed on the computer that is executing the .bat file.
First, create the following four files:
-
C:\badscript.sql
SELECT batch_1_this_is_an_error
GO
SELECT 'batch #2'
GO
-
C:\goodscript.sql
SELECT 'batch #1'
GO
SELECT 'batch #2'
GO
-
C:\returnvalue.sql
:exit(select 100)
@echo off
C:\windowsscript.bat
@echo off
echo Running badscript.sql
sqlcmd -i badscript.sql -b -o out.log
if not errorlevel 1 goto next1
echo == An error occurred
:next1
echo Running goodscript.sql
sqlcmd -i goodscript.sql -b -o out.log
if not errorlevel 1 goto next2
echo == An error occurred
:next2
echo Running returnvalue.sql
sqlcmd -i returnvalue.sql -o out.log
echo SQLCMD returned %errorlevel% to the command shell
:exit
Then, at the command prompt, run C:\windowsscript.bat:
C:\>windowsscript.bat
Running badscript.sql
== An error occurred
Running goodscript.sql
Running returnvalue.sql
SQLCMD returned 100 to the command shell