http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/2013/06/12/sqlcmd-explicit-and-implicit-calls-examples/

Printed 2014/08/20 08:45PM

SQLCMD – Explicit and Implicit calls – Examples

By Prashanth Jayaram, 2013/06/12

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



Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.