none
SQL stored procedure timeout RRS feed

  • Question

  • Dear all,

     

    HAPPY NEW YEAR to everybody!

     

    I'm not sure whether this is a VB.NET problem or a T-SQL problem. I just post it here. Sorry, it's a long story.

     

    I'm using VB.NET 2005 & SQL server 2005. I have a SQL stored procedure which updates record in a table. The statement is very simple:

    update TABLE_A set FIELD_1=<parm 1> where FIELD_2="ABC"
    (parm 1 is a pass in parameter, the data type is the same as field definition in TABLE_A)

     

    The problem is that when this stored procedure is executed directly in SQL server, it can finish running in less than 5 seconds. However, when the VB.NET program execute this stored procedure it returns the "TIMEOUT EXPIRED" error sometimes. Sometimes only, not always. I have tried to set the command timeout property to a bigger value (500), but it doesn't solve the problem. In addition, I have tried to run to this program on another PC, the result is the same. Before the execute stored procedure statement in the VB.NET program, there are other statements which select records from the same SQL server, and it works fine. So it is not a network connection problem. I use debug in VB.NET and set a breakpoint at this execute store procedure statement. I monitor the SQL server activity by task manager, it provides some clue. When this stored procedure is executed, the activity level of the SQL task increase rapidly to 60%-70% and then drop to 0% within 2 seconds. As a result, the VB.NET program just wait and finially timeout. I wonder what is happening.

     

    I have 2 questions:

    1) What is the different when a stored procedure is executed directly on SQL server and when it is submitted by a VB.NET program? It seems the stored procedure is waiting for something, probably a lock?
    2) How can I know from the VB.NET program what is happening inside the SQL server after the stored procedure is submitted?

     

    Any help?

    thanks in advance!

    Sunday, December 30, 2007 11:59 AM

Answers

All replies

  • how much record it will update with the stored procedure update statement?

     

    You can try to set the command timeout to 0 (no limit).

    Monday, December 31, 2007 6:32 AM
  • Dear Swain,

     

    Do you mean the VB.NET program should wait forever? Only a few records updated. As I said before, if the stored procedure is executed on SQL server directly, it will finish running within 5 seconds.

     

    thanks

    Monday, December 31, 2007 1:27 PM
  • Hi Baby programmer,

     

    This thread has the similar issue to yours. Please check those suggestions inside.

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=687165&SiteID=1

    Please make sure you don't use a transaction in the stored procedure in your VB.NET program.

     

    I move it from VB Language forum to .NET Framework Data Access and Storage forum for better responses.

     

    Happy New Year!

    Martin

    Thursday, January 3, 2008 10:04 AM