A transport-level error has occurred when sending the request to the server.

Hi,

A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

I am using a direct connection from my Gridview to perform a huge database search. the stored procedure will take 10- 60 seconds to execute the query.

What the problem i am facing is when the sp takes long time (more than 1 minutes), then when i try any other operation like calling another sp frm another page, i am getting the above specified error.

Anybody can tell why is it happening?

here my code snippet goes

<asp:GridView ID="searchGV" runat="server" AllowPaging="True" AllowSorting="True"

BorderColor="#999999" BorderStyle="None" CellPadding="3" Width="100%" AutoGenerateColumns="False" DataSourceID="searchObj" DataKeyNames="Recordnumber" OnDataBound="searchGV_DataBound" OnPageIndexChanged="searchGV_PageIndexChanged">

<RowStyle CssClass="gridRow" />

<PagerStyle HorizontalAlign="Center" BackColor="#999999" ForeColor="Black" />

<HeaderStyle CssClass="gridHeader" />

<AlternatingRowStyle CssClass="gridAlternateRow" />

<Columns>

<asp:TemplateField HeaderText="All">

<HeaderTemplate>

<asp:CheckBox ID="chkid" AutoPostBack="true" OnCheckedChanged ="OnCheckChangedEvent" runat="server" />

</HeaderTemplate>

<EditItemTemplate>

<asp:CheckBox ID="chkSelect" runat="server" />

</EditItemTemplate>

<ItemTemplate>

<asp:CheckBox ID="chkSelect" runat="server"/>

</ItemTemplate>

<ControlStyle Height="18px" Width="15px" />

<ItemStyle Width="5px" />

</asp:TemplateField>

<asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title">

<ItemStyle Width="150px" />

</asp:BoundField>

<asp:BoundField DataField="Artist" HeaderText="Artist(s)" SortExpression="Artist"/>

<asp:BoundField DataField="Album" HeaderText="Album" SortExpression="Album"/>

<asp:BoundField DataField="Writer" HeaderText="Writer(s)" SortExpression="Writer" ><ItemStyle Width="150px" /></asp:BoundField>

<asp:BoundField DataField="ISRC" HeaderText="ISRC" SortExpression="ISRC" />

<asp:BoundField DataField="UPC" HeaderText="UPC" SortExpression="UPC"/>

<asp:BoundField DataField="PLineLabel" HeaderText="P-Line Label" SortExpression="PLineLabel" ><ItemStyle Width="100px" /></asp:BoundField>

<asp:BoundField DataField="DistLabel" HeaderText="Distribution Label" SortExpression="DistLabel" >

<ItemStyle Width="100px" />

</asp:BoundField>

<asp:BoundField DataField="PublishInfo" HeaderText="Publisher(s)" SortExpression="PublishInfo" />

<asp:BoundField DataField="Recordnumber" HeaderText="Record Number" SortExpression="Recordnumber" />

<%-- <asp:BoundField DataField="UID" HeaderText="UID" Visible="false" />--%>

</Columns>

</asp:GridView>

with adapter

<asp:SqlDataSource ID="searchObj" runat="server" SelectCommand="gsp_titleSearchResult" SelectCommandType="StoredProcedure" ConnectionString="<%$ ConnectionStrings:ConnStr %>">

<SelectParameters>

<asp:ControlParameter ControlID="HuserID" PropertyName="Value" Name="Userid" Type="String" Size="50" DefaultValue="" />

<asp:ControlParameter ControlID="Htitle" PropertyName="Value" Name="title" Type="String" Size="50" DefaultValue=" " />

<asp:ControlParameter ControlID="Hartist" PropertyName="Value" Name="artist" Type="String" Size="50" DefaultValue=" " />

<asp:ControlParameter ControlID="Halbum" PropertyName="Value" Name="album" Type="String" Size="50" DefaultValue=" " />

<asp:ControlParameter ControlID="Hwriter" PropertyName="Value" Name="writer" Type="String" Size="50" DefaultValue=" " />

<asp:ControlParameter ControlID="Hisrc" PropertyName="Value" Name="isrc" Type="String" Size="50" DefaultValue=" " />

<asp:ControlParameter ControlID="Hupc" PropertyName="Value" Name="upc" Type="String" Size="50" DefaultValue=" " />

<asp:ControlParameter ControlID="Hpline" PropertyName="Value" Name="pline" Type="String" Size="50" DefaultValue=" " />

<asp:ControlParameter ControlID="Hdist" PropertyName="Value" Name="distlabel" Type="String" Size="50" DefaultValue=" " />

<asp:ControlParameter ControlID="Hpublish" PropertyName="Value" Name="publisher" Type="String" Size="50" DefaultValue=" " />

<asp:ControlParameter ControlID="ReleaseID" PropertyName="Value" Name="Releaseid" Type="Int32" Size="50" DefaultValue="0" />

<asp:ControlParameter ControlID="RecordLabelID" PropertyName="Value" Name="recordlabelid" Type="Int32" Size="50" DefaultValue="0" />

<asp:ControlParameter ControlID="PublisherID" PropertyName="Value" Name="Publisherid" Type="Int32" Size="50" DefaultValue="0" />

<asp:ControlParameter ControlID="ArtistID" PropertyName="Value" Name="Artistid" Type="Int32" Size="50" DefaultValue="0" />

</SelectParameters>

</asp:SqlDataSource>

Advance thanks

Anvar Sadath

Make sure that if you are using connection pooling the pool size does not hit the configured limit. If you are making use of extensive locking within the procedure (or even SQL Server does) you will get a timeout for the other procedure / statement, waiting for ressources. Try to find out if the procedure does locking using sp_lock or Acitivity monitor or if deadlock are occuring during execution time.

Jens K. Suessmeyer

http://www.sqlserver2005.de