June 7, 2006 at 6:58 am
We have a very object oriented ASP.NET website with SQL 2000 database. Usually we connect to a database via stored procedures. We need to provide web-users with option to create filters for reports and schedule them to run via web interface daily, weekly, monthly.
What is the best way to do it? How to schedule execution of stored procedures by intervals of time online?
We do not use Reporting Services of SQL Server.
June 8, 2006 at 7:49 am
We have a system here which requires that jobs be performed periodically. I created a scheduling engine and a web interface for it which allows multiple schedules to be defined. Then I wrote an NT service in C# which loads the schedules and calculates when the next occurrance should be and triggers it from code (along with other processing). You could programattically create jobs in SQL, but I needed something more extensible. You could certainly use a similar approach but use a T-SQL scheduled job instead of the NT Service to do the work.
June 9, 2006 at 12:17 pm
Thanks! I will look up on how to programatically create jobs in T-SQL. Something like: msdb..sp_start_job probably.
June 11, 2006 at 12:58 am
Creating a load of jobs may be overkill, depending on how many you create it may be best to log to a table the SP that needs to be run, and when it needs to be run ie. Last time and interval perhaps, then have a single scheduled job which polls this table every n hours (depending on the regularity you require), running the required SP for the required user. It would certainly be much easier to debug and maintain than a large number of scheduled jobs all triggered to run at different times. Just my 2c.
June 12, 2006 at 7:21 am
Great Idea with a Log table!! You simply gave me the solution. Working on it now. Thank you a lot!
I did find though how to create jobs (see below - in case if someone interested):
=============================================
(From BOL "How to Create a job (Transact-SQL)" )
-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'JobName')
IF (@JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''JobName'' since there is
already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N'JobName'
SELECT @JobID = NULL
END
BEGIN
-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT ,
@job_name = N'JobName', @owner_login_name = N'DEML\sworth2',
@description = N'This is the job to run a stored procedure',
@category_name = N'[Uncategorized (Local)]', @enabled = 1,
@notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend =
0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID,
@step_id = 1, @step_name = N'TSQLStep1', @command = N'EXEC
SP_Spaceused', @database_name = N'Northwind', @server = N'',
@database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code =
0, @flags = 0, @retry_attempts = 0, @retry_interval = 1,
@output_file_name = N'', @on_success_step_id = 0, @on_success_action =
1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID,
@start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID,
@name = N'3TimesADay', @enabled = 1, @freq_type = 4, @active_start_date
= 20050331, @active_start_time = 0, @freq_interval = 1,
@freq_subday_type = 8, @freq_subday_interval = 8,
@freq_relative_interval = 0, @freq_recurrence_factor = 0,
@active_end_date = 99991231, @active_end_time = 235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID,
@server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply