Error running a job - SQL Server Forums

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)

Error running a job

Author

Topic

mary_itohan
Posting Yak Master

191 Posts

Posted - 2008-10-13 : 05:40:20

Hello,
I created a job and tried to run it. But get this error

EXEC dbo.sp_start_job 'test' ;

Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to run job test (from User SV2720\ServerAdmin) refused because the job has no job steps.

But when i select * from sys.jobsteps I get


eefc1336-94f9-4972-b2e0-5474ab178903	1	My Confirmations	TSQL	exec mytest_sp	0	NULL	0	1	0	2	0	NULL	Example	NULL	5	5	0	NULL	0	0	6	20081013	93900	NULL	6719f6ec-e4b0-4681-95cc-6774c2ea31c6

And here is my entire code for creating the job.


EXEC sp_add_job @job_name = 'test',@owner_login_name ='sa'

EXEC sp_add_jobstep @job_name = 'test',
   @step_name = 'My Confirmations',
   @subsystem = 'TSQL',
   @command = 'exec mytest_sp', 
   @retry_attempts = 5,
   @retry_interval = 5,
   @database_name = 'Example',
   @database_user_name= 'sa'

EXEC sp_add_jobschedule @job_name = 'test', 
   @name = 'Scheduled Delivery',
   @freq_type = 4, -- daily
   @freq_interval = 1,
   @freq_subday_type = 0x4,
   @freq_subday_interval=1

EXEC sp_add_jobserver @job_name = 'test'

what is wrong ?

_____________________

Yes O !

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-13 : 05:44:46

Let's turn to Books Online again.

DECLARE @jobId BINARY(16)

EXEC msdb.dbo.sp_add_job @job_name=N'Test', @owner_login_name=N'sa', @job_id = @jobId OUTPUT
		
EXEC msdb.dbo.sp_add_jobstep @job_id = @jobId, @step_name = N'Test', 
		@step_id=1, 
		@subsystem=N'TSQL', 
		@command=N'exec mytest_sp'

EXEC msdb.dbo.sp_add_jobschedule @job_id = @jobId, @name=N'Test', 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=4, 
		@freq_subday_interval=1

The way to connect the schedule and steps with job is with JobID, not jobname.

E 12°55'05.63"
N 56°04'39.26"

mary_itohan
Posting Yak Master

191 Posts

Posted - 2008-10-13 : 05:57:30

What does the "N" in the N'test' mean ?

I tried
EXEC dbo.sp_start_job '81e8ed44-4f3c-46c5-b933-dab3081b2b01' ;

But i still get the error.

Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 67
The specified @job_name ('81e8ed44-4f3c-46c5-b933-dab3081b2b01') does not exist.

_____________________

Yes O !

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-13 : 06:00:18

The prefix N means converting the string to UNICODE. At least that is what Books Online says.

And according to Books Online, the first parameter to sp_start_job is a NAME, not an ID.
Use this if you persist in using id

EXEC dbo.sp_start_job @Job_ID = '81e8ed44-4f3c-46c5-b933-dab3081b2b01' ;

E 12°55'05.63"
N 56°04'39.26"

mary_itohan
Posting Yak Master

191 Posts

Posted - 2008-10-13 : 06:00:28

Examples
The following example starts a job named Weekly Sales Data Backup.

Copy Code
USE msdb ;
GO

EXEC dbo.sp_start_job N'Weekly Sales Data Backup' ;
GO

This example, the name is used

_____________________

Yes O !

mary_itohan
Posting Yak Master

191 Posts

Posted - 2008-10-13 : 06:02:03

Am still getting no job steps

Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to run job test (from User SERVER1\Admin) refused because the job has no job steps.

_____________________

Yes O !

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-13 : 06:03:45

Beacuse you have no job steps attached to that job name.

E 12°55'05.63"
N 56°04'39.26"

mary_itohan
Posting Yak Master

191 Posts

Posted - 2008-10-13 : 06:05:03

Am sorry, but i thought this

EXEC sp_add_jobstep @job_name = 'test',
@step_name = 'My Confirmations',
@subsystem = 'TSQL',
@command = 'exec mytest_sp',
@retry_attempts = 5,
@retry_interval = 5,
@database_name = 'Example',
@database_user_name= 'sa'

Will add the job steps ?

Please correct me. Thanks

_____________________

Yes O !

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-13 : 06:07:24

If you run that single statement in SSMS or QA, do you get any error messages?

E 12°55'05.63"
N 56°04'39.26"

mary_itohan
Posting Yak Master

191 Posts

Posted - 2008-10-13 : 06:26:57

Ok I got it to run.

the username was wrong. Thanks Peso

_____________________

Yes O !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources