stored procedure submitting as a database job

[umag]

Hi
I am very new to sybase sql server and exploring things for doing some work on this. Can someone please tell me how can I submit a database stored procedure as database job. I have a stored procedure in database and I want to run that on database once in a hour.

Thanks in advance
Uma

 

[nbrk]

do you want to do this through UNIX or windows?

Good Luck

 

[umag]

Hi nbrk
thank for your response. But I want that is to submit as a database job (like in oracle you can submit your stored procedure as a job) not through batch script. I know that the same can done through batch scripting either through unix or windows depends on where the databse is in.

 

[nbrk]

Ok, I am a little unsure of exactly you want...would the "waitfor" SQL statement be the kind of thing you are looking for?

Good Luck

 

[umag]

OK nbrk
sorry If I am not clear. what I am looking for is

I have a stored procedure. I want this stored procedure to run every 1 hour. Is there anyway in sybase sql server that I can achieve this without writing batch files.

Thanks in advance for help

Uma

 

[nbrk]

This is pretty crude but you could do this in an ISQL script

Label:
execute <stored procedure>
waitfor delay &quot;01:00:00&quot;
goto Label

As I said this is pretty basic...hope it helps a little

Good Luck

 

[nbrk]

don't forget you could do this in isql with the -i option

 

[JMCraig]

The general answer to your question is that there's not any such function in Sybase (AFAIK). You have to have an open connection to the database to issues a command; so you're faced with either batching it via the OS or via SQL.

The non-goto version (which could also test a field somewhere to know if it should stop) looks like this:

create table loop_stopper
( run bit)
go
insert loop_stopper (run) values (1)
go
...
while exists (select * from loop_stopper where run = 1) begin
execute <stored procedure>
waitfor delay &quot;01:00:00&quot;
end /* while */

Then, if you want to stop it, all you have to do is this from any session where you have update privileges on the loop_stopper table:

update loop_stopper
set run = 0
go

You could also build the delay right into the stored procedure itself. But, you will have to maintain an open session on the proc--if you disconnect, your server process will terminate.

If you want to specify a bit more about what this procedure does, someon on the forum might have another type of suggestion.

BOL,

John J M Craig
Alpha-G Consulting, LLC

http://alphagconsulting.bizhosting.com

nsjmcraig@netscape.net