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.

Scheduling Stored Procedures for MS SQL Server 2000

Is there any way to make MS SQL Server 2000 to perform some stored procedures, say 3 times a day? If so, how? Thanks.Use the SQL Agent. Google for examples. It's a core part of a SQL server installation. Set up your jobs, let fly.|||

I need to execute a webmethod form awebservice every 5 minutes and wanted 2 use a stored procedure but I can't schedulea job because Tiscali won't allow me. Is there another way to schedule a storedprocedure or call a webmethod with out having my computer do it over the internet?They also won't allow me to install a windows service in the server.

sql

Scheduling stored proc.

How do you schedule stored procedures? I can see no way of linking them to the job scheduler.
Also, how do I time the stored proc. schedule to activate after a scheduled DTS package?
Thanks again guys!OK, I see how to schedule a stored proc...by just scheduling an execute command.

But I still cant see how to time this with the scheduling of a DTS package.|||You create a new DTS.
You drop your connection,
You drop "Execute SQL TASK". You write your SQL-query

(example: "exec myproc")

you choose "save as" and name your DTS.
Now you will have a DTS in the list, and you right click on it and choose "Scedule Package"

Good luck!|||Its possible to execute and schedule a stored procedure using job scheduler just mention SP name and select connected database, schedule it.

scheduled job runs forewer

Hello,
I have scheduled job that are scheduled to run every 2 hours.
Its exec stored procedures. Normal execution time is up to 10 min.
But recently job not running successfully, its just executes forever. So I had to kill that job and I run it manually.
Please help,
I have no idea why it stops executing on its own by the schedule.

Thank you,did you use sp_who2 to see if it is blocked?

did you use dbcc_inputbuffer or sql_handle to see if the code executing changed?

did you create an output file in the job scheduler to capture output from the job steps?

did you change the procs to write to the output file as each starts, runs, and ends?

did you logically think "what can i do to troubleshoot this issue?"

did you google for similar situations?

did you look at the "sticky" at the top of the forum to see what you might need to add to your post to help us help you?|||Thank you for all of the suggestions you have for me.
I will try them one by one.|||How about trying one of the easiest things, stop and start the sql agent. Sometimes the scheduler gets wacked out and a simple stop and start will fix it.|||Does the code contains loops or cursors?|||Does the code contains loops or cursors?

while (1=1)
begin
print 'probably'
end|||The procedure does contain cursors and it runs Ok via SQL Query Analyzer, but once I add it up to SQL Server Agent Job it takes forever...
I do have an output file to capture output from the job steps, but since the job never been completed, and I had to kill it, there are no completion info.
If I will stop and start the sql agent before execution of this job, how the other scheduled processes be affected by that?

Thank you|||Whatever other issues may be occurring, step #1 is to drop the cursors and use set-based operations.|||Unless the jobs sends separate e-mails to employees, for example...|||The procedure does send an e-mail to few employees. The proc was written way before I started my work within the company, I already suggested to go trough code, and rewrite some sql - got rejection, so not sure how else to handle the issues.
Thank you.|||...I already suggested to go trough code, and rewrite some sql - got rejectionBlindman's principle of employement: Never work for people who aren't as smart as you.|||Does that make you unemployed Blinddude? :p|||Does that make you unemployed Blinddude? :p

More like unemployable.;)

hmscott|||that's how you guys have so much time to post.|||your boss doesn't have to be as smart as you.

what's important is that he/she does what you tell them to do. :)

Scheduled job executing Stored procedures fails

Hi,
I have 4 seperate stored procedures that if I run manually from Query
Analyzer work fine. Each one picks rows from a number of joined tables
depending on a date criteria. Each SP is exactly the same except for the
date ranges.
When they are run manually they do what I expect and I end up with 4 tables
each populated with a 1/2 dozen records.
However when I create a job in Enterprise Manager and add four steps into it
(one for each job) I end up with it creating four empty tables even though
two minutes ago I ran them manually and got what I expect and if I run them
manually after the scheduled job ran I get the correct results.
Each step says something like "Exec MoneyLaundering_21Days" and that's it.
The correct database is chosen in the top of the scheduled job dialogue box.
I've even tried scheduling a new job and just putting one SP into it but I
get the same result. I'm kinda at my wits end here so any assistance would
be much appreciated.
Regards,
Matt Lemon.Do you have the sp wrapped in a transaction that you are not committing?
Andrew J. Kelly SQL MVP
"news.esat.net" <matt.lemon@.williamfry.ie> wrote in message
news:dvttn9$cj4$1@.reader01.news.esat.net...
> Hi,
> I have 4 seperate stored procedures that if I run manually from Query
> Analyzer work fine. Each one picks rows from a number of joined tables
> depending on a date criteria. Each SP is exactly the same except for the
> date ranges.
> When they are run manually they do what I expect and I end up with 4
> tables each populated with a 1/2 dozen records.
> However when I create a job in Enterprise Manager and add four steps into
> it (one for each job) I end up with it creating four empty tables even
> though two minutes ago I ran them manually and got what I expect and if I
> run them manually after the scheduled job ran I get the correct results.
> Each step says something like "Exec MoneyLaundering_21Days" and that's it.
> The correct database is chosen in the top of the scheduled job dialogue
> box.
> I've even tried scheduling a new job and just putting one SP into it but I
> get the same result. I'm kinda at my wits end here so any assistance
> would be much appreciated.
> Regards,
> Matt Lemon.
>|||Did you look at error log? Have you tried profiler?
I assume, your procedures create tables AND populate them with some records
right?
Maybe the identity under which job is running doesn't have appropriate
credentials to populate tables?
Peter

Schedule Stored Procedures

Hello all,

I created a stored proceduce to delete specific records from a table. I want it to run every week.

How can I schedule a stored procedure?

I tried with SQL Server Agent -> Jobs, but it isn't a transact SQL-script.

Any ideas?

Thanks in advance.just a thought.... and if you write "exec yourProc" as transact SQL-script?..|||Hello Konstantin,

Thanks for the quick reply. It's working! That simple...

Best regards.

schedule a trace

Can someone share an example of how to schedule a trace? I have seen some stuff online that used xp_trace extended procedures however examples are not detailed enough for me to create a script of my own. So if someone can help me with their knowledge that will be great.

thanks

See SQL Server 2005 Books Online topics:
Scheduling Traces
http://msdn2.microsoft.com/en-us/library/ms187656.aspx

Using SQL Trace
http://msdn2.microsoft.com/en-us/library/ms191443.aspx