Schedule SQL query to run

[ruster]

Hello all,

I'm a bit of a rookie at SQL since I don't have much work with it very often. However, I have a situation where I need to schedule (on an SQL Server 2000 platform), an SQL script to run once a day. I know what the syntax of the SQL query needs to be, but don't know how I go about scheduling a task to run the SELECT string.

Can anyone help me out.

Thanks,

Ruster

 

[SQLBill]

Easiest way:

Use Enterprise Manager. Expand until you see Management. Expand that. Expand SQL Server Agent. You will see Jobs. Right click on that and select New Job.

In the first tab, enter the job name and owner. Should also add a description, but it's not necessary.

In the second tab (Steps), click on New Step. This is where you put your code. Give the step a name, select the type of code, select the database. Your code can be the whole SQL script or you can save your script as a stored procedure and just run the execute statement. I think the script may be limited to a certain amount of characters, but I can't remember. Go to the Advanced tab. This is where you will set up where the results should go to. Also, what you want to happen upon success and failure.

In the third tab (Schedule), this is where you schedule the job. Click on New Schedule. Give the schedule a name. Then set the schedule how you want it to run.

When you are all done, click Apply and OK. Now you will see the job in the Jobs display. It will run as scheduled or you can right click on it and select Start.

When the job has run at least once, you can right click on the job, select View Job History and you will see a list of everytime it ran. It will show whether it succeeded or failed. Check the box that says Show Job Steps and you will see what happened with each individual step. This is helpful for troubleshooting failed jobs.

If you want to 'play' with it and your job isn't one you can run whenever, try this:

Create a job called Cycle Error Log. Make one step called Cycle Error Log, use Master, and the code EXEC SP_CYCLE_ERRORLOG. In advanced, make it QUIT REPORTING SUCCESS, QUIT REPORTING FAILURE. You can even have it output a text file (remember to add the extension). Apply it and click OK. Now right click on it and select Start. Run the job. This will close out the current error log and open a new one. Kinda of handy to run every so often depending on how big your error log gets. Schedule it for once a month. If needed you can still run it manually.

-SQLBill

Posting advice: FAQ481-4875

 

[ruster]

SQLBill, your the best.

One quick question, the SQL Agent is stopped currently. I suspect I have to start it for the jobs to run?

Ruster

 

[SQLBill]

Yes you do. I would set it to run automatically. I'm not quite sure, but it's possible that when a job runs it starts the agent service and stops it when the job finishes. You would have to test it.

-SQLBill

Posting advice: FAQ481-4875

 

[Catadmin]

Also, you'll probably want to set SQL Server Agent to start back up if it stops unexpectedly. If you don't do that and something happens to it, your job won't run.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"