May 27, 2009 at 1:20 am
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
May 27, 2009 at 3:45 am
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
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
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
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
any one reply plzzzz
May 28, 2009 at 11:09 pm
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
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
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
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'
October 24, 2024 at 9:36 pm
Thank you! Thank you! Thank you! Thank you!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy