Post reply

How to make a SP stop automaticaly after s.time?

  • June 25, 2003 at 5:52 am

    #101538

    Hi,

    Does somebody know how to stop a stored procedure from running if it takes more than "n" minutes and don't resume?

    I have this problem here. In some cases, someone is quering my db on my web site, and it starts my stored procedure. I still don't know why, but it stops responding.

    The problem is: how do I configure the SP (or SQL in general) to kill the process that does not respond?

    Sorry I made this question twice, but my english is not that good, so I tried to make myself understood.

    Alexandre Aschenbach


    Alexandre Aschenbach

  • June 25, 2003 at 6:48 am

    #462999

    Hi,

    Sorry I haven't had time to test it, but the following maybe along the lines of what you need.

    --------------------------------------

    declare @startdate datetime

    set @startdate = getdate()

    .

    .

    .

    if @startdate = datediff(mi, @startdate, getdate()) + n /*where n is the number of minutes you wish to timeout after*/

    return

    --------------------------------------

    Hope this helps.

    Ritch

    *I didn't do anything it just got complicated*


    "I didn't do anything it just got complicated" - M Edwards

  • June 27, 2003 at 4:33 am

    #463411

    Sorry was talking crock before, why break a habit of a lifetime . The following works though.

    -------------------------------

    declare @startdate datetime,

    @enddate datetime,

    @count int

    set @startdate = getdate()

    set @enddate = dateadd(mi, n, @startdate) --where n is the minutes you want to time out after

    set @count = 1

    while @count > 0

    begin

    if getdate() >= @enddate

    break

    else

    set @count = @count + 1

    end

    print 'It worked'

    --------------------------------

    Hope it helps.

    Laters

    Ritch

    *I didn't do anything it just got complicated*

    Edited by - Ritch on 06/27/2003 04:41:24 AM


    "I didn't do anything it just got complicated" - M Edwards

  • June 27, 2003 at 6:03 am

    #463426

    In most of the cases I handle this type of situation from the client.

    If you use ADO connection to connect to the database It has a Commandtimeout property

    You can set the time there

    Preethi

    Cheers,
    Prithiviraj Kulasingham

    http://preethiviraj.blogspot.com/

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply