Scheduling stored procedures with MS SQL 2000?

Is there anyway to make MS SQL Server 2000 perform some stored procedures,
say 3 times a day? If so, how? They told me to go to Management->SQL Server
Agent-> Jobs. But for writing the steps, does a stored procedures belong to
a
"Transact-SQL Script" type? Is there any documents or examples available?
Sorry, I know I'm troublesome.Yes. You would script it like this in the Command textbox:
exec dbo.MyStoredProcedure
If you need to pass in certain arguements, you may want to
create a wrapper stored procedure that can automatically
determine your parameters( ie. start date/end date if applicable).
It is problem easier to manage it this way.
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.masterado.net/home/listings.aspx
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:451C02E7-87CC-46CD-AAF9-81D099E7D018@.microsoft.com...
> Is there anyway to make MS SQL Server 2000 perform some stored procedures,
> say 3 times a day? If so, how? They told me to go to Management->SQL
> Server
> Agent-> Jobs. But for writing the steps, does a stored procedures belong
> to a
> "Transact-SQL Script" type? Is there any documents or examples available?
> Sorry, I know I'm troublesome.|||Yes you can schedule stored procedures to run 3 times a day using a job.
and yes, a stored procedure belong to a "Transact-SQL Script" type.
In the command box, type exec StoredProcedureName.
Schedule it to run daily every 8 hours.
Simon Worth
wrytat wrote:
> Is there anyway to make MS SQL Server 2000 perform some stored procedures,
> say 3 times a day? If so, how? They told me to go to Management->SQL Serve
r
> Agent-> Jobs. But for writing the steps, does a stored procedures belong t
o a
> "Transact-SQL Script" type? Is there any documents or examples available?
> Sorry, I know I'm troublesome.|||Thank you for your advice. Just asking if there's any available articles
online in MSDN or wherever that demonstrate how to create a job? Am I asking
for too much?|||This is just an example - hope it helps.
Look in BOL as well under "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:
Simon Worth
wrytat wrote:
> Thank you for your advice. Just asking if there's any available articles
> online in MSDN or wherever that demonstrate how to create a job? Am I aski
ng
> for too much?|||As others have suggested, you could use a SQL Agent Job. You may also want
to consider using the Windows task scheduler with the osql utility
(http://msdn.microsoft.com/library/d...r />
_1wxl.asp)
or Windows Script Host. Other scheduling software such as Control-M if your
organization has it is probably an even better choice.
Although I have some simple jobs set up I prefer the flexibility offered
through other scheduling solutions, particularly if the job is dependent on
another task or other tasks depend on it.
HTH
--
Dave Fancher
http://davefancher.blogspot.com
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:451C02E7-87CC-46CD-AAF9-81D099E7D018@.microsoft.com...
> Is there anyway to make MS SQL Server 2000 perform some stored procedures,
> say 3 times a day? If so, how? They told me to go to Management->SQL
> Server
> Agent-> Jobs. But for writing the steps, does a stored procedures belong
> to a
> "Transact-SQL Script" type? Is there any documents or examples available?
> Sorry, I know I'm troublesome.