Hopefully a helpful collection of examples of using SQLCMD in SSMS

Is SQLCMD Enabled:

enabled –
SQLCMD01

not enabled –
SQLCMD02

How to enable SQLCMD for a single query:

SSMS -> Query -> SQLCMD Mode

SQLCMD03

 

How to enable SQLCMD in SSMS by default:

SSMS -> Tools -> Options -> Query Execution -> Tick “By default, open new queries in SQLCMD mode

SQLCMD04

 

Query examples for SSMS SQLCMD:

/*

Query - SQLCMD Examples

*/

-- Use Variables throughout different batchs
:SETVAR Filter "GBR"
:SETVAR Top 10


-- Load scripts 
:R c:Temp\Script.sql


-- Connect to SQL Instance
:SETVAR  Server "DEVSQL01"
:CONNECT $(Server)
SELECT @@SERVERNAME


-- Output Results to file
:OUT C:\Temp\Results.txt
SELECT name FROM sys.databases


-- Output Errors (lost if no put is used)
:SETVAR Folder "C:\Temp\"
:SETVAR Errors "Errors.txt"
:ERROR $(Folder)$(Errors)
SELECT 1/0


-- Stop on Error
:ON ERROR EXIT
IF NOT EXISTS ( SELECT  *
                FROM    sys.objects
                WHERE   object_id = OBJECT_ID(N'[dbo].[SOMETABLE]')
                        AND type IN ( N'U' ) )
    RAISERROR ('This is not a Valid Instance Database', 15, 10);
GO

PRINT 'Keep Working';


-- Ignore Errors and continue
:ON ERROR IGNORE
IF NOT EXISTS ( SELECT  *
                FROM    sys.objects
                WHERE   object_id = OBJECT_ID(N'[dbo].[SOMETABLE]')
                        AND type IN ( N'U' ) )
    RAISERROR ('This is not a Valid Instance Database', 15, 10);
GO

PRINT 'Keep Working';


-- Clears all SQLCMD Variables
:SETVAR  Var "SQLCMD Variable"
PRINT '$(Var)'
:RESET
PRINT '$(Var)' -- no value returned 
PRINT 'End script'


-- Quit a SQLCMD Script
:SETVAR  Var "SQLCMD Variable"
PRINT '$(Var)'
:QUIT
PRINT 'End script' -- NO Data returned from either command!


-- Exit SQLCMD scripts
:SETVAR  Var "SQLCMD Variable"
PRINT '$(Var)'
:EXIT
PRINT 'End script' -- NO Data returned from either command!

-- Use CMD via SQLCMD without xp_
!! DIR C:\Temp



 

 

 

Advertisements