scheduling sql agent jobs from an asp page

[Tony Lyons]

Hi Group,
Is it possible to schedule and execute sql jobs from an asp page.
I am developing an application that requires an administration section. In this section the admin would like to change / update the schedule of the sql job and also run the jobs ad hoc. Basicall y they would like the functionality of sql agen in enterprise manager to be available from their web application.

Has anbody done this before ?
All information / solutions shall be psoted back to the group as I reckon this would be pretty useful functionality.

Thanks in advance

Tony

[WizKid]

Piece o' cake... refer BOL from the following SPs and build your ASP page around it..
sp_start_job
sp_stop_job
sp_update_job
sp_update_jobschedule
sp_update_jobstep
sp_add_jobstep
sp_delete_jobstep
etc..


------------
Tony Lyons at 2/17/01 12:13:58 PM


Hi Group,
Is it possible to schedule and execute sql jobs from an asp page.
I am developing an application that requires an administration section. In this section the admin would like to change / update the schedule of the sql job and also run the jobs ad hoc. Basicall y they would like the functionality of sql agen in enterprise manager to be available from their web application.

Has anbody done this before ?
All information / solutions shall be psoted back to the group as I reckon this would be pretty useful functionality.

Thanks in advance

Tony

[Tony]

Thanks for this.... It's not going to be as difficult as perceived initially.

Is it possible to dynamically change the connection properties of a dts package in the same way .. The user would also like to run jobs identifiable by a project number. The import files for each project are in a different location. So if say project A was selected then the connections in the dts packages would need to change to the location of A's project files.

Thanks again

Tony




------------
WizKid at 2/17/01 5:18:29 PM

Piece o' cake... refer BOL from the following SPs and build your ASP page around it..
sp_start_job
sp_stop_job
sp_update_job
sp_update_jobschedule
sp_update_jobstep
sp_add_jobstep
sp_delete_jobstep
etc..


------------
Tony Lyons at 2/17/01 12:13:58 PM


Hi Group,
Is it possible to schedule and execute sql jobs from an asp page.
I am developing an application that requires an administration section. In this section the admin would like to change / update the schedule of the sql job and also run the jobs ad hoc. Basicall y they would like the functionality of sql agen in enterprise manager to be available from their web application.

Has anbody done this before ?
All information / solutions shall be psoted back to the group as I reckon this would be pretty useful functionality.

Thanks in advance

Tony

[Phil McCormack]

The dynamic changing of DTS Properties could be a dangerous thing. I would suggest building dynamic DTS Packages instead, or using a Generic DTS Package

see www.sqlconsultants.co.uk for VB code to do this. All connection info is held in a table and each package is identified uniquely by a name. So you could have ProjectAExcel1toSQL, ProjectBExcel1toSQL, etc....

Then by calling the generic DTS with a name, it sets up the correct connections for you. V useful in a dev/test/live environment.


------------
Tony at 2/19/01 3:07:25 AM

Thanks for this.... It's not going to be as difficult as perceived initially.

Is it possible to dynamically change the connection properties of a dts package in the same way .. The user would also like to run jobs identifiable by a project number. The import files for each project are in a different location. So if say project A was selected then the connections in the dts packages would need to change to the location of A's project files.

Thanks again

Tony




------------
WizKid at 2/17/01 5:18:29 PM

Piece o' cake... refer BOL from the following SPs and build your ASP page around it..
sp_start_job
sp_stop_job
sp_update_job
sp_update_jobschedule
sp_update_jobstep
sp_add_jobstep
sp_delete_jobstep
etc..


------------
Tony Lyons at 2/17/01 12:13:58 PM


Hi Group,
Is it possible to schedule and execute sql jobs from an asp page.
I am developing an application that requires an administration section. In this section the admin would like to change / update the schedule of the sql job and also run the jobs ad hoc. Basicall y they would like the functionality of sql agen in enterprise manager to be available from their web application.

Has anbody done this before ?
All information / solutions shall be psoted back to the group as I reckon this would be pretty useful functionality.

Thanks in advance

Tony

[micwazoo]

A couple of other methods would be:
Create package configuration files, and just update the package configuration with the new variables prior to the job being submitted.

or

Use variables in your package, and pass the values through the command line of the job. Sort of like this
DTEXEC /FILE "C:\Packages\MyPackage.dtsx" /SET
"\Package.Variables[User::ImportFilePath].Properties[Value];C:\ImportFiles\*MyImportFile.csv"