locked
SqlConnectionTimeout exception on a stored procedure running perfect in Sql Server window RRS feed

  • Question

  • User-628540892 posted

    Hi, 

    I am having a very weird problem:

    whenever i am trying to execute a specific stored procedure i wrote through the .NET platform i am getting an exception, 

    BUT when i run it directly through the SQL server it runs quickly without any problems

    this is the code:

     using(SqlConnection conn = new SqlConnection(SiteFunc.ConnStr))
    		{
    			SqlDataAdapter da = new SqlDataAdapter();
    
    			try
    			{
    				SqlCommand cmd;
    				DataTable dtResults;
    				conn.Open();
    				
    				string txtFirstName = "";
    				string txtLastName = "";
    				string txtPrevName = "";
    				string ddlArmy = "";
    				string ddlResistance = "";
    				string ddlCategories = "";
    				string ddlCorps = "";
    				int army;
    				int.TryParse(ddlArmy, out army);
    				//army            
    				cmd = new SqlCommand("SoldierSearch_MultiLingual", conn);
    				cmd.CommandType = CommandType.StoredProcedure;
    				cmd.Parameters.Add(new SqlParameter("@FirstName", txtFirstName));
    				cmd.Parameters.Add(new SqlParameter("@LastName", txtLastName));
    				cmd.Parameters.Add(new SqlParameter("@PrevName", txtPrevName));
    				cmd.Parameters.Add(new SqlParameter("@Army", army));
    				cmd.Parameters.Add(new SqlParameter("@Res", ddlResistance));
    				cmd.Parameters.Add(new SqlParameter("@Cat", ddlCategories));
    				cmd.Parameters.Add(new SqlParameter("@Corps", ddlCorps));
    				cmd.Parameters.Add(new SqlParameter("@Lang", SiteFunc.Lang));
    				cmd.Parameters.Add(new SqlParameter("@PageNum", 1));
    				cmd.Parameters.Add(new SqlParameter("@PageSize", 20));
    				cmd.Parameters.Add("@TotalRecs", SqlDbType.Int).Direction = ParameterDirection.Output;
    				cmd.Parameters.Add("@ResultHdr", SqlDbType.NVarChar, -1).Direction = ParameterDirection.Output;
    				
    				da.SelectCommand = cmd;
    				dtResults = new DataTable();
    				
    				da.Fill(dtResults);
    			
    				string ttlRecs = SiteFunc.ObjectToString(cmd.Parameters["@TotalRecs"].Value);
    				Response.Write(ttlRecs);
    				
    			}
    			catch (Exception ex)
    			{
    				SiteFunc.debugs(ex);
    			}
    			finally
    			{
    				if (conn != null)
    				{
    					conn.Close();
    				}
    			}// end finally        
    		}

    this is the exception: 

    System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) 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.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) at test.Page_Load(Object sender, EventArgs e) in d:\spaces\blabla\blablablabla\wwwroot\test.aspx.cs:line 55 ClientConnectionId:afe013d1-ac53-4f6c-9a7f-08689cad90b9 Error Number:-2,State:0,Class:11

    and the following is the sql sp:

    ALTER PROCEDURE [dbo].[SoldierSearch_MultiLingual] 
    	-- Add the parameters for the stored procedure here
    	@FirstName nvarchar(100) = '',
    	@LastName nvarchar(100) = '',
    	@PrevName nvarchar(100) = '',
    	@Army int = NULL,
    	@Res int = NULL,
    	@Cat int = NULL,
    	@Corps int = NULL,
    	@Lang nvarchar(2) = '',
    	@PageNum int = 1,
    	@PageSize int = 30,
    	@TotalRecs int OUTPUT,
    	@ResultHdr nvarchar(MAX) OUTPUT
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        -- Insert statements for procedure here
    	SELECT
    	UserID AS ID, Full_Name, ShortDesc, 
    	(SELECT  TOP 1 
    	CASE 
    		WHEN LOWER(@Lang) = '' THEN  C.Title 
    		WHEN LOWER(@Lang) = 'en' THEN C.TitleEN 
    		ELSE C.TitleRu 
    	END
    		FROM map_SoldierDetails ms  
    		left join Category C ON ms.CatID = C.ID
    		WHERE C.MasterID = 5953
    		AND ms.CustID = UserID)  as ArmyTitle,
    	(SELECT  TOP 1 
    	CASE 
    		WHEN LOWER(@Lang) = '' THEN  C.Title 
    		WHEN LOWER(@Lang) = 'en' THEN C.TitleEN 
    		ELSE C.TitleRu 
    	END
    		FROM map_SoldierDetails ms  
    		left join Category C ON ms.CatID = C.ID
    		WHERE C.MasterID = 5962
    		AND ms.CustID = UserID)  as ResTitle,
    	(SELECT  TOP 1 
    	CASE 
    		WHEN LOWER(@Lang) = '' THEN  C.Title 
    		WHEN LOWER(@Lang) = 'en' THEN C.TitleEN 
    		ELSE C.TitleRu 
    	END
    		FROM map_SoldierDetails ms  
    		left join Category C ON ms.CatID = C.ID
    		WHERE C.MasterID = 5967
    		AND ms.CustID = UserID)  as PartTitle,
    	(CASE 
    		WHEN LOWER(@Lang) = '' THEN FName + ' ' + LName 
    		WHEN LOWER(@Lang) = 'en' THEN FNameEn + ' ' + LNameEn 
    		ELSE FNameRu + ' ' + LNameRu 
    	END) AS MLName,	
    	(CASE 
    		WHEN LOWER(@Lang) = '' THEN LName + ' ' + FName 
    		WHEN LOWER(@Lang) = 'en' THEN LNameEn + ' ' + FNameEn 
    		ELSE LNameRu + ' ' + FNameRu 
    	END) AS SortName	
    	INTO #Results
    	FROM STCUstomers 
    	INNER JOIN SoldierDetails ON SoldierDetails.CustomerID = STCustomers.UserID
    	WHERE 
    	(@FirstName = '' OR FName LIKE '%'+@FirstName+'%' OR FNameEn LIKE '%'+@FirstName+'%' OR FNameRu LIKE '%'+@FirstName+'%')
    	AND (@LastName = '' OR LName LIKE '%'+@LastName+'%' OR LNameEn LIKE '%'+@LastName+'%' OR LNameRu LIKE '%'+@LastName+'%')
    	AND (@PrevName = '' OR PreviousLName LIKE '%'+@PrevName+'%' OR PreviousLNameEn LIKE '%'+@PrevName+'%' OR PreviousLName LIKE '%'+@PrevName+'%')
    	AND (@Army = 0 OR CustomerID IN (SELECT CustID FROM map_SoldierDetails WHERE CatID = @Army) )
    	AND (@Res = 0 OR CustomerID IN (SELECT CustID FROM map_SoldierDetails WHERE CatID = @Res) )
    	AND (@Cat = 0 OR CustomerID IN (SELECT CustID FROM map_SoldierDetails WHERE CatID = @Cat) )
    	AND (@Corps = 0 OR CustomerID IN (SELECT CustID FROM map_SoldierDetails WHERE CatID = @Corps)) 
    	AND Full_Name IS NOT NULL AND Full_Name != '' AND Active = 1
    	ORDER BY SortName
    	
    	SELECT @TotalRecs = COUNT(*) FROM #Results
    	
    	SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY [SortName] ASC)AS RowNumber, * FROM #Results) FullList
        WHERE RowNumber BETWEEN(@PageNum -1) * @PageSize + 1 AND(((@PageNum -1) * @PageSize + 1) + @PageSize) - 1
    
    	DROP TABLE #Results
    
    	IF @Cat != 0 
    		BEGIN
    			IF @Lang = '' SELECT @ResultHdr = ShortBody FROM Category WHERE ID = @Cat
    			IF LOWER(@Lang) = 'en' SELECT @ResultHdr = ShortBodyEn FROM Category WHERE ID = @Cat
    			IF LOWER(@Lang) = 'ru' SELECT @ResultHdr = ShortBodyRu FROM Category WHERE ID = @Cat
    		END
    	ELSE SELECT @ResultHdr = ''
    
    END
    
    

    please help, I am out of ideas.

    Gil

    Saturday, May 12, 2018 10:56 AM

All replies

  • User475983607 posted

    whenever i am trying to execute a specific stored procedure i wrote through the .NET platform i am getting an exception, 

    BUT when i run it directly through the SQL server it runs quickly without any problems

    In my experience this type if issue is related to the data used while tests compared to real application data.  The data works fine with contrived test data but real data causes unexpected behavior.   I usually start with the data.

    If you are using the same inputs to test in SSMS then look into a connection issues between the web server and SQL server.

    Saturday, May 12, 2018 11:45 AM
  • User-1716253493 posted

    The problem is your SP, too many criteria to evaluate, also to many IN (SELECT ...)

    I guess, the sql server is very busy

    Saturday, May 12, 2018 3:43 PM
  • User347430248 posted

    Hi Codingr,

    I suggest you to add 'command time out property' in your code.

    Example:

    using (SqlCommand cmd = new SqlCommand())
    {
      cmd.Connection = connection1;
      cmd.CommandTimeout = 240; //in seconds
      //etc...
    }

    You can modify the timeout seconds as per your requirement.

    Reference:

    SqlCommand.CommandTimeout Property

    Regards

    Deepak

    Monday, May 14, 2018 2:32 AM