Timeout error in VB.NET while running a sql script

0
votes

Can someone please help? I am trying to create a grid view in visual studio by connecting to a server and using a stored procedure. I am getting a time out error when I test it out. Can someone advise me as to what I can do? The query itself takes about 7 minutes to complete. The data is stored in a table. What can I do to extend the timeout?

Here is my connection script:

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default2.aspx.vb" Inherits="Default2" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>SQL-EDD 4 SERVER</title>
</head>
<body>
   <asp:GridView ID="GridView1" DataSourceID="SqlDataSource1" AutoGenerateColumns="False"
      runat="server">
      <Columns>
        <asp:BoundField DataField="DBNAME" HeaderText="DBNAME" SortExpression="DBNAME" />
        <asp:BoundField DataField="IMGS" HeaderText="IMGS" ReadOnly="True" 
              SortExpression="IMGS" />
          <asp:BoundField DataField="IMG_DATE_UPLOAD" HeaderText="IMG_DATE_UPLOAD" 
              ReadOnly="True" SortExpression="IMG_DATE_UPLOAD" />
      </Columns>
    </asp:GridView>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" SelectCommand="IMGCTR"
      ConnectionString="<%$ ConnectionStrings:JEFFREYEDD4 %>" 
        SelectCommandType="StoredProcedure" />


    <form id="form1" runat="server">
    <div>

    </div>
    </form>
</body>
</html>

THE SQL CODE IS BELOW:

SET NOCOUNT ON
DECLARE @DBNAME NVARCHAR(MAX)
DECLARE @DBNAME1 NVARCHAR(MAX)
DECLARE @varSQL NVARCHAR(MAX)

DROP TABLE TEMPIMGCOUNTERSERVER3 
CREATE TABLE TEMPIMGCOUNTERSERVER3 
(DBNAME NVARCHAR(MAX),
IMG_DATE_UPLOAD DATETIME,
IMG_UPLOAD_COUNT INT)

DECLARE DBNAME CURSOR FAST_FORWARD FOR
SELECT [NAME] FROM sys.databases WHERE [name] like 'z%' and [name] not like 'Z1301_TEVA_SINGLE' AND [NAME] NOT LIKE 'Z1016_E-100016R'
AND CREATE_DATE BETWEEN GETDATE()-30 AND GETDATE()
ORDER BY [Name]

OPEN DBNAME

FETCH NEXT FROM DBNAME INTO @DBname
WHILE (@@FETCH_STATUS=0)
BEGIN

SET @DBNAME1=('USE ['+ @DBNAME +']')
EXEC SP_EXECUTESQL  @DBNAME1

    Set @varSQL='INSERT INTO TEMPIMGCOUNTERSERVER3 (DBNAME, IMG_UPLOAD_COUNT, IMG_DATE_UPLOAD) 
    SELECT ''['+@DBNAME+']'' AS DBNAME, SUM(PGCOUNT) AS IMAGES, convert(NVARCHAR(10), CREATED, 101) AS CREATED
        FROM ['+@DBNAME+'].dbo.tbldoc WHERE CREATED BETWEEN ''2011-01-16'' AND ''2011-01-22'' AND PGCOUNT >0 GROUP BY CREATED 
        HAVING SUM(PGCOUNT) IS NOT NULL'    
    EXEC SP_EXECUTESQL @varSQL

    FETCH NEXT FROM DBNAME 
    INTO @DBNAME
    END

    CLOSE DBNAME
    DEALLOCATE DBNAME

    INSERT TEMPIMGCOUNTERSERVER3 (DBNAME, IMG_UPLOAD_COUNT)  
    SELECT 'TOTAL_IMGSIZE_IN_MBS', SUM(IMG_UPLOAD_COUNT) FROM TEMPIMGCOUNTERSERVER3 

    SELECT  DBNAME, SUM(IMG_UPLOAD_COUNT) AS IMGS, convert(NVARCHAR(10), IMG_DATE_UPLOAD, 101) AS IMG_DATE_UPLOAD FROM TEMPIMGCOUNTERSERVER3 where IMG_UPLOAD_COUNT > 0
    GROUP BY DBNAME, IMG_DATE_UPLOAD
    ORDER BY IMG_DATE_UPLOAD

Error is as follows:

Server Error in '/lawsqlreporting' Application.

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated. 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. The statement has been terminated.

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.

Stack Trace:

[SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1951450 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4849003 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2394 System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +33 System.Data.SqlClient.SqlDataReader.get_MetaData() +83 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12 System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +10 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +130 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +287 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +92 System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1297 System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +19 System.Web.UI.WebControls.DataBoundControl.PerformSelect() +142 System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +73 System.Web.UI.WebControls.GridView.DataBind() +4 System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82 System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +72 System.Web.UI.Control.EnsureChildControls() +87 System.Web.UI.Control.PreRenderRecursiveInternal() +44 System.Web.UI.Control.PreRenderRecursiveInternal() +171 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +842

Version Information: Microsoft .NET Framework Version:2.0.50727.4952; ASP.NET Version:2.0.50727.4955

3
asp.netsqlvb.netsql-server-2008
Is this an asp.net timeout or sql command or connection timeout? – Oded
You could increase the time out period your app will allow, but you are better off trying to tune the stored procedure to return the results faster. Can you post the SQL code? – Dan Snell
Continuing with Oded, please post the full error message you get. – Ken D
I just learned about this hidden proc today, on Stackoverflow, no less: wiki.lessthandot.com/index.php/Sp_MSforeachDB. That will remove some of the overhead in your query. – Chris B. Behrens
I tried that before. The problem is I only I need to query databases beginning with 'Z' that were created during a 60 day time period. – Jeff

3 Answers

1
votes

In order to increase the timeout you can increase the CommandTimeout for the DataCommand that's fetching the data. When you use SqlDataSource this is done using an event handler to obtain the command. Insert the following in code behind of your page:

Private Sub SqlDataSource1_Selecting(sender as object, _
   e as SqlDataSourceSelectingEventArgs) handles SqlDataSource1.Selecting

   e.Command.CommandTimeout = 500 
End Sub

In order to increase the web application's request timeout you have to set the executionTimeout to a suitable length by modifying the web config. 

<httpRuntime
   executionTimeout = "600" 
/>

But better is of course to tune your query.

4
votes

First of all - extending the timeout is not the solution. Nevertheless, here's how you do it with your data source:

protected void SqlDataSource1_Selecting(object sender,
SqlDataSourceSelectingEventArgs e)
{
e.Command.CommandTimeout = 30;
}

Here's the analysis you should perform:

First: does the stored procedure execute satisfactorily when executed outside of the context of the page, e.g., in the query window? If not, the problem is with the query. Google "optimize sql query" and you'll get a bunch of good stuff.

If it does, then how do other queries in the application perform (assuming this is not the first one you're trying to get working)? If they work fine, double-check that you're using the same connection string.

0
votes

So here is an approach you might want to take a look at with out the cursor. I have not run this but with some tweaking it should work. 

CREATE Table #TableNames (DBNameID INT Not NUll Identity, DBName VarCHAR(50))

CREATE TABLE #TableResults (DBNAME NVARCHAR(MAX),  IMG_DATE_UPLOAD DATETIME,  IMG_UPLOAD_COUNT INT)

Insert Into #TableNames(DBName)
SELECT [NAME] FROM sys.databases 
WHERE [name] like 'z%' and [name] not like 'Z1301_TEVA_SINGLE' 
AND [NAME] NOT LIKE 'Z1016_E-100016R' 
AND CREATE_DATE BETWEEN GETDATE()-30 AND GETDATE() ORDER BY [Name]

DECLARE @Counter INT = 1;
DECLARE @DBName VARCHAR(MAX)
DECLARE @varSQL NVARCHAR(MAX)
Declare @rows INT;
select @rows = COUNT(DBNameID) from #TableNames


While @Counter <= @rows BEGIN
    SET @DBName = (SELECT DBName from #TableNames where DBNameID = @Counter)

    Set @varSQL='INSERT INTO #TableResults (DBNAME, IMG_UPLOAD_COUNT, IMG_DATE_UPLOAD)      
SELECT ''['+@DBNAME+']'' AS DBNAME, SUM(PGCOUNT) AS IMAGES, convert(NVARCHAR(10), CREATED, 101) AS CREATED
FROM ['+@DBNAME+'].dbo.tbldoc WHERE CREATED BETWEEN ''2011-01-16'' AND ''2011-01-22'' AND PGCOUNT >0 GROUP BY CREATED          
HAVING SUM(PGCOUNT) IS NOT NULL' 

SET @Counter += 1
END

INSERT #TableResults (DBNAME, IMG_UPLOAD_COUNT)       
SELECT 'TOTAL_IMGSIZE_IN_MBS', SUM(IMG_UPLOAD_COUNT) 
FROM #TableResults       

SELECT  DBNAME, SUM(IMG_UPLOAD_COUNT) AS IMGS, convert(NVARCHAR(10), IMG_DATE_UPLOAD, 101) AS IMG_DATE_UPLOAD 
FROM #TableResults where IMG_UPLOAD_COUNT > 0     
GROUP BY DBNAME, IMG_DATE_UPLOAD     
ORDER BY IMG_DATE_UPLOAD

DROP TABLE #TableNames
DROP TABLE #TableResults

If you get this working at it still is taking too long you may want to look at how long it takes to run the select you are executing per db. If each of these is taking a long time you may need to look at the query plan for that part of the query itself.