Hi All,

We are using DAAB (Microsoft.ApplicationBlocks.Data) to execute stored
procedure from .NET But at times we get the following exception "Timeout
expired. The timeout period elapsed prior to completion of the operation or
the server is not responding".

Would appreciate immediate help.

Exception Information *********************************************
Exception Type: System.Data.SqlClient.SqlException Errors:
System.Data.SqlClient.SqlErrorCollection Class: 10 LineNumber: 0 Message:
Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding. Number: -2 Procedure:
ConnectionRead (recv()). Server: State: 0 Source: .Net SqlClient Data
Provider TargetSite: System.Data.SqlClient.SqlDataReader
ExecuteReader(System.Data.CommandBehavior, System.Data.SqlClient.RunBehavior,
Boolean) HelpLink: NULL StackTrace Information
********************************************* at
System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior cmdBehavior,
RunBehavior runBehavior, Boolean returnStream) at
System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior behavior)
at Microsoft.ApplicationBlocks.Data.SqlHelper.Execute Reader(SqlConnection
connection, SqlTransaction transaction, CommandType commandType, String
commandText, SqlParameter[] commandParameters, SqlConnectionOwnership
connectionOwnership) at
Microsoft.ApplicationBlocks.Data.SqlHelper.Execute Reader(String
connectionString, CommandType commandType, String commandText, SqlParameter[]
commandParameters) at
TCOM.OSS.Providers.SQLProvider.Provider.CallStored Proc(String procedureName,
SqlParameter[]& sqlParameterList)

Hi

Check for blocking. Run sp_who2 to see what process it blocking another one.
Look at "blocks, avoiding" in SQL Server BOL.

Regards
Mike

"Sachin Surana" wrote:

Hi Mike,

Thanks a lot for the prompt answer. Actually, we are getting this error on
production and at times when no other heavy process is running.

Could it be because of the 'Timeout' settings in DAAB not working properly
or DAAB not handling connection management properly.

Thanks & Best Regards,
Sachin

"Mike Epprecht (SQL MVP)" wrote:

We have the same problem? Did you find solution to it?
thanks,

*** Sent via Developersdex http://www.developersdex.com ***

I am having the same issue with a timeout and sqlHelper. Is there a way
to set the timeout time within a sqlHelper property or method?

thanks.
-MJ

Sachin Surana wrote:


--
MBeaudreau
------------------------------------------------------------------------
Posted via http://www.codecomments.com
------------------------------------------------------------------------

Does anyone know how to modify SqlHelper.cs ExecuteNonQuery to extend
the timeout? I have a sql stored procedure that runs without output
params but is timing out.

Any help is greatly appreciated.
thanks,
-MJ

Sachin Surana wrote:


--
MBeaudreau
------------------------------------------------------------------------
Posted via http://www.codecomments.com
------------------------------------------------------------------------

The property can be set through CommandTimeout property of SqlCommand. Do
this in the method 'PrepareCommand' of DAAB.

Cheers,
Sachin

"MBeaudreau" wrote:

Hello everyone...
plzz help me,im getting this error whenever i want to connect my pc with
another remote pc which i've designated as Server and my database is saved
there so i want to connect my pc with tht remote pc and save data entered by
my pc to that remote pc's database...
but i get this error....PLEASE HELP IMMEDIATELY...



Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding. (.Net SqlClient Data Provider)

----------------------------------------
For help, click: http://go.microsoft.com/fwlink?
ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServ er&EvtID=-2&LinkId=20476

----------------------------------------
Error Number: -2
Severity: 11
State: 0


----------------------------------------
Program Location:

at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException
exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.
ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner,
Boolean& failoverDemandDone, String host, String failoverPartner, String
protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean
encrypt, Boolean integratedSecurity, SqlConnection owningObject, Boolean
aliasLookup)
at System.Data.SqlClient.SqlInternalConnectionTds.
OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString
connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..
ctor(SqlConnectionString connectionOptions, Object providerInfo, String
newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.
CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo,
DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.
CreateNonPooledConnection(DbConnection owningConnection,
DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.
GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.
OpenConnection(DbConnection outerConnection, DbConnectionFactory
connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Management.UI.VSIntegration.Ob jectExplorer.
ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Co nnector.
ConnectionThreadUser()

Hi

The following code will connect the sql adapter and sql command, applying
this will solve the timeout exception

DbConn.Open()
Dim DbCmd As New SqlClient.SqlDataAdapter
Dim cmd As New SqlCommand(sSQL, DbConn)
cmd.CommandTimeout = 250
DbCmd.SelectCommand = cmd
Dim Dt As New DataTable
DbCmd.Fill(Dt)
DbConn.Close()
Return Dt

Same logic can be applied to oledb command also

Dim DbCmd As New OleDb.OleDbDataAdapter
Dim cmd As New OleDb.OleDbCommand(sSQL, OraConn)
cmd.CommandTimeout = 250
DbCmd.SelectCommand = cmd
Dim Dt As New DataTable
DbCmd.Fill(Dt)
DbConn.Close()
Return Dt

Happy Programming

SqlConnection EDCConnectionString =3D new SqlConnection(=E2=80=9DConnection=
String=E2=80=9D);
EDCConnectionString.Open();
string SQLTesterStats =3D =E2=80=9CStoredProcedure=E2=80=9D;
SqlCommand cmdTesterStats =3D new SqlCommand(SQLTesterStats, EDCConnectionSt=
ring);
cmdTesterStats.CommandType =3D CommandType.StoredProcedure;
cmdTesterStats.Parameters.AddWithValue(=E2=80=9D@s tart=E2=80=9D, =E2=80=9C=
04-01-2007 00:00=E2=80=B3);
cmdTesterStats.CommandTimeout =3D 500000;
SqlDataReader TesterStatsReader =3D cmdTesterStats.ExecuteReader();
DataRow NewRow;
while (TesterStatsReader.Read())
{
NewRow[=E2=80=9Dproduct=E2=80=9D] =3D TesterStatsReader[=E2=80=9Dfamily_n=
ame=E2=80=9D].ToString();
}
TesterStatsReader.Close();
EDCConnectionString.Close();


if u r not having any parameters then set to ''.