Link to home
Start Free TrialLog in
Avatar of cjharle
cjharle

asked on

How do I schedule a stored procedure to run at specified intervals

I have stored procedure that I need to run at a specified interval. How do I do this
Avatar of cyberkiwi
cyberkiwi
Flag of New Zealand image

This is the starting page: http://msdn.microsoft.com/en-us/library/ms190268.aspx
And follow the see also:tasks depending on what type of job you are after, in this case it is a Transact-SQL Job Step, so go for http://msdn.microsoft.com/en-us/library/ms187910.aspx
Lastly, you need to schedule it: http://msdn.microsoft.com/en-us/library/ms191439.aspx
You can optionally follow other links to add an email alert, but that requires more setup (database mail).
create a bat file

sqlcmd -S xxx.xxx.xxx.xxx,1521\Instance -U user -P pass@word -d DBName -Q "exec [dbo.mySP]" -e

then use windows task manager to run this...
Avatar of rimonh
rimonh

You may want to define a SQL server job that will run the store procedure on a specific time or time interval.
Avatar of cjharle

ASKER

I am creating a new job step. What type should I select if I want to run a stored procedure?
ASKER CERTIFIED SOLUTION
Avatar of cyberkiwi
cyberkiwi
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of cjharle

ASKER

Thanks I didn't dig deep enough into your links to see the simple EXEC command. This worked perfectly.
Avatar of cjharle

ASKER

Here's another related item:

does anyone know why I wouldn't be able to enter a value in the "run-as" field. They want to see a different user as having run the job.