Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

how to enable SQL CMD MODE using t-sql script Expand / Collapse
Author
Message
Posted Wednesday, May 27, 2009 1:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 2, 2010 9:07 AM
Points: 34, Visits: 128
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
Post #723828
Posted Wednesday, May 27, 2009 2:06 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 28, 2013 8:50 AM
Points: 1,606, Visits: 1,039
Check out :]


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




Tanx
Post #723856
Posted Wednesday, May 27, 2009 3:45 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, June 13, 2014 12:27 PM
Points: 267, Visits: 386
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)
Post #723907
Posted Wednesday, May 27, 2009 7:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 2, 2010 9:07 AM
Points: 34, Visits: 128
thanks but i want to do the same thing in script.

that is i have to incert this script in the job.
Post #724046
Posted Wednesday, May 27, 2009 11:02 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, June 13, 2014 12:27 PM
Points: 267, Visits: 386
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)
Post #724369
Posted Wednesday, May 27, 2009 7:32 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 2, 2010 9:07 AM
Points: 34, Visits: 128
i have to enable SQLCMD at runtime. i.e., in the job.

please let me know how to do this??????
Post #724745
Posted Thursday, May 28, 2009 8:48 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 2, 2010 9:07 AM
Points: 34, Visits: 128
any one reply plzzzz
Post #725573
Posted Thursday, May 28, 2009 11:09 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, June 13, 2014 12:27 PM
Points: 267, Visits: 386
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)
Post #725603
Posted Friday, May 29, 2009 1:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 2, 2010 9:07 AM
Points: 34, Visits: 128
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......



Post #725637
Posted Friday, May 29, 2009 3:26 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, June 13, 2014 12:27 PM
Points: 267, Visits: 386
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)
Post #725680
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse