how to enable SQL CMD MODE using t-sql script

• mohan.pariveda

  Ten Centuries

  Points: 1210

  More actions

  May 27, 2009 at 1:20 am

  #384661

  I created a job in that i am using some SQL CMD commands like :connect, :r ..etc,.

  before running this job i need to enable SQL CMD MODE at runtime.

  so anyone can please help me with the script..

  thanks

  Mohan

• Eswin

  SSCrazy Eights

  Points: 8320

  More actions

  May 27, 2009 at 2:06 am

  #1000396

  Check out :]

  http://msdn.microsoft.com/en-us/library/ms165702.aspx

  Tanx

• hi_abhay78

  SSCertifiable

  Points: 6265

  More actions

  May 27, 2009 at 3:45 am

  #1000439

  If you are using QA then : on the key board press ALT+Q and then m .

  in the Query menu there is SQLCMD mode option

  HTH

  Abhay Chaudhary
  Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

• mohan.pariveda

  Ten Centuries

  Points: 1210

  More actions

  May 27, 2009 at 7:02 am

  #1000551

  thanks but i want to do the same thing in script.

  that is i have to incert this script in the job.

• hi_abhay78

  SSCertifiable

  Points: 6265

  More actions

  May 27, 2009 at 11:02 am

  #1000844

  then there is no need to use the query analyzer .

  you can use SQLCMD using DOS prompt ...

  Abhay Chaudhary
  Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

• mohan.pariveda

  Ten Centuries

  Points: 1210

  More actions

  May 27, 2009 at 7:32 pm

  #1001172

  i have to enable SQLCMD at runtime. i.e., in the job.

  please let me know how to do this??????

• mohan.pariveda

  Ten Centuries

  Points: 1210

  More actions

  May 28, 2009 at 8:48 pm

  #1001868

  any one reply plzzzz

• hi_abhay78

  SSCertifiable

  Points: 6265

  More actions

  May 28, 2009 at 11:09 pm

  #1001888

  Step 1:

  create a batch file : for eg sqlcmd_start.bat and enter these lines :

  cd "Program Files"\"Microsoft SQL Server"\90\Tools\Binn

  sqlcmd.exe -E -Sabhay\Orion -q "use master"

  The above lines are 2 lines and not word wrapped to 2 lines stuff .You need to copy it as it is.

  change the servername\instancename .for default insttance need not to give any instance or just type mssqlserver

  Step 2 :

  Create a scheduled task and schedule a time for it .

  Abhay Chaudhary
  Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

• mohan.pariveda

  Ten Centuries

  Points: 1210

  More actions

  May 29, 2009 at 1:39 am

  #1001910

  thanks for reply.. it is very use full.

  I will explain you the actual problem what i m facing.

  Every monday i have to prepare the database Uptime & growth Report.

  i have written a script for getting report.

  Till now i am doing this task manually by logging into the each server & executing the script.

  Now we need to automate this task.

  I have saved the script on the desktop of my Box.

  File name is : Db_uptime.sql

  From my system i want run the below script:

  :connect server1

  :r " script file path( c:\Db_uptime.sql)"

  go

  :connect server2

  :r " script file "

  go

  .

  .

  .

  .

  .

  .

  .

  .

  :connect Server n

  :r " script file "

  By enabling SQLCMD in SSMS, i can able to run this script & i am getting the Output.

  But I want to automate the above (:connect ) script.

  The problem is how can i enable the SQLCMD at runtime??????????

  i.e,. before running the job it need to be enabled.

  I think you understand what i am saying......

• hi_abhay78

  SSCertifiable

  Points: 6265

  More actions

  May 29, 2009 at 3:26 am

  #1001946

  So you want to run :connect ...got it (if i have not let me know :-))

  -> create your script as usual including :connect commands and save it as .sql file .I did this test

  :connect abhay\Sherry

  waitfor delay '00:00:02'

  :connect abhay\Orion

  -> call the .sql file from SQLCMD using the bat file

  cd "Program Files"\"Microsoft SQL Server"\90\Tools\Binn

  sqlcmd.exe -E -Sabhay\Orion -ic:\abhay.sql

  @pause <---this is optional ...you actually dont need this .but can use it for testing purpose ..

  Regards

  Abhay Chaudhary
  Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

• Birdman-969830

  SSC Veteran

  Points: 260

  More actions

  June 24, 2009 at 1:18 pm

  #1014878

  If you mean in a job as in TSQL that you execute in Managment studio, why don't you set SQLCMD mode on by default?

  tools ->options -> Query Execution -> Enable 'by default, open new queries in SQLCMD mode'

• m2c2

  SSC Enthusiast

  Points: 117

  More actions

  October 24, 2024 at 9:36 pm

  #4473396

  Thank you!  Thank you!  Thank you!  Thank you!