Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Question

1.00/5 (1 vote)

See more:

C#

public DataSet GetFinalResultFormula(int? shiftID, int courseID, int specializationID, int instituteID, int sessionID, int USERID, int SubjectID,int pageno)
        {

            try
            {

                _sql = "CalculateGracemarksCat";
                _parms = new SqlParameter[8];
                _parms[0] = new SqlParameter("@ShiftID", SqlDbType.Int);
                _parms[0].Value = shiftID == 0 ? null : (object)shiftID;
                _parms[1] = new SqlParameter("@CourseID", SqlDbType.Int);
                _parms[1].Value = courseID;
                _parms[2] = new SqlParameter("@SpecializationID", SqlDbType.Int);
                _parms[2].Value = specializationID;
                _parms[3] = new SqlParameter("@InstituteID", SqlDbType.Int);
                _parms[3].Value = instituteID;
                _parms[4] = new SqlParameter("@SessionID", SqlDbType.Int);
                _parms[4].Value = sessionID;
                _parms[5] = new SqlParameter("@USERID", SqlDbType.Int);
                _parms[5].Value = USERID;
                _parms[6] = new SqlParameter("@EmpSubjectID", SqlDbType.Int);
                _parms[6].Value = SubjectID;
                _parms[7] = new SqlParameter("@pagenumber", SqlDbType.Int);
                _parms[7].Value = pageno == 0 ? null : (object)pageno;

                ManuplateTable(_sql, _parms);





                _sql = "GetFinalResultFormula";
                _parms = new SqlParameter[8];
                _parms[0] = new SqlParameter("@ShiftID", SqlDbType.Int);
                _parms[0].Value = shiftID == 0 ? null : (object)shiftID;
                _parms[1] = new SqlParameter("@CourseID", SqlDbType.Int);
                _parms[1].Value = courseID;
                _parms[2] = new SqlParameter("@SpecializationID", SqlDbType.Int);
                _parms[2].Value = specializationID;
                _parms[3] = new SqlParameter("@InstituteID", SqlDbType.Int);
                _parms[3].Value = instituteID;
                _parms[4] = new SqlParameter("@SessionID", SqlDbType.Int);
                _parms[4].Value = sessionID;
                _parms[5] = new SqlParameter("@USERID", SqlDbType.Int);
                _parms[5].Value = USERID;
                _parms[6] = new SqlParameter("@EmpSubjectID", SqlDbType.Int);
                _parms[6].Value = SubjectID;
                _parms[7] = new SqlParameter("@pagenumber", SqlDbType.Int);
                _parms[7].Value = pageno == 0 ? null : (object)pageno;

                //_parms[7] = new SqlParameter("@FilterResultStatus", SqlDbType.Char);
                //_parms[7].Value = FilterResultStatus == "(All)" ? null : (object)FilterResultStatus;
                //_parms[8] = new SqlParameter("@FiltersubjectId", SqlDbType.Int);
                //_parms[8].Value = Filtersubjectid == 0 ? null : (object)Filtersubjectid;

                _dataSet = RunProcedure(_sql, _parms, true);
                _dataSet.Tables[0].TableName = "GetFinalResultFormula";
                _dataSet.Tables[1].TableName = "GetFinalResult";
                return _dataSet;
            }
            catch
            {
                throw;
            }
        }


 <add name="Delhi" connectionstring="Data source=xxxxxxx;User Id=xx;Password=xxx; Initial Catalog=RU;Connection Timeout=900;" providername="System.Data.SqlClient">



<httpruntime executiontimeout="500" maxrequestlength="100000">

What I have tried:

above i post my code so please help me how to fix this issues

Posted 17-Oct-19 20:32pm

Member 12183079

Updated 18-Oct-19 1:26am

Patrice T

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Patrice T · Answer 1 · 2019-10-17T21:20:00

Solution 1

Quote:
above i post my code so please help me how to fix this issues

On first look, there is no fix to apply in this code because the error message says that the problem is in SQL server. You forgot to show the stored procedure.

Posted 17-Oct-19 21:20pm

Patrice T

Comments

Member 12183079 23-Oct-19 2:00am

when i execute stroed procedure then it take 1:15 min and they trough result

