[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to schedule a job script using commands

Posted on 2007-10-20
10
Medium Priority
?
1,432 Views
Last Modified: 2008-01-09
hi,

I'd like to schedule a job script to run daily at say 1am. I'd like to set this schedule using script commands (not enterprise manager). The script to be run is say at d:\myscript.txt

What are the commands to set this?

Thanks!
0
Comment
Question by:xenium
  • 6
  • 4
10 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20114576
>not enterprise manager
why not?

now, with sql server 2000, you would use osql.exe to run a script file
with sql 2005, the corresponding tool is sqlcmd.exe

please look in the books online about the tool you have too use, about the flags to use...
if you have any question, don't hesitate to ask

0
 

Author Comment

by:xenium
ID: 20114600
I want to provide a script so i can install on a new machine very quickly (the new machine is not built yet)

Some of the commands i think i need are:
sp_add_job
sp_add_jobstep
sp_add_jobschedule

But there are many options and i'm new to this. In the simple case that i need, i hope it can't be more the a few lines of code. Points up if its harder than i think.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20114614
sorry, I misunderstood you a bit.

now, I suggest you set up a full job in enterprise manager, and then right-click it and choose the "script job" menu... that will give you a full script with all the steps you need, so you can use that as "template".
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:xenium
ID: 20115472
Thanks, this helps. Yes confusing, 2 scripts 1 to setup the schedule (what i'm looking for), and 1 that i will be scheduled daily (that i have already).  

I guess then i just need to know the command to run a script file, so i can put this into my one-step job, can you help here?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20115694
>I guess then i just need to know the command to run a script file
is the script a procedure, or contained in a file?
if it is a file, you get back to my first comment:
* use sqlcmd command line tool to run the script file
and combine that with
* you can set up a job to run a batch file or command line tool with arguments instead of a sql script


0
 

Author Comment

by:xenium
ID: 20116131
It's a file, eg d:\myscript.txt as mentioned. What do i type into the 'Command' box of jobstep? following from suggestion to use Enterprise mgr then generate script.
Thanks!
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 20173340
if you take this "command":
isqlw.exe -U sa -P admin -f c:\script\shrink.sql
your step needs to be of type "operating system command"

if you take this version:
exec master..xp_cmdshell 'osql -Usa -Padmin -Smyserver -i"c:\script\shrink.sql"'

your step needs to be a "normal" SQL type step.
0
 

Author Comment

by:xenium
ID: 20204926
Thanks, this is useful, i'm just getting my head round all this new stuff, will post back shortly.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question