Post reply

how to enable SQL CMD MODE using t-sql script

  • 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

  • May 27, 2009 at 2:06 am

    #1000396

    Check out :]

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

    Tanx 😀

  • 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)

  • 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.

  • 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)

  • 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??????

  • May 28, 2009 at 8:48 pm

    #1001868

    any one reply plzzzz

  • 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)

  • 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......

  • 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)

  • 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'

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply