[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 207
  • Last Modified:

Triggering a routine everday a a certain hour

Hi

Is there a way in asp to run a routine everyday at a certine time automatically?
0
robrodp
Asked:
robrodp
3 Solutions
 
ispalenyCommented:
I don't think you want to run it in ASP. You can use Windows Scheduler, SQL Server Agent or some other scheduler.
0
 
ispalenyCommented:
Can you post more information about what you trying to do ?
0
 
Jim P.Commented:
In the Enterprise Manager
Micrososoft SQL Servers -> SQL Server Group -> %Server/Instance Name% -> Management -> SQL Server Agent -> Job

Right-click in the rght-hand window New Job.
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
arbertCommented:
You can call ASP from SQL Server as well.  Just tell us more about what you're trying to do...
0
 
ispalenyCommented:
ASP is a client initiated code. So you are not able to define an exact time to run it. But there is one exception, if your site is busy all the day. You can put code checking time in ASP and run it on every connection. But when is my site busy, why making it more busy? I think it is more suitable to use a scheduler. The process run by the scheduler can even drive ASP pages over HTTP connection, if you are not able to reach server in other way.
0
 
arbertCommented:
"ASP is a client initiated code. So you are not able to define an exact time to run it."

Sure you can.....SQL Server can be a client just like anything else....
0
 
robrodpAuthor Commented:
Thanks

I have a routine that updates all data daily. It elimitaes events that have an old date

I want to run it once at say 12:00

0
 
ispalenyCommented:
You can provide HTTP code interaction in many ways. CDO objects use current user's Internet Explorer profile.

Replace

http://www.yoururl.com
N'[Uncategorized (Local)]'
N'Test'
N'(local)'

by

your url
your job category
your job name
your SQL Server name

SQL script:
------------------------------------------

BEGIN TRANSACTION            
  DECLARE @JobID BINARY(16)  
  DECLARE @ReturnCode INT    
  SELECT @ReturnCode = 0    
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1
  EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'

  -- Delete the job with the same name (if it exists)
  SELECT @JobID = job_id    
  FROM   msdb.dbo.sysjobs    
  WHERE (name = N'Test')      
  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 ''Test'' 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'Test'
    SELECT @JobID = NULL
  END

BEGIN

  -- Add the job
  EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'Test', @owner_login_name = N'X\y', @description = N'No description available.', @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'Test', @command = N'set message = createobject("CDO.Message")
set message.Configuration = createobject("CDO.Configuration")
message.CreateMHTMLBody "http://www.yoururl.com"
set message.Configuration =  nothing
set message = nothing', @database_name = N'VBScript', @server = N'', @database_user_name = N'', @subsystem = N'ActiveScripting', @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'Test', @enabled = 1, @freq_type = 4, @active_start_date = 20050514, @active_start_time = 120000, @freq_interval = 1, @freq_subday_type = 1, @freq_subday_interval = 0, @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:
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now