Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

A created a new SQL Job in SQl Server Agent. I followed the steps mentioned in the article http://msdn.microsoft.com/en-us/library/ms187910.aspx

Still it is in idle state. PLease note that the schedular is prepared to execute every 10 seconds.

Below is the Drop and create script

IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'test')
EXEC msdb.dbo.sp_delete_job @job_id=N'7ac1e514-fec2-4516-b716-f00107fb127e', @delete_unused_schedule=1
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 01/26/2012 14:22:27 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'test', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'No description available.', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [rr]    Script Date: 01/26/2012 14:22:31 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'rr', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'insert into [datet](dat) values(getdate())', 
        @database_name=N'master', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N's', 
        @enabled=1, 
        @freq_type=4, 
        @freq_interval=1, 
        @freq_subday_type=2, 
        @freq_subday_interval=10, 
        @freq_relative_interval=0, 
        @freq_recurrence_factor=0, 
        @active_start_date=20120125, 
        @active_end_date=99991231, 
        @active_start_time=203200, 
        @active_end_time=215959, 
        @schedule_uid=N'0ad45ad2-6d8f-4193-a687-f60bf7d5f0e5'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

enter image description here

share|improve this question
    
The job will be idle unless it is actually running when you refresh the view in SSMS. What does the job history show (right-click the job, select "History")? –  Ed Harper Jan 26 '12 at 8:19
    
not showing any record... –  abcdefghi Jan 26 '12 at 8:21
    
If the job isn't running, something is wrong with the schedule. I suggest you script out the job (right-click, select "script job as" > CREATE TO > New Query Editor window) and edit your question to include the resulting script. –  Ed Harper Jan 26 '12 at 8:33
    
I have added the Drop/Create script as suggested by you. –  abcdefghi Jan 26 '12 at 8:50
    
Please update the question with the latest version of the job script –  Ed Harper Jan 26 '12 at 16:17
show 2 more comments

migrated from stackoverflow.com Jan 26 '12 at 11:58

This question came from our site for professional and enthusiast programmers.

2 Answers

As far as I can see your schedule is set to execute between midnight (12:00AM) to noon (12:00PM), i.e. your job will not execute in the afternoon and evening. Change your ending time to 11:59:59PM and it should run all day.

share|improve this answer
add comment

The job definition you have posted is configured only to execute between 20:32 and 21:59 in server local time. Check the local time of the server and confirm whether it's configured to be in the timezone you're expecting.

To remove this restriction, amend the schedule to run from 00:00:00 to 23:59:59.

EDIT

Try changing the schedule end time from 12:00:00 PM to 11:59:59 PM, just to be certain about the time frame

share|improve this answer
    
Please check the schedule screenshot in my question. I hope it should be fine now ? –  abcdefghi Jan 26 '12 at 9:15
    
@StackOverflowUser - that looks correct. If you apply the change you'll soon be able to tell if the job is executing by monitoring the job history –  Ed Harper Jan 26 '12 at 14:09
    
But i am still waiting to get a single record inserted in this table through the sql job scheduler –  abcdefghi Jan 26 '12 at 14:12
    
@StackOverflowUser - is job history being generated? –  Ed Harper Jan 26 '12 at 16:03
    
no recordis coming up in history... –  abcdefghi Jan 26 '12 at 16:03
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.