Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

PowerSQL By Prashanth Jayaram

Technology enthusiast with 7+ years of experience in Database Technology. I am Microsoft Certified Professional with certificates of OCA, MCP, MCTS, MCITP developer, MCITP administration and backed with a degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication, PowerShell and Performance Tuning. Hobbies are Drawing, playing soccer and listening to Melodies songs.

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


Comments

Leave a comment on the original post [sqlpowershell.wordpress.com, opens in a new window]

Loading comments...