SQL Server - job schedule in sqlserver - Asked By rashmi r on 09-Apr-12 07:54 AM

hi friends,

job schedule in sql server script for select and insert query,

i want only the script (every time check for new records if have (compare datetime) latest records its automatically inserted in database) of job scheduling  in sql server.

SELECT calldate,duration,userfield FROM vcall WHERE calldate > '2012-04-08 16:04:56.000' Order By
calldate desc limit 100"

please give the correct solution and the script for this.

Somesh Yadav replied to rashmi r on 09-Apr-12 08:23 AM
try here,

Venkat K replied to rashmi r on 09-Apr-12 12:14 PM

Just validate the data and use sp_stop_job to stop the job.
In step 1 of your job try to return 0 or 1 based on the updated data you have
create proc sp_test
@prdt datetime
declare @dt datetime

select top 1 @dt=[dtValue] from my_table order by dtvalue desc

--print @id --debug code

if @dt>@prdt
    --Fetch the value here 

  -- Stop job
 sp_stop_job @jobname  -- to stop the job if there is no data.

Jitendra Faye replied to rashmi r on 10-Apr-12 01:48 AM
For this you need to create job scheduling agent for this.

this is an example for this-

USE msdb ;
-- creates a schedule named NightlyJobs.
-- Jobs that use this schedule execute every day when the time on the server is 01:00.
EXEC sp_add_schedule
    @schedule_name = N'NightlyJobs' ,
    @freq_type = 4,
    @freq_interval = 1,
    @active_start_time = 010000 ;
-- attaches the schedule to the job BackupDatabase
EXEC sp_attach_schedule
   @job_name = N'BackupDatabase',
   @schedule_name = N'NightlyJobs' ;

refer this link also-