| SQLCMD |
All T-SQL statements are held in the statement cache until a batch terminator is encountered. The RESET command clears the statement cache.
The statement cache can also be cleared by typing the ^C escape sequence at the console. However, if this sequence is used when the cache is empty, the entire SQLCMD execution session is terminated.
It is safe to mix SQLCMD command with T-SQL statements in the same batch because they all get pre-processed before T-SQL is submitted to tor any given batch, all SQLCMD commands are pre-processed and only after that is T-SQL sent to the server. It means he server. However, you should be aware that sometimes you can get unexpected results if you try doing too many things in the same batch. Consider, for example, the SETVAR command mentioned earlier that defines a scripting variable.
use AdventureWorks
:setvar tablename Person.Address
select * from $(tablename)
:setvar tablename HumanResources.Department
select * from $(tablename)
go
One could expect that the first select would be from Person.Address and the second one from HumanResources.Department. However, that’s not the case. As already mentioned, all SQLCMD commands in a given batch are pre-processed first, and only after that is the resulting T-SQL sent to the server. In this example, both selects are from HumanResources.Department. To fix that, put the batch separator (go in the default case) between the two SETVAR commands:
:setvar tablename Person.Address
select * from $(tablename)
go
:setvar tablename HumanResources.Department
select * from $(tablename)
declare @Dt_DateAndTime Varchar(100), @backupfile sysname, DECLARE @ThreeDaysAgo VARCHAR(50)
select @Dt_DateAndTime = convert(char(8), getdate(), 112)+”+ Replace(convert(char(12), getdate(), 14),’:',”)
select @Dt_DateAndTime
select @backupfile = ‘\\ServerName\c$\backup\Filename’+@Dt_DateAndTime+’.bak’
backup database <dbname>
to disk = @backupfile
with init
SELECT @ThreeDaysAgo = CAST(DATEADD(d, -3, GETDATE()) AS VARCHAR)
EXECUTE master.dbo.xp_delete_file 0,N’D:\Backups\db’,N’bak’,@ThreeDaysAgo
As mentioned earlier, in addition to command line options, SQLCMD also supports many powerful commands. A SQLCMD command always starts with a new line and is preceded by a colon. A colon is required to make a clear distinction between a SQLCMD command and a T-SQL script command. However, for backward compatibility with OSQL, some legacy commands are recognized without the colon prefix.
It’s worth noting that SQLCMD commands (unlike command line options) are not case sensitive.
1>:help
:!! [<command>]
- Executes a command in the Windows command shell.
:connect server[\instance] [-l timeout] [-U user [-P password]]
- Connects to a SQL Server instance.
:ed
- Edits the current or last executed statement cache.
:error <dest>
- Redirects error output to a file, stderr, or stdout.
:exit
- Quits SQLCMD immediately.
:exit()
- Execute statement cache; quit with no return value.
:exit(<query>)
- Execute the specified query; returns numeric result.
go [<n>]
- Executes the statement cache (n times).
:help
- Shows this list of commands.
:list
- Prints the content of the statement cache.
:listvar
- Lists the set SQLCMD scripting variables.
n error [exit|ignore]
- Action for batch or SQLCMD command errors.
ut <filename>|stderr|stdout
- Redirects query output to a file, stderr, or stdout.
:perftrace <filename>|stderr|stdout
- Redirects timing output to a file, stderr, or stdout.
:quit
- Quits SQLCMD immediately.
:r <filename>
- Append file contents to the statement cache.
:reset
- Discards the statement cache.
:serverlist
- Lists local and SQL Servers on the network.
:setvar {variable}
- Removes a SQLCMD scripting variable.
:setvar <variable> <value>
- Sets a SQLCMD scripting variable.
1>
SQLCMD Console
1. Start RUN command
2. Type in SQLCMD — it will start prompt in SQLCMD mode
3. Paste the following: :serverlist
CMD Console
1. Start RUN command
2. Type in CMD — it will start prompt in CMD mode
3. Paste the following: sqlcmd -Lc > c:\serverlist.txt
4. This will create output file on the C:\ drive
You can use sqlcmd to execute a simple stored procedure:
SQLCMD.EXE -S ServerName -E -d DatabaseName -Q “EXEC dbo.proc_name”
1. Executing a script file from SQLCMD.
i. Create a script file by typing in the following line and save it as Message.sql
print ‘Welcome today”s date is: ‘ + (convert(varchar, getdate()))
ii. Now goto command prompt and type:
SQLCMD -i Message.sql
here, -i is the switch to specify the input file name.
2. Executing series of script files (sample)
Lets create couple of .sql files and then see how to execute them in order from command prompt. Please note that i am just showing an example here 
there are better methods of doing the same which I would explain later in the series!
i) Copy paste the below script and name it as 01TableCreation.sql
Create table tblTest
(
Sno int identity,
FName varchar(20)
)
Go
ii) Copy paste the below script and name it as 02InsertRecords.sql
Insert into tblTest (Fname) values (‘a’)
iii) Copy paste the below two lines and name it as test.bat
sqlcmd -U sa -P hotmail -S SomeServer -d testbed -i “C:\Files\SQL
Related\Scripts\sqlcmd\01TableCreation.sql”
sqlcmd -U sa -P hotmail -S SomeServer -d testbed -i “C:\Files\SQL
Related\Scripts\sqlcmd\02InsertRecords.sql”
here,
-U is SQL User name
-P is SQL Password
-S is the SQL Server name
-d is the SQL database name
-i is the input file to execute
iv) Execute the batch file
Now goto command prompt (Start >> Run >> cmd) and execute this batch file. The batch file would have created a table and inserted a record into it. If you want to deploy some DB scripts on a remote box for which you don’t have access from Mgmt Studio you can follow this batch file route. So that the ppl who are having access to that SQL box can just run this batch file (after changing the values of the different switches, if need be)
First get connected into the DB Server using SQLCMD using either the windows authentication or SQL authentication as explained in the previous post.
Then on the prompt you can type in the TSQL queries directly and press enter. In the next line, say Go and press enter to execute the query and see the result.
After displaying the result the cursor would stand on the prompt expecting for further queries from us
By default, it get’s connected to the default database of that login only. One can make use of -d switch to connect to a DB of their choice. Please refer the below sample where I make use of Adventureworks DB and query a table.
What is the difference between -q and -Q switch?
-q is for running queries from SQLCMD.
-Q is for exitting from SQLCMD immediately after executing a given query.
SQLCMD -q “Select getdate()”
This query will print the current datetime and then return to SQLCMD prompt.
SQLCMD -Q “Select getdate()”
This query will print the current datetime and then exit from SQLCMD automatically.
There are instances where we might need to run some default scripts on a specified server once SQLCMD gets connected. It can be achieved in just three steps as explained below:
Step 1: Create a script file which you wanted to fire when SQLCMD gets connected to your SQL Server.
For keeping the example simple, I used the following line and saved it as SqlCmdStartUpScripts.sql
print ‘Welcome today”s date is: ‘ + (convert(varchar, getdate()))
Step 2: Open DOS prompt and type
set sqlcmdini=c:\vadivel\sql related\scripts\SqlCmdStartUpScripts.sql
Step 3: Then type
SQLCMD
and press Enter.
I frequently create scripts that back up a database on one server and restore it on another server. With sqlcmd you can achieve the whole thing in a single script file because you can connect to both source and destination server. The following script backs up the Northwind database on one server and restores it on another server:
– connect to the source server
:CONNECT rrehak\sql2000
BACKUP DATABASE Northwind
TO DISK = ‘C:\Temp\Northwind.bak’
WITH INIT
GO
– connect to the destination server
:CONNECT rrehak
– if the database exists, disconnect any
possible connections
IF EXISTS (SELECT * FROM
master.dbo.sysdatabases WHERE name =
‘Northwind’)
ALTER DATABASE Northwind
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE [Northwind]
FROM DISK = N’C:\Temp\Northwind.bak’
WITH FILE = 1,
MOVE N’Northwind’ TO N’C:\SQL Server
Databases\Northwind.mdf’,
MOVE N’Northwind_log’ TO N’C:\SQL Server
Databases\Northwind_1.ldf’,
NOUNLOAD, STATS = 10
GO
Here’s one more improvement to the script above, which demonstrates how to execute operating system commands from sqlcmd. The demo script restored the database from the location where the source database was backed up. Let’s say you want to copy a backup file to a folder on the destination server because you’ll need to restore the file repeatedly and want to avoid restoring over the network. Sqlcmd can execute DOS commands using the :!! directive. In this case, place the following script after the backup section and before the restore section to execute the DOS copy command for creating a copy of the backup file:
:setvar CopyCommand “copy
\ \sqlserver1\c$\Temp\Northwind.bak
\ \sqlserver2\c$\sql”
:!! $(CopyCommand)
All of these features make sqlcmd the tool of choice for automating SQL Server administration and maintenance tasks. For example, you can create a “worker” script file that contains a common set of SQL scripts that need to execute in multiple databases, possibly on multiple servers as well. Inside of that script you would use sqlcmd variables.
| SQL Jobs |