SQLCMD – Explicit and Implicit calls – Examples

The scripting variables can be defined explicitly by using the setvar command or implicitly by using the sqlcmd -v option. Also, this is very helpful in Replication to check rowcount across publisher and subscriber articles.

Explicit Call-

SQLCMD can be enabled in Query Editor. Go to SSMS Menu -> Query ->Select SQLCMD.

:Connect hqvd0026
:SETVAR DB MyDB
CREATE DATABASE $(DB);
GO
:Connect hqvd0026
:SETVAR DB MyDB

USE $(DB)
CREATE TABLE dbo.MyTable(Col1 int);

Implict Call-

You can alternatively use SQLCMD variables in your script and pass the desired values at run time using command-line arguments.

Create a script file c:\blog\CreateDatabase.sql and paste below content

CREATE DATABASE $(DB);

Create a script file c:\blog\CreateObject.sql and paste the below content
USE $(DB)
CREATE TABLE dbo.MyTable(Col1 int);

You can then pass the database name like:Go to start -> run ->Type CMD

P:\>SQLCMD -i “c:\blog\CreateDatabase.sql” -v DatabaseName=DB -S AQDSPO8 -E

P:\>SQLCMD -i “c:\blog\CreateObject.sql” -v DatabaseName=DB -S AQDSPO8 -E

-v option to set a scripting variable that exists in a script
-S ServerName
-E Trusted Connection

Replication – Record count across publisher and subscriber

:Connect AQMSDP01
select count(*) from MES_PROD.dbo.tb_F4108_LotMaster
Go

:Connect AQMSRP01
select count(*) from MES_REPL_Rpt.dbo.tb_F4108_LotMaster

Reolication Record Count

Replication Record Count

Advertisements

About Prashanth Jayaram

DB Technologist, Author, Blogger, Service Delivery Manager at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek My Profile: https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ http://codingsight.com/author/prashanthjayaram/ https://www.red-gate.com/simple-talk/author/prashanthjayaram/ http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ Connect Me: Twitter @prashantjayaram GMAIL powershellsql@gmail.com The articles are published in: http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
Aside | This entry was posted in SQL, T-SQL, Variable Passing and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s