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

Question

0.00/5 (No votes)

See more:

C#

public DataSet GetCurrentTrainingDue(int CompanyId, int ContactId, string GroupIds, string ContactIds, DateTime? FromDate, DateTime? ToDate, string TestStatusFilter, string SortOrder4, bool? AllEmployees,string Documents)
        {
            try
            {
                //string TestStatusFilter = "Current,Failed,Overdue,Not Taken";
                string SortOrder1 = "CompanyDescriptor";
                string SortOrder2 = "ContactDecriptor";
                string SortOrder3 = "DocumentDescriptor";
                if (SortOrder4 == null)
                    SortOrder4 = "TestingDateDesc";
                SqlConnection conn = new SqlConnection(objConnection.GetConnection());
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                SqlCommand cmd = new SqlCommand();
                DataSet DsLoginData = new DataSet();
cmd.Parameters.Add(new SqlParameter("@CompanyIds", CompanyId));
                cmd.Parameters.Add(new SqlParameter("@RequestingContactId", ContactId));
                cmd.Parameters.Add(new SqlParameter("@GroupIds", GroupIds));
                cmd.Parameters.Add(new SqlParameter("@ContactIds", ContactIds));
                cmd.Parameters.Add(new SqlParameter("@AllEmployees", AllEmployees));
                cmd.Parameters.Add(new SqlParameter("@FromDate", FromDate));
                cmd.Parameters.Add(new SqlParameter("@ToDate", ToDate));
                cmd.Parameters.Add(new SqlParameter("@Passed", false));
                cmd.Parameters.Add(new SqlParameter("@Failed", false));
                cmd.Parameters.Add(new SqlParameter("@Disabled", false));
                cmd.Parameters.Add(new SqlParameter("@ShowInactiveContacts", false));
                cmd.Parameters.Add(new SqlParameter("@IncludeGroupsInResultSet", false));
                cmd.Parameters.Add(new SqlParameter("@TestStatusFilter", TestStatusFilter));
                cmd.Parameters.Add(new SqlParameter("@LastTestStatusFilter", null));
                cmd.Parameters.Add(new SqlParameter("@SortOrder1", SortOrder1));
                cmd.Parameters.Add(new SqlParameter("@SortOrder2", SortOrder2));
                cmd.Parameters.Add(new SqlParameter("@SortOrder3", SortOrder3));
                cmd.Parameters.Add(new SqlParameter("@SortOrder4", SortOrder4));
                cmd.Parameters.Add(new SqlParameter("@DocumentIds", Documents));
                cmd.CommandText = "Reporting_TrainingHistory";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = conn;
                
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                sda.SelectCommand.CommandTimeout = 10000;
                sda.Fill(DsLoginData);
                DsLoginData = SqlHelper.SqlHelper.ExecuteDataset(conn, "Reporting_TrainingHistory", p);                                           
                DsLoginData.Tables[0].TableName = "vwReporting_TrainingHistory";
                return DsLoginData;
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                conn.Close();
            }
        }

plz help me

Posted 23-Jul-15 0:03am

Sambit_Sahoo

Updated 23-Jul-15 0:12am

DamithSL

v2

Add a Solution

Comments

DamithSL 23-Jul-15 6:14am

what happens when you run without CommandTimeout?

1 solution

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)

Wendelius · Answer 1 · 2015-07-23T00:20:00

Solution 1

If you hit the time out in the execution of the procedure Reporting_TrainingHistory then the most probable cause is that the procedure is:
- taking more time than expected
- in infinite loop etc

So the problem is not on the calling side, the C# code but inside the procedure.

Investigate what the procedure does and is there some fault in it. You also know the paramters you used so you can investigate what happens with those parameters.

If needed, take advantage of SSMS ability to show execution plans, timed statistics and so on

Posted 23-Jul-15 0:20am

Wendelius

Comments

Sambit_Sahoo 23-Jul-15 6:23am

