none
Store Procedure running for ever RRS feed

  • Question

  • Trying to run store procedures those are interlinked with each other through parameter passing and collect data from linked server. Was running smoothly but for some reason now its running for ever. Checked the timeout and configure it with 0(infinite). Checked sp_who and found the value in 'cmd' column is 'INSERT'. Please help me with your knowledge and guidence to ressolve this.

    Thank you.

    SA 

    Tuesday, June 29, 2010 1:19 PM

Answers

  • Trying to run store procedures those are interlinked with each other through parameter passing and collect data from linked server.


    Linked server operations are subject to network issues. Stored procedures cannot really handle well linked server related malfunctions.

    Consider using SSIS to move data between linked (remote) and local servers. SSIS has extensive error handling and logging features.

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Sunday, July 4, 2010 1:23 PM
    Answerer

All replies

  • Could it be parameter's sniffing problem?

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


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, June 29, 2010 2:45 PM
    Moderator
  • Trying to run store procedures those are interlinked with each other through parameter passing and collect data from linked server.


    Linked server operations are subject to network issues. Stored procedures cannot really handle well linked server related malfunctions.

    Consider using SSIS to move data between linked (remote) and local servers. SSIS has extensive error handling and logging features.

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Sunday, July 4, 2010 1:23 PM
    Answerer