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