I have MVC2 application using Entity Framework and ADO.net. In ADO.net SqlCommand connection, it get
Server Error in '/' Application.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
The code is,
using (SqlCommand command = conn.CreateCommand())
{
var commandText = "exec 'stored producedure name'";
command.CommandTimeout = 240;
SqlDataAdapter da = new SqlDataAdapter(commandText, conn);
DataSet ds = new DataSet();
da.Fill(ds, "Table");
}
The stored procedure takes about 3 mins to complete. So I change CommandTimeout
to 240s (4 mins). However the strange thing is that it still get "Time out" error after about 30 second running. The code runs in VS 2010. What is the problem?
I try timeout 0, but it still gets timeout error after 30s of running. It is really weird because it looks like prolem of longer running command. But why after reset commandTimeout, it is still the same.
I try timeout 0, but it still gets timeout error after 30s of running. It is really weird because it looks like prolem of longer running command. But why after reset commandTimeout, it is still the same.
why you use command.CommandTimeout=240; ??
You can remove the line command.CommandTimeout=240; then check
Please specify currenlty where u are testing your code?in development or staging ?
Make new function for this sp and try below code then check if still not done then please pass me your sp Or check sql server side query time out.
Try below code.
Dim Cmd As New SqlClient.SqlCommand
Dim Adp As New SqlClient.SqlDataAdapter
Dim ds As New DataSet
Try
Using SqlConnection As New SqlClient.SqlConnection(Connectionstring)
Cmd.CommandType = CommandType.StoredProcedure
Cmd.CommandText = "Yourspname"
Cmd.Parameters.Add("id", SqlDbType.Int).Value = id
Cmd.CommandTimeout = 500
Cmd.Connection = SqlConnection
Adp.SelectCommand = Cmd
SqlConnection.Open()
Adp.Fill(ds)
If Not ds Is Nothing Then
Return ds
Else
Return Nothing
End If
End Using
Catch ex As Exception
Return Nothing
Finally
If Not Cmd Is Nothing Then
Cmd = Nothing
End If
If Not Adp Is Nothing Then
Adp = Nothing
End If
End Try
Yes, the SP is complicated which returns a table and run over 3 mins. I am not sure why it takes so long to complete. The code is as follow. Do you think there is a problem in SP and cause the slow of running?
CREATE PROCEDURE [dbo].[uspReportFundingRecommendationSummaryDetail]
@LanguageID varchar(20),
@SportID varchar(20),
@YearID varchar(20)
AS
BEGIN
SET NOCOUNT ON
-- Declare Local Variables
-- Common Variables
DECLARE @Procedure_Name varchar(100),
@Error_Num int,
@Error_Message varchar(1000)
-- Procedure Specific Variables
-- Initialze Local Variables
SET @Procedure_Name = 'uspReportFundingRecommendationSummaryDetail'
SET @Error_Num = 0
SET @Error_Message = ''
BEGIN TRY
SELECT
CASE @SportID
WHEN 'S' THEN 'Summer'
ELSE 'Winter'
END AS Season,
MIN(SR.OTPRank) as OTPRank,
MIN(SR.SportRankingCategoryID) as SportRank,
CASE @LanguageID
WHEN 'FR' THEN S.Name_FR
ELSE S.Name_EN
END as SportName,
MIN(FS.YearID) as YearID,
MIN(CPrior.YearID) as PriorYearID,
ISNULL(MIN(PYear.PriorFunding),0) as PriorFunding,
SUM(FD.RequestOTP) as NSORequest,
CASE
WHEN MIN(PYear.PriorFunding) = 0 THEN NULL
ELSE SUM(FD.RequestOTP) / MIN(PYear.PriorFunding) - 1
END as NSOChange,
SUM(FD.RecommendTotalHPA) as HPARecommend,
CASE
WHEN MIN(PYear.PriorFunding) = 0 THEN NULL
ELSE SUM(FD.RecommendTotalHPA) / MIN(PYear.PriorFunding) - 1
END as HPAChange,
SUM(FD.RecommendTotalOTP) as OTPRecommend,
CASE
WHEN MIN(PYear.PriorFunding) = 0 THEN NULL
ELSE SUM(FD.RecommendTotalOTP) / MIN(PYear.PriorFunding) - 1
END as OTPChange,
SUM(FD.RecommendTotal) as MgmtRecommend
FROM FundingSummary FS
INNER JOIN [Year] CYear
ON FS.YearID = CYear.YearID
INNER JOIN [Year] CPrior
ON CYear.YearFrom = CPrior.YearTo
LEFT JOIN Organization FSO
ON FS.OrganizationID = FSO.OrganizationID
LEFT JOIN Sport S
ON FS.SportID = S.SportID
LEFT JOIN (
SELECT SR.SportID,
SR.SportRankingCategoryID,
SRC.OTPRank
FROM SportRanking SR
INNER JOIN SportRankingCategory SRC
ON SR.SportRankingCategoryID = SRC.SportRankingCategoryID
WHERE YearID = @YearID
) SR
ON FS.SportID = SR.SportID
LEFT JOIN FundingBlock FB
ON FS.FundingSummaryID = FB.FundingSummaryID
LEFT JOIN FundingCategory FC
ON FB.FundingBlockID = FC.FundingBlockID
LEFT JOIN FundingDetail FD
ON FC.FundingCategoryID = FD.FundingCategoryID
LEFT JOIN (
SELECT FS.SportID,
SUM(FD.FundsTotal) as PriorFunding
FROM FundingSummary FS
LEFT JOIN FundingBlock FB
ON FS.FundingSummaryID = FB.FundingSummaryID
LEFT JOIN FundingCategory FC
ON FB.FundingBlockID = FC.FundingBlockID
LEFT JOIN FundingDetail FD
ON FC.FundingCategoryID = FD.FundingCategoryID
INNER JOIN [Year] YP
ON FS.YearID = YP.YearID
LEFT JOIN [Year] YC
ON YP.YearTo = YC.YearFrom
WHERE YC.YearID = @YearID
GROUP BY FS.SportID, YP.YearID
) PYear
ON FS.SportID = PYear.SportID
WHERE Left(S.SportID,1) = @SportID
AND FSO.OrganizationTypeID = 'NSO'
AND FS.YearID = @YearID
GROUP BY S.SportID,
CASE @LanguageID
WHEN 'FR' THEN S.Name_FR
ELSE S.Name_EN
END
ORDER BY MIN(SR.OTPRank),
MIN(SR.SportRankingCategoryID),
CASE @LanguageID
WHEN 'FR' THEN S.Name_FR
ELSE S.Name_EN
END
END TRY
BEGIN CATCH
--Standard Error Handling
SET @Error_Num = Error_Number()
SET @Error_Message = Error_Message()
RAISERROR(@Error_Message , 15, 1, @Procedure_Name) WITH NOWAIT, SETERROR
END CATCH
END
yzhang738
Member
288 Points
228 Posts
Why change in CommandTimeout not working
Jan 21, 2013 10:37 PM|LINK
Hi,
I have MVC2 application using Entity Framework and ADO.net. In ADO.net SqlCommand connection, it get
Server Error in '/' Application.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
The code is,
using (SqlCommand command = conn.CreateCommand()) { var commandText = "exec 'stored producedure name'"; command.CommandTimeout = 240; SqlDataAdapter da = new SqlDataAdapter(commandText, conn); DataSet ds = new DataSet(); da.Fill(ds, "Table"); }The stored procedure takes about 3 mins to complete. So I change CommandTimeout to 240s (4 mins). However the strange thing is that it still get "Time out" error after about 30 second running. The code runs in VS 2010. What is the problem?
York
nikunjnandan...
Participant
882 Points
223 Posts
Re: Why change in CommandTimeout not working
Jan 22, 2013 05:36 AM|LINK
Hii,
Try timeout 0 and then check.
Have u checked on same server that sp is taking 3 mins or You checked in local and this error occured on server.
Different server take different timing for same sp. It's depending upon data,configuration of server,traffic of server lots of things.
Nikunj Nandaniya
My Blog
rsrvs214
Member
208 Points
81 Posts
Re: Why change in CommandTimeout not working
Jan 22, 2013 05:50 AM|LINK
Hai
refer this link.its help full for you
http://blogs.msdn.com/b/spike/archive/2008/07/31/timeout-expired-the-timeout-period-elapsed-prior-to-completion-of-the-operation-or-the-server-is-not-responding.aspx
regards rsrvs
yzhang738
Member
288 Points
228 Posts
Re: Why change in CommandTimeout not working
Jan 22, 2013 07:11 AM|LINK
I try timeout 0, but it still gets timeout error after 30s of running. It is really weird because it looks like prolem of longer running command. But why after reset commandTimeout, it is still the same.
rsrvs214
Member
208 Points
81 Posts
Re: Why change in CommandTimeout not working
Jan 22, 2013 07:17 AM|LINK
why you use command.CommandTimeout = 240; ??
You can remove the line command.CommandTimeout = 240; then check
nikunjnandan...
Participant
882 Points
223 Posts
Re: Why change in CommandTimeout not working
Jan 22, 2013 07:47 AM|LINK
Hii,
Is your dbcommand is global object?
Please specify currenlty where u are testing your code?in development or staging ?
Make new function for this sp and try below code then check if still not done then please pass me your sp Or check sql server side query time out.
Try below code.
Dim Cmd As New SqlClient.SqlCommand Dim Adp As New SqlClient.SqlDataAdapter Dim ds As New DataSet Try Using SqlConnection As New SqlClient.SqlConnection(Connectionstring) Cmd.CommandType = CommandType.StoredProcedure Cmd.CommandText = "Yourspname" Cmd.Parameters.Add("id", SqlDbType.Int).Value = id Cmd.CommandTimeout = 500 Cmd.Connection = SqlConnection Adp.SelectCommand = Cmd SqlConnection.Open() Adp.Fill(ds) If Not ds Is Nothing Then Return ds Else Return Nothing End If End Using Catch ex As Exception Return Nothing Finally If Not Cmd Is Nothing Then Cmd = Nothing End If If Not Adp Is Nothing Then Adp = Nothing End If End TryNikunj Nandaniya
My Blog
yzhang738
Member
288 Points
228 Posts
Re: Why change in CommandTimeout not working
Jan 22, 2013 10:49 PM|LINK
As the code I put, sqlcommand is a local object in using clause.
The code is running in development. What is the difference between development and staging?
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Why change in CommandTimeout not working
Jan 23, 2013 02:04 AM|LINK
How did you write your SP? Is it so complicated?
nikunjnandan...
Participant
882 Points
223 Posts
Re: Why change in CommandTimeout not working
Jan 23, 2013 05:23 AM|LINK
Hi,
Once try with my code and make new function for that.
If not done then put your sp here.
Nikunj Nandaniya
My Blog
yzhang738
Member
288 Points
228 Posts
Re: Why change in CommandTimeout not working
Jan 24, 2013 05:18 AM|LINK
Yes, the SP is complicated which returns a table and run over 3 mins. I am not sure why it takes so long to complete. The code is as follow. Do you think there is a problem in SP and cause the slow of running?
Thanks.