How do i schedule a job ( T-SQL)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How do i schedule a job ( T-SQL)

Author

Topic

mary_itohan
Posting Yak Master

191 Posts

Posted - 2008-08-31 : 09:58:58

Hello,
I am trying to schedule a job to run when called from a SP

The name of the SP am trying to run is 'weekly_Updates' and i am trying to add it as a job. Am not sure what I am doing

How do i add this SP to be execute at 0:00 hours

thanks


USE msdb ;
GO

EXEC dbo.sp_add_job
    @job_name = N'sally', 
    @enabled = 1,
    @description = N'weekly Updates',
    @owner_login_name = N'sa',
    @notify_level_eventlog = 2,
    @notify_level_email = 2,
    @notify_level_netsend = 2,
    @notify_level_page = 2,
    @notify_email_operator_name = N'sysname',
    @notify_netsend_operator_name = N'sa', 
    @notify_page_operator_name = N'sa',
    @delete_level = 0 ;
GO

Am getting this error

Msg 14234, Level 16, State 1, Procedure sp_verify_job, Line 243
The specified '@notify_email_operator_name' is invalid (valid values are returned by sp_help_operator).

Yes O !

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-08-31 : 10:47:19

this param is wrong:

@notify_email_operator_name = N'sysname',

you need to specify the name of an operator, or null. as the error msg indicates, you can call sp_help_operator to get a list of valid operators.

elsasoft.org

mary_itohan
Posting Yak Master

191 Posts

Posted - 2008-08-31 : 16:05:00

When I run sp_help_operator I get nothing back :(

Yes O !

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-08-31 : 16:32:20

What version of MS SQL are you using ?

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-08-31 : 16:39:06

Just in case. Pls note: MS SQL 2005 express cant schedul jobs. Same with MSDE

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-31 : 16:57:35

Just pass it NULL or remove the parameter from the list of input parameters.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-08-31 : 17:47:50

This would work

USE msdb ;
GO

EXEC dbo.sp_add_job
@job_name = N'sally'

Also see http://www.lazycoding.com/products.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources