can I create a job to call a stored procedure?

Hello,

Can I create a job to call a stored procedure everyday? If yes, how to do it?

Thanks

I use sql server 2005.

Thanks

|||

Yes, refer to Books Online, Topic: SQL Agent, Jobs

Unless you are using SQL Express. With SQL Express, SQL Agent is not included. You can accomplish the same functionality using the Windows Scheduler service, SQLCmd.exe and a stored procedure.

|||

from sql agen job, I can't find stored procedure calling item from the drop down list. It only has maintanence plan etc.

Thanks

|||If you will explore Books Online, Topic: Jobs, Creating, you will find excellent walk-thoughs and examples.|||You have
1) create new job,
2) add a step to created job
3) Add name for this step, such as "Step1"
4) Choose type: Transact-SQL script
5) Choose actual database from list
6) Add command:

Code Snippet

EXEC sp_yourstored_proc_name

7) Go to schedules tab and set correct schedule plan|||

Easiest way to at least for me is to use isql wrapped in a bat file.

Open notepad or your favorite text editor.

Type the following, replacing where required.

@.echo off
isql -S SERVER_NAME_OR_IP -d DATABASE_NAME -Q "EXEC STORED_PROC_NAME" -U YOUR_USERNAME -P YOUR_PASSWORD -s , -o "OUTPUT_FILE"

Save the file with .bat as the extension.

Navigate to Start, All Programs, Control Panel, Scheduled Tasks, Add Scheduled Task.

Select next from the introduction screen.

Select browse from the next screen and find the .bat file created earlier.

Give the job a name and select interval from the next screen.

Enter the username and password to run as (helpful if using integrated security).

Select finish.

You can test your job by navigating to the scheduled jobs folder, right click on the job, select run

You can obtain the command arguments for isql using "isql /?" (w/o the quotes) at the command prompt.

Simple bat example (will execute sp_monitor and log the results to c:\sqlout.csv)

@.echo off

isql -S 192.168.1.250 -d master -Q "EXEC sp_monitor" -U test -P test -s , -o "c:\sqlout.csv"