SQL Server Jobs

[viper1777]

Hi,
I have created an SQL job from a Delphi application using ADO. Mainly I have used the sp_add_job and associated commands. I have added a job, 2 steps and a schedule.

I then query the job I created for the job_id and insert into msdb..sysjobservers. I do this to get the runnabke status of the job to say 'Yes' instead of 'No (Add Target servers to this job)'.

The problem I am left with is that the Next Run Date says 'Date and Time not available'. Before running the above insert into msdb..sysjobservers I could go into the job in Ent Mgr and open it up change anything and click apply and all my problems were solved. (but only before I do the insert, not after)

But I need to create this job without Ent Mgr Access, the whole process needs to be automated.

Has anyone ever seen this because it has me beat. I have looked at all the sysjob% tables in msdb, before and after and there is no difference.

Thanx!

Dave Shaw

 

[NoCoolHandle]

THis is an example or what I have done in the past....
Red code = values you need to supply..

cn.Execute "msdb.dbo.sp_add_job @job_name = '[red]SomeJobName[/red]',@owner_login_name = '[red]SomeLoginName[/red]',@enabled=1"

cn.Execute "msdb.dbo.sp_add_jobserver  @job_name='[red]SomeJobName[/red]', @server_name='[red]SomeServerName[/red]'"

cn.Execute "msdb.dbo.sp_add_jobschedule @job_name='[red]SomeJobName[/red]' ,@name='normal',@freq_type=4,@freq_interval=1,@freq_subday_type=4,@freq_subday_interval=10,@active_start_time=0010"'[green]Every Ten Minutes, starting 10 past midnight[/green]

cn.Execute "msdb.dbo.sp_add_jobstep @job_name='[red]SomeJobName[/red]',@step_name='[red]SOmeJobStepName[/red]',@subsystem='TSQL',@on_success_action=1,@on_fail_action=2,@database_name='[red]SomeDatabase[/red]',@command='[red]exec SomeStoredProcOrOSTask'[/red]"

HTH


Rob