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

Replication Record Count