I have a handshaking problem with SQL which I can't figure out.
The code contains the following:-
{
using (StreamWriter sw = File.AppendText("D:\\SPTimesheets.log"))
{
sw.WriteLine("FORM1_LOAD: Reading PersonID, EmployeeClassCode, Full Name and ProfitCentreCode from Workbench.People.");
sw.Close();
}
try
{
con0.Open();
SqlCommand command1 = new SqlCommand(SelectString, con0);
myReader = command1.ExecuteReader();
command1.CommandTimeout = 300;
if (myReader.HasRows)
{
while (myReader.Read())
{
WBID = Convert.ToInt32(myReader["PersonID"]);
ThisWBID = WBID;
LoggedOnUser = myReader["FullName"].ToString();
EmployeeCC = myReader["EmployeeClassCode"].ToString();
textBox1.Text = myReader["FullName"].ToString();
EmployeeProfitCentreCode = myReader["ProfitCentreCode"].ToString();
EmployeeDayHoursSat = myReader["Week1WeekHrsDay1"].ToString();
EmployeeDayHoursSun = myReader["Week1WeekHrsDay2"].ToString();
EmployeeDayHoursMon = myReader["Week1WeekHrsDay3"].ToString();
EmployeeDayHoursTue = myReader["Week1WeekHrsDay4"].ToString();
EmployeeDayHoursWed = myReader["Week1WeekHrsDay5"].ToString();
EmployeeDayHoursThu = myReader["Week1WeekHrsDay6"].ToString();
EmployeeDayHoursFri = myReader["Week1WeekHrsDay7"].ToString();
UserTimeSheetGroup = myReader["TimesheetGroupID"].ToString();
useremail = myReader["EmailAddress"].ToString();
if (myReader["pJobManagerID"].ToString() == "-1")
{
CanManage = true;
buttonmanage.Enabled = true;
buttonmanage.Visible = true;
}
}
con0.Close();
}
else
{
MessageBox.Show("Cannot find you as a user");
using (StreamWriter sw = File.AppendText("D:\\SPTimesheets.log"))
{
sw.WriteLine("Cannot find user - exiting");
sw.Close();
MailMe();
}
System.Environment.Exit(0);
}
using (StreamWriter sw = File.AppendText("D:\\SPTimesheets.log"))
{
sw.WriteLine("WBID = " + WBID.ToString());
sw.Close();
}
}
catch (Exception e1)
{
ExceptionHandler("Form1_Load Line 446 ", e1.ToString(), SelectString, e1);
}
}
and
public void ExceptionHandler(string Location, string ThisError, string ThisQuery, Exception e)
{
using (StreamWriter sw = File.AppendText("D:\\SPTimesheets.log"))
{
sw.WriteLine(Location + ", " + ThisError);
if (ThisQuery.Length > 0)
{
sw.WriteLine("Select query was:");
sw.WriteLine(ThisQuery);
}
switch (e.ToString())
{
.....................
default:
sw.WriteLine("No standard exception found.");
break;
}
This works fine on a number of computers using a VPN. However on one computer on a VPN it consistently gives this error:-
Form1_Load Line 446 , System.Data.SqlClient.SqlException (0x80131904): Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=4688; handshake=2462; ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.Open()
at SPTimesheets.Form1.Form1_Load(Object sender, EventArgs e)
ClientConnectionId:3055052b-81c9-4433-8ccf-4f154a4bfbf8
Error Number:-2,State:0,Class:11
Select query was:
SELECT PersonID, EmployeeClassCode, FullName, ProfitCentreCode, Week1WeekHrsDay1, Week1WeekHrsDay2, Week1WeekHrsDay3, Week1WeekHrsDay4, Week1WeekHrsDay5, Week1WeekHrsDay6, Week1WeekHrsDay7, pJobManagerID, TimesheetGroupID, EmailAddress FROM Workbench.dbo.People WHERE (((LoginCode = 'Annerine') OR (LoginCode = 'SPGLOBAL\Annerine')) AND (HasLogin = -1))
No standard exception found.
What I have tried:
Checked bandwidth available on remote computer, this is better than others who have no problem.
Checked the user access rights to SQL, all good and anyway this user can use this software when not remote.
Checked the database settings for this user, all relevant ones are the same as other users who don't have this problem.
Searched extensively, problem found elsewhere but generally seems intermittent not consistent.