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

• aaschen

  SSC Rookie

  Points: 36

  More actions

  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

• Ritch

  Ten Centuries

  Points: 1044

  More actions

  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

• Ritch

  Ten Centuries

  Points: 1044

  More actions

  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

• G.R.Prithiviraj Kulasingham

  SSCarpal Tunnel

  Points: 4105

  More actions

  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/