none
Data access works with Sql Server Management Studio but not with .NET. RRS feed

  • Question

  • I have a stored procedure that I am calling like:

     

    Code Snippet

    SqlCommand command = connection.CreateCommand();

    command.CommandText = "proc_GetKitDetailList";

    command.CommandType = CommandType.StoredProcedure;

    command.Parameters.Add(new System.Data.SqlClient.SqlParameter("@in_values", SqlDbType.Xml));

    if (connection.State != ConnectionState.Open)

    {

    connection.Open();

    }

    command.Parameters["@in_values"].Value = xmlString;

    SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SequentialAccess);

     

     

     

    But I get the stack trac like:

     

    ------ Test started: Assembly: TestProject.dll ------

    TestCase Test.BsiServices.MessageTest.TestKitList'

    failed: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

    at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

    at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()

    at System.Data.SqlClient.SqlDataReader.get_MetaData()

    at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

    at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)

    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)

    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

    at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

    at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)

    0 passed, 1 failed, 0 skipped, took 60.64 seconds.

     

    If I execute the same stored procedure with the same input from Sql Server Manager it returns data with no problem. I have checked the connection string and the input parameters to make sure they are the same in both cases. I am at a loss as to where to proceed in debugging this problem. It seems like the stored procedure is timing out for some reason but only when call through the .NET APIs. Any ideas?

     

    Thank you.

     

    Kevin

     

    Monday, August 13, 2007 5:29 AM

Answers

  • As nobody has answered this question, and I've just suffered 2 hours of trying to fix this myself, I'll supply my answer.

     

    I closed down my SQL Management Studio, so I only had Visual Studio Running. I ran my project and the code work. It seemed that I had a connection open somewhere that was locking. Visual Studio was running under a username and password supplied by my project, SQL studio was running as my windows token.

     

    I was also using commits and temp tables in my stored procedure. Ensure that commits have been completed (or rolled back) and ensure all temp tables are clear before end of Stored proc.

     

    Wednesday, February 27, 2008 10:43 AM

All replies

  • Try this

     

    Code Snippet

    command.CommandTimeout = 120;

     

     

    Monday, August 13, 2007 10:14 AM
  • If your stored procedure executes longer than 30 seconds, then you will get that error, since default timeout for all the commands is 30 seconds. To increase timeout of the command you need to set CommandTimeout property of SqlCommand to larger value (more that 60 seconds in your case). Are you sure that it should take so much time? Make sure that queries use proper indexes during selection and there are no table scans.

    Monday, August 13, 2007 10:32 AM
    Moderator
  • I have exactly the same issue except in my case the stored proccedure takes about 0.3 seconds in management studio and 30 seconds - a few minutes when run from code, I have no ideas why, it doesn't appear to have anything to do with locking.

    Monday, August 13, 2007 1:22 PM
  •  

    That is the problem. The stored procedure takes less than a second when run from the SQL Management Studio. But it times out when I run it from .NET. I can try increasing the timeout when I call it from .NET but it should not be timing out. What is the difference in the two different methods of calling the stored procedure? Thank you.

     

    Kevin

    Monday, August 13, 2007 2:19 PM
  • As nobody has answered this question, and I've just suffered 2 hours of trying to fix this myself, I'll supply my answer.

     

    I closed down my SQL Management Studio, so I only had Visual Studio Running. I ran my project and the code work. It seemed that I had a connection open somewhere that was locking. Visual Studio was running under a username and password supplied by my project, SQL studio was running as my windows token.

     

    I was also using commits and temp tables in my stored procedure. Ensure that commits have been completed (or rolled back) and ensure all temp tables are clear before end of Stored proc.

     

    Wednesday, February 27, 2008 10:43 AM
  • I catch the same problem. Under SQL Studio my stored procedure works fine and takes 1-2 seconds.
    From C# .Net code I receive exception after 20-30 seconds of execution. If I use "SelectCommand.CommandTimeout = 120;", exception waits 120 seconds before return "Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.....". I tried to run from my work machine (Windows XP), localy from Windows 2003 server; Debug/Release versions - the same result. 
    Program can drop 20 times and begin to work after that without any changes and drop again after 2 days, using the same compiled exe-file.
    SQL2005 server:  2% CPU utilization, no locks in system. And I use (nolock) hint in my select operators in stored procedures. Framework 2.0
    Also I tried to run SP this way:
    DataSet ds = new DataSet("tmp");
    SqlDataAdapter adapter = new SqlDataAdapter();
    adapter.SelectCommand = new SqlCommand("Exec MyProc 7", _conn);
    adapter.SelectCommand.CommandTimeout = 120;  // ?  -  No result
    adapter.Fill(ds);
    No changes, exception.
    Did you find decision? Smile
    Wednesday, April 16, 2008 3:01 PM
  • Hi,

    I'm having the same problem with a SQL Server 2005 database stored procedure. When is called from a .NET application takes near 3 minutes to return its result (Checked with SQL Server Profiler). When is called from the SQL Server Management Studio takes 11 seconds to return its result (Time got from SQL Server Management Studio).

    I also realized that SQL Server Profiler doesn't detect calls from SQL Server Management Studio.

    May anybody give me some advice about this problem.

    Thanks in advace.

    Benjamin

    Friday, January 14, 2011 4:28 PM
  • Try the forums under the SQL Server category. Post the connection string when you ask there so the DBAs know which communication protocol is used.

    The following is signature, not part of post
    Please mark the post answered your question as the answer, and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.
    Visual C++ MVP
    Friday, January 14, 2011 5:03 PM