Below is the Stored Procedure
CREATE PROC [dbo].[Reporting_TrainingHistory](

@CompanyIds varchar(500),

@RequestingContactId INT,

@GroupIds varchar(max) = NULL,

@ContactIds varchar(max) = NULL,

@AllEmployees BIT = 0,

@FromDate datetime = NULL,

@ToDate datetime = NULL ,

@Passed bit = 0 ,

@Failed bit = 0,

@Disabled bit = 0,--@ShowInactiveDocuments

@ShowInactiveContacts BIT = 0,

@IncludeGroupsInResultSet BIT = 0,

@TestStatusFilter VARCHAR(MAX) = NULL,

@LastTestStatusFilter VARCHAR(MAX) = NULL,

@SortOrder1 VARCHAR(MAX) = 'CompanyDescriptor',

@SortOrder2 VARCHAR(MAX) = 'ContactDecriptor',

@SortOrder3 VARCHAR(MAX) = 'DocumentDescriptor',

@SortOrder4 VARCHAR(MAX) = 'TestingDateDesc',

@DocumentIds VARCHAR(MAX) = null

) AS SET NOCOUNT ON

DECLARE @CDP TABLE (

ContactID INT

,DocumentID INT

,DocumentAssignedDate DATETIME

,[Type] VARCHAR(12)

,NextTestDate DATETIME

,LastTestDate DATETIME

,DeliveryDate DATETIME

,TestFrequency INT

,LastContactDocumentTestAttemptID INT

,HasTest BIT

,Active BIT

,ContactDocumentTestAttemptStatusId INT

)

IF @AllEmployees = 1 --use create a group of lists for the companies

BEGIN

SET @GroupIds = NULL

SELECT @GroupIds = COALESCE(@GroupIds+',','') + LTRIM(GroupId)

FROM [Group] g

JOIN fnSplit(@CompanyIds,',') l ON g.CompanyId = l.item

END

IF @ContactIds IS NULL OR @ContactIds = ''

BEGIN

SET @ContactIds = NULL

SELECT @ContactIds = COALESCE(@ContactIds+',','') + LTRIM(ContactId)

FROM [Contact] g

JOIN fnSplit(@CompanyIds,',') l ON g.CompanyId = l.item

END

--filter the contact ids if the requesting contact isn't an admin but is a hierarchial user manager

IF (SELECT .dbo.fnContact_IsHiearchialManager(@RequestingContactId)) = 1 AND (SELECT .dbo.fnContact_IsAdmin(@RequestingContactId)) = 0

BEGIN

DECLARE @tempContactIds VARCHAR(MAX)

SELECT @tempContactIds = COALESCE(@tempContactIds+',','') + LTRIM(s.ContactId)

FROM .dbo.fnContact_Subordinates(@RequestingContactId) s

JOIN .dbo.fnSplit(@ContactIds, ',') s1 ON s1.item = s.ContactId

SET @ContactIds = @tempContactIds

END

INSERT INTO @CDP

SELECT cdp.*

FROM ContactDocumentPermissions cdp

JOIN (SELECT ContactId = item FROM .dbo.fnSplit(@ContactIds, ',')) ids ON cdp.ContactId = ids.ContactId

WHERE HasTest = 1

SELECT DISTINCT

c.ContactId

,ContactDecriptor = c._Descriptor

,ContactActive = c.Active

,d.DocumentId

,DocumentDescriptor = d._Descriptor

,DocumentActive = d.Active

,d.MinPassingPercent

,d.PastDue

,com.CompanyId

,CompanyDescriptor = com._Descriptor

,CompanyActive = com.Active

,GroupId = CASE @IncludeGroupsInResultSet

WHEN 1 THEN cg.GroupId

ELSE NULL END

,GroupDescriptor = CASE @IncludeGroupsInResultSet

WHEN 1 THEN gda._Descriptor

ELSE NULL END

,ReportDate = cdta._EditDate

,LastTestStatus = .dbo.fn_TestStatus(cdta.ContactDocumentTestAttemptStatusId,FinalScore,PastDue,LastContactDocumentTestAttemptId,NextTestDate,MinPassingPercent)

--Cimmented by ASHISH MOTGHARE

--,ReportStatus = .dbo.fn_ReportStatus(NbrOfDeliveryDelayDays,DocumentAssignedDate,cdta.ContactDocumentTestAttemptStatusId,LastTestDate,NextTestDate,PastDue,d.HasTest,ShowOnCurriculum)

,ReportStatus = CASE

WHEN 0 = 1

THEN 'Cur

Wendelius 23-Jul-15 6:35am

That seems to be only part of the procedure.

But more importantly, try to find out if any of the queries is performing badly. I don't have your data or the database so I'm unable to do those.