Re: Limitations of Scheduled jobs

anonymous_at_discussions.microsoft.com
Date: 03/15/04 

Date: Mon, 15 Mar 2004 02:20:42 -0800

Hi,
Thanks for the response.
Actually we can schedule the jobs with accuracy less than
1 min. Enterprise manager GUI allows only minutes but
using SQL script, we can set the interval parameter to any
seconds(using @freq_subday_interval & @freq_subday_type),
this way you create only one job. I was able to do that.
What I just found from my testing is even if I set the
duration to 3 seconds and the SP is taking longer than
that, there would be no overlap since the next iteration
of the job would fire only when the first is completed.
Pls. correct me if I'm wrong.

>-----Original Message-----
>Hi Nonnie,
>
>That is not going to work with a job, because you can
only schedule jobs
>with an accuracy of 1 minute, and you can't have 2
instances of the same job
>running at the same time. You can work around these
limitations if you have
>2 jobs (or however many you need to cover an interval
that is longer than
>the job duration), use an endless loop in a job, and use
the WAITFOR TIME
>statement to time kicking of the appropriate job.
Sp_start_job works
>asynchronously, where executing the stored procedure
works synchronously, so
>that wouldn't solve the problem. For example:
>
>DECLARE @i INT
>
>SET @i = 0
>WHILE 1=1
>BEGIN
> IF @i % 2 = 0
> EXEC sp_start_job job_1
> IF @i % 2 = 1
> EXEC sp_start_job job_1
> SET @i = @i + 1
> WAITFOR DELAY '00:00:03'
>END
>
>It might not be a good idea btw to run the stored
procedure when it is still
>running, as it might be blocked by the previous instance,
but that depends
>on what the stored procedure does exactly.
>
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>
>"Nonnie" <nonniem@rediffmail.com> wrote in message
>news:cfb501c40a63$e34a8b90$a301280a@phx.gbl...
>> Hi,
>> I'm using SQL Server 2000 as the DB for my web
application
>> and I have a stored procedure that needs to be run
every 3
>> seconds. But the execution of this SP takes more than 3
>> sec. Is it okay to schedule a job for this or is it
better
>> to write a .NET windows service and use multi threading?
>> Please let me know the limitations of both the choices.
>
>
>.
>

Relevant Pages

  • Re: scheduling a t-sql stored procedures name in table column without creating a job for each on
    ... You want to schedule or simply run/put them on a Q and they run? ... Again I would still connect to SQL Server however it is that you want to do it, get all the possible procs from your Control table. ... I want to write a stored procedure that will ... You also do not need to use a Script task to do ...
    (microsoft.public.sqlserver.dts)
  • Re: Limitations of Scheduled jobs
    ... > Actually we can schedule the jobs with accuracy less than ... where executing the stored procedure ... >>on what the stored procedure does exactly. ... >>SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: Report to display data from sql serv.
    ... IIF (case statements in SQL Server) scenarios etc. ... then you need to create a Stored Procedure in SQL Server and use that as ... at run time supplying the parameters in code by using the Exec command. ...
    (microsoft.public.access.reports)
  • Re: Views vs Stored Procedures, whats the difference?
    ... I hope you are not suggesting you embed SQL queries into the application? ... A stored procedure logic will be exactly as fast as the algorithm you ... I understant that SQL Server supports hints. ... implementations (nestedloop, merge, hash, ..) on decent sized tables, then ...
    (comp.databases.ms-sqlserver)
  • Re: ADO stored proc
    ... to know how to access a SQL Server stored procedure using vbscript ... I've looked at vbscript books and sql server books on ... I don't like this technique since: ...
    (microsoft.public.scripting.vbscript)
Loading