Přihlásit
 
HlavníKnihovnaCertifikaceDownloadPodporaKomunitaFóra
Zdroje informací pro profesionály v oboru IT >
> 
> 
sql server stored procedure stopped to work

Odpovědět sql server stored procedure stopped to work

  • 17. dubna 2012 22:57
     
     
    Přihlásit se a hlasovat
    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

     

Všechny reakce

  • 18. dubna 2012 0:33
     
     
    Přihlásit se a hlasovat
    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

     
  • 18. dubna 2012 1:01
     
     
    Přihlásit se a hlasovat
    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.

     
  • 18. dubna 2012 1:20
     
     
    Přihlásit se a hlasovat
    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

     
  • 18. dubna 2012 5:20
    Přispěvatel
     
     Odpovědět
    Přihlásit se a hlasovat
    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/

     
  • 18. dubna 2012 9:16
     
     
    Přihlásit se a hlasovat
    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

     
  • 18. dubna 2012 19:38
     
     
    Přihlásit se a hlasovat
    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

     
  • 18. dubna 2012 19:55
     
     
    Přihlásit se a hlasovat
    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

     
  • 18. dubna 2012 23:11
     
     
    Přihlásit se a hlasovat
    0

    Thanks a lot recompile option worked.

    thanks for all the help.