none
sql server stored procedure stopped to work

    Question

  • 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

    Tuesday, April 17, 2012 10:57 PM

Answers

All replies

  • 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 12:33 AM
  • 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:01 AM
  • 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 1:20 AM
  • 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/

    • Proposed as answer by Peja Tao Wednesday, April 18, 2012 6:37 AM
    • Marked as answer by Venkatesh Manian Wednesday, April 18, 2012 11:11 PM
    Wednesday, April 18, 2012 5:20 AM
  • 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 9:16 AM
  • 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:38 PM
  • 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 7:55 PM
  • Thanks a lot recompile option worked.

    thanks for all the help.

    Wednesday, April 18, 2012 11:11 PM