Click here to Skip to main content
15,613,470 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a handshaking problem with SQL which I can't figure out.
The code contains the following:-
C#
{
       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")  //this user is job manager.
                   {
                       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
C#
       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.
Posted
Updated 21-Sep-21 14:02pm
v2
Comments
PIEBALDconsult 4-Aug-21 19:59pm    
Try increasing the connection timeout?
ormonds 4-Aug-21 21:30pm    
I did increase it to 300, as shown in the code. That's five minutes, if that isn't long enough surely there must be some other problem?
Richard Deeming 5-Aug-21 4:55am    
There must be something different about the VPN setup for the affected computer. If you're using the server name to connect, perhaps it's not configured to resolve names on the remote network?

Also, don't store connection objects in class-level fields. Create them when they're required, and wrap them in a using block to ensure they're disposed of as soon as they're no longer needed.
ormonds 6-Aug-21 18:54pm    
Thank you. Yes, I am now learning more about VPN set up.
ormonds 10-Aug-21 22:34pm    
Thank you. I am now sure the the VPN is the problem so am troubleshooting that.

1 solution

 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900