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
create a bat file
sqlcmd -S xxx.xxx.xxx.xxx,1521\Insta nce -U user -P pass@word -d DBName -Q "exec [dbo.mySP]" -e
then use windows task manager to run this...
sqlcmd -S xxx.xxx.xxx.xxx,1521\Insta
then use windows task manager to run this...
You may want to define a SQL server job that will run the store procedure on a specific time or time interval.
ASKER
I am creating a new job step. What type should I select if I want to run a stored procedure?
check here to learn about jobs if you go with SQL Server to run your job:
http://www.databasedesign-resource.com/sql-server-jobs.html
http://www.sqlusa.com/bestpractices2005/startjobfromstoredproc/
http://www.databasedesign-resource.com/sql-server-jobs.html
http://www.sqlusa.com/bestpractices2005/startjobfromstoredproc/
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks I didn't dig deep enough into your links to see the simple EXEC command. This worked perfectly.
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.
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.
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).