we have created a new job step object in the statements above. the jobstep object exposes the attributes of a single sql server agent executable job step. sql server agent jobs contain one or more execution units called steps. each job step contains a textual command, type of execution that specifies command interpretation, and logic that determines the behaviour of the job if the step succeeds or fails
idstep = idstep + 1 ojobstep.name = jobid & idstep ojobstep.stepid = idstep 'set the job step executable subsystem. ojobstep.subsystem = "tsql"
the subsystem property specifies the sql server agent execution subsystem used to interpret job step task-defining text.
if databasename <> "" then ojobstep.databasename = databasename else ojobstep.databasename = "yourdatabase" end if
if the user fails to pass the database name from the front end than the component will pick up the hardcoded database name provided that you have hardcoded the database name in your code.
if idstep = "1" then if commandtext <> "" then ojobstep.command = commandtext else ojobstep.command = "select * from table1" ojobstep.onsuccessaction = sqldmojobstepaction_gotonextstep end if else ojobstep.stepid = 2 if commandtext2 <> "" then ojobstep.command = commandtext2 else ojobstep.command = "delete from table2" ojobstep.onsuccessaction = sqldmojobstepaction_quitwithsuccess end if end if
we have added two commands to the jobs, one will return all the records from the table and the second will delete all the records from the particular table, this has been done just to give you an example, you can do whatever you want with your database tables by passing the command text either from the front end or by hardcoding the command text in the code as seen above.
ojob.jobsteps.add ojobstep
next
add the individual job step to the jobsteps collection.
'set the target server ojob.applytotargetserver (cstr(server))
the applytotargetserver method adds an execution target to the list of targets maintained for the referenced sql server agent job.
now, here comes the important part, the scheduling of the job, the job has been created but now we have to schedule the job so that it runs at a specific date and time.
jobschedule object exposes the attributes of a single sql server agent executable job schedule.
dim ojobschedule as sqldmo.jobschedule
set ojobschedule = new sqldmo.jobschedule
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
'schedule the task!
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
you can calculate any time and date for your task to start execution, it solely depends on your choice or requirement. we have calculated the year, month and day separately.
dim startyear, startmonth, startday
'indicate execution scheduled for everyday by using
'the frequencytype and frequencyinterval properties.
ojobschedule.name = jobid
ojobschedule.schedule.frequencytype = sqldmofreq_onetime
