Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003

    Exclamation Unanswered: Stored procedures hangs application on first execution


    I have a .Net application which invokes an stored procedure (SQL Server 2005 Express installed on the same machine). When the stored procedure is called the first time, the application hangs because the sp never ends execution and the application's process has to be killed. But when the application is executed again, the sp runs as expected. What could be happening?

    The stored procedure references remote tables by means of synonyms. If the Management Studio is used instead, the sp never ends execution when invoked the first time, but the query can be cancelled.

    Now, if the sp is invoked first in the Management Studio first and then by the application, this does not hang (the sp executes as expected).

    Thanks a lot.
    Last edited by EMoscosoCam; 04-19-07 at 15:54.

  2. #2
    Join Date
    Nov 2005
    I suggest you run SQL Server Profiler and trace all TSQL statements (don't quite remember the name of the events though) to see what is going on and where inthe sproc execution hangs.

  3. #3
    Join Date
    Jun 2003
    Provided Answers: 1
    We can't help you much if you don't post the sproc.
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

  4. #4
    Join Date
    Nov 2004
    on the wrong server
    Provided Answers: 6
    on the second execution you probably have a cached execution plan. however your sql should be written and your database should be designed so it does not rely upon a cached plan to finish execution.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts