Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Hi When I am trying to access my application by URL I am getting the error screen containing the below error. Previously I get it fixed by using dbcc commands in backend..bt now this is not working..

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

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

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

here is my source code for the stored proc I am calling from my .NET application.

      USE [TCO]
      ALTER PROC [dbo].[SP_TCOV3] (@year INT) AS
DECLARE @rpt_year INT;
IF @year > 2011 
BEGIN
SET @rpt_year = 2011;
BEGIN   
SET @rpt_year = @year;
DECLARE @From_Date DATETIME='01-01-2012'  -- mm/dd/yyyy
DECLARE @End_Date DATETIME=(SELECT TOP 1 DATEADD(dd, -DAY(DATEADD(m,1,dbo.ManpowerCost.payment_date)), DATEADD(m,1,dbo.ManpowerCost.payment_date)) as value
FROM dbo.ManpowerCost order by payment_date desc)  
SELECT
        allApps.[Application Name],          
        '$'+(CAST((CAST(allApps.[BAM Staff Support] AS DECIMAL(10,2))) as varchar(50)))AS [BAM Staff Support],
        '$'+(CAST((CAST(allApps.[BAM Non-Shell Support] AS DECIMAL(10,2))) as varchar(50)))AS [BAM Non-Shell Support] ,
        '$'+(CAST((CAST(allApps.[BSM DBA Support] AS DECIMAL(10,2))) as varchar(50)))AS [BSM DBA Support] ,
        '$'+(CAST((CAST(allApps.[Middleware Support] AS DECIMAL(10,2))) as varchar(50)))  AS [BSM Middleware Support],
        '$'+(CAST((CAST(allApps.[IRM Logical Access] AS DECIMAL(10,2))) as varchar(50)))AS [IRM Logical Access] ,
        '$'+(CAST((CAST(allApps.[Application Licensing and Maintenance] AS DECIMAL(10,2))) as varchar(50))) AS [Application License and Maintenance],
        '$'+(CAST((CAST(allApps.[Middleware Licensing and Maintenance] AS DECIMAL(10,2))) as varchar(50)))as [Middleware License and Maintenance],  
        '$'+(CAST((CAST(allApps.[Database Licensing and T-System DBA Maintenance] AS DECIMAL(10,2))) as varchar(50))) AS [TS DBA Maintenance],
        '$'+(CAST((CAST(allApps.[Hosting and Storage] AS DECIMAL(10,2))) as varchar(50)))AS [Hosting and Storage]  ,
        '$'+(CAST((CAST(allApps.[Telecom Connection Charge] AS DECIMAL(10,2))) as varchar(50))) AS [Telecom Connection Charges],
        '$'+(CAST((CAST(allApps.[Total Application TCO] AS DECIMAL(10,2))) as varchar(50)))AS [Total Application TCO] 
        --dbo.FN_TCOV3(@rpt_year)AS allApps 
        dbo.FN_TCOV3_Report(@From_Date,@End_Date)AS allApps 
)ORDER BY allApps.[Application Name]
The same proc is working if I do not use start date and end date parameter that I have used above
and call dbo.FN_TCOV3(@rpt_year)AS allApps instead of dbo.FN_TCOV3_Report(@From_Date,@End_Date)AS allApps, the only diff. b/w these two function is one takes only a year as parameter and the other takes 2 parameter namely start and end date.
I need to use both the parameters , Pls assist.
                Timeout can happen due to various reasons. Please show what you are trying to do with sql-server...
– Kaf
                Feb 15, 2013 at 16:50
If your query really needs to run for a long time, you can extend the TimeOut period by setting the CommandTimeout property of your SqlCommand object ( myCom.CommandTimeout = 300; for a 5 minutes timeout ; also set the corresponding page Server TimeOut accordingly)
That said, if you don't know why your query is running that long, you have to investigate and fix it.
Search for :
missing indexes
locks
inefficient algorithms
In Sql Server Management Studio, highlight the query and 
click Display Estimated Execution Plan.
Index Seek is good. If there is an index scan,
create an index on the column(s) on your table.
This fixed the problem for me.
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.