Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
sql server stored procedure stopped to work

Answered sql server stored procedure stopped to work

  • Tuesday, April 17, 2012 10:57 PM
     
     
    Sign In to Vote
    0

    I have a stored procedure which fails/ times out simetimes. But when I drop and recreate the procedure, it works properly.

    i copied to sql in that procedure and executed it separately. it works well.

    i do see people getting this issue but there is no resolution to this problem. what is the root cause cause of this issue.

    thanks

    venkatesh

     

All Replies

  • Wednesday, April 18, 2012 12:33 AM
     
     
    Sign In to Vote
    0

    What is the error you are getting when it fails??

    The procuderes may fail some times like when the database is in restore some thing like this.

    Rajitha

    Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

     
  • Wednesday, April 18, 2012 1:01 AM
     
     
    Sign In to Vote
    0

    The procedure doesnt fail at all, it keep executing for hours. normal execution is 2 sec.

    i checked the db and there isnt any restore job at all or any other other job running at that time.

     
  • Wednesday, April 18, 2012 1:20 AM
     
     
    Sign In to Vote
    0

    Are you using CURSORS (while loops) in your procedure? That may result slow performence of procedure.

    Rajitha

    Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

     
  • Wednesday, April 18, 2012 5:20 AM
    Answerer
     
     Answered
    Sign In to Vote
    0

    Looks like parameter sniffing , read Plamen's article 

    http://pratchev.blogspot.com/2007/08/parameter-sniffing.html

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

     
  • Wednesday, April 18, 2012 9:16 AM
     
     
    Sign In to Vote
    0

    hi , you can also clear our doubts on the below link.

    http://asp.net.bigresource.com/SQL-server-Why-this-stored-procedure-is-not-working-IX8qrhiCn.html

     
  • Wednesday, April 18, 2012 7:38 PM
     
     
    Sign In to Vote
    0

    Thank you very much for the help. I did try using With recompile in the procedure.

    Now exec sp is working properly but when I call the procedure  from ado.net which has the recompile option in it. it does not work.

    do you know why

     
  • Wednesday, April 18, 2012 7:55 PM
     
     
    Sign In to Vote
    0
    Have you tried using profiler to capture what sql server is doing when the proc runs an extend period of time?

    John

    http://knowledgy.org

     
  • Wednesday, April 18, 2012 11:11 PM
     
     
    Sign In to Vote
    0

    Thanks a lot recompile option worked.

    thanks for all the help.