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.

eg:
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.


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

http://solihinho.wordpress.com/2009/01/01/query-for-listing-sql-server-job-schedule/
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
ex:
create proc sp_test
@prdt datetime
as
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 

else
  -- 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 ;
GO
-- 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 ;
GO
-- attaches the schedule to the job BackupDatabase
EXEC sp_attach_schedule
   @job_name = N'BackupDatabase',
   @schedule_name = N'NightlyJobs' ;
GO


refer this link also-

http://msdn.microsoft.com/en-us/library/ms191439.aspx