SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


how to enable SQL CMD MODE using t-sql script


how to enable SQL CMD MODE using t-sql script

Author
Message
mohan.pariveda
mohan.pariveda
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 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
Eswin
Eswin
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1937 Visits: 1078
Check out :]


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

Tanx :-D
hi_abhay78
hi_abhay78
Mr or Mrs. 500
Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)

Group: General Forum Members
Points: 543 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)
mohan.pariveda
mohan.pariveda
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 128
thanks but i want to do the same thing in script.

that is i have to incert this script in the job.
hi_abhay78
hi_abhay78
Mr or Mrs. 500
Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)

Group: General Forum Members
Points: 543 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)
mohan.pariveda
mohan.pariveda
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 128
i have to enable SQLCMD at runtime. i.e., in the job.

please let me know how to do this??????
mohan.pariveda
mohan.pariveda
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 128
any one reply plzzzz
hi_abhay78
hi_abhay78
Mr or Mrs. 500
Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)

Group: General Forum Members
Points: 543 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)
mohan.pariveda
mohan.pariveda
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 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......
hi_abhay78
hi_abhay78
Mr or Mrs. 500
Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)

Group: General Forum Members
Points: 543 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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search