Member 12183079 23-Oct-19 2:13am

two procedure calling in same time i am share procedure to you

-- exec [CalculateGracemarksCat] 3,1,12,1,14,1,0

CREATE PROCEDURE [dbo].[CalculateGracemarksCat]

@CourseID INTEGER,

@ShiftID INTEGER=null,

@SpecializationID INTEGER,

@InstituteID INTEGER,

@SessionID INTEGER=NULL,

@USERID INTEGER,

@EmpSubjectID INTEGER ,

@pagenumber int=null

AS

SET NOCOUNT ON

begin

DECLARE @EmployeeID INTEGER

DECLARE @EmployeeAssignSubject TABLE ( subID INTEGER )

Declare @CatMarksTbl Table

(

StudentID int,

SubjectID int,

SubCategory varchar(10),

ExaminationID int,

ExaminationTypeID int,

MaximumMarks decimal(18,2),

MinMarks decimal(18,2),

MarksObtained decimal(18,2),

Grade nvarchar(10),

StdStatus nvarchar(20),

[GraceMarks] decimal(18,2),

IsMain bit ,

IsCat bit,

ShiftID INT ,

CourseID int ,

SpecializationID INT ,

SessionID INT

)

Declare @MainMarksTbl Table

(

StudentID int,

SubjectID int,

SubCategory varchar(10),

ExaminationID int,

ExaminationTypeID int,

MaximumMarks decimal(18,2),

MinMarks decimal(18,2),

MarksObtained decimal(18,2),

Grade nvarchar(10),

StdStatus nvarchar(20),

[GraceMarks] decimal(18,2),

IsMain bit ,

IsCat bit,

ShiftID INT ,

CourseID int ,

SpecializationID INT ,

SessionID INT

)

IF OBJECT_ID('dbo.CatGraceMarksParticularTBL', 'U') IS NOT NULL

DROP TABLE dbo.CatGraceMarksParticularTBL;

-- Drop table CatGraceMarksParticularTBL

create table CatGraceMarksParticularTBL

(

StudentID int,

SubjectID int,

ParticularsTypeID int, ----1--Main,2--Cat,3--Grace marks

MarksDetails Nvarchar(max)

)

IF @CourseID = 0 -- when employee login , no need to choose programm ,

BEGIN

SELECT @EmployeeID = EmployeeID FROM dbo.biz_EmployeeProfile WHERE UserID=@USERID

IF @EmpSubjectID = 0

BEGIN

INSERT INTO @EmployeeAssignSubject (subID)

SELECT subjectID FROM biz_AssignSubjectEmployee WHERE EmployeeID=@EmployeeID AND (@ShiftID is null or ShiftID=@ShiftID) AND

isnull(IsSubjectSelect,0)=1

AND isnull(IsActive,0)=1 AND InstituteID= @InstituteID

END

ELSE

BEG

Patrice T 23-Oct-19 3:51am

Use Improve question to update your question.
So that everyone can pay attention to this information.

MadMyche · Answer 2 · 2019-10-18T01:26:00

The exception pretty much is saying that your stored procedure is taking too long to run. This means that you you need to shift your focus from the calling code, ASP.NET; to the code that is being called: the Stored Procedure.

If the database is running on SQL Server; the best way to debug is going to be using SSMS (Sql Server Mgmt Studio) and see what is causing the procedure to take so long. The most common problem is going to be a lack of or improper indexes on the tables in the database.

I noticed your connection string contains a reference to increase the ConnectionTimeout to 15 minutes.... a little bit excessive and has no bearing on the issue you are having; it is only related to how long it takes for your application to contact the DB. It is rare to have a legitimate need to set this value as the default of 15 seconds is plenty long enough for a client to establish a connection with the server

There is also a CommandTimeout attribute that can be placed on the actual SqlCommand object to allow longer running of a query before it times out. The default value is 30 seconds. Context connections/asynch methods are not affected by changes to this value. In all reality it is a bandage for poor SQL queries or designs.

References:
Connection timeout and Command timeout in SQL Server – Don Castelino[^]
SqlCommand.CommandTimeout Property (System.Data.SqlClient) | Microsoft Docs[